I have a certain function which returns tabular value. It has several optional parameters. The problem is that when calling a function (in the studio) without specifying the parameters, I get an empty selection, but it should not be so. I feel that conditions in where are incorrectly set. But as soon as he was not out, he did not want to work as he should.

The function looks like this:

ALTER FUNCTION dbo.GetPeople(@m_id INT, @sex INT = -1, @country_id INT = -1, @city_id INT = -1) RETURNS @ReturnTable TABLE(-- тут поля AS BEGIN INSERT INTO @ReturnTable(-- тут поля FROM users WHERE @sex > 0 AND sex = @sex AND @country_id > 0 AND sex = @sex AND @country_id > 0 AND sex = @sex; RETURN; END;

I call accordingly

 SELECT * FROM GetPeople (1234567,DEFAULT,DEFAULT,DEFAULT)
  • and what should be returned in case of default parameters? - DreamChild
  • if your conditions are always false, what do you want? transferring -1 you won't get @sex > 0 - teran
  • you probably need something like ((@sex = -1) OR (sex = @sex)) ... AND .. - teran
  • I need where where sex = @ sex is taken into account only if the @sex parameter is different from the default value, i.e. set. and if sex is not set, then do not use it in where - Antykus

2 answers 2

 WHERE(@country_id = -1 AND country_id > 0 OR @country_id > -1 AND country_id = @country_id) AND (@city_id = -1 AND city_id > 0 OR @city_id > -1 AND city_id = @city_id) AND (@sex = -1 AND sex >= 0 OR sex > -1 AND sex = @sex)

sort of fit

    In my opinion, through CASE it is easier and more readable:

     ALTER FUNCTION dbo.GetPeople(@m_id INT, @sex INT = -1, @country_id INT = -1, @city_id INT = -1) RETURNS @ReturnTable TABLE(-- тут поля AS BEGIN INSERT INTO @ReturnTable(-- тут поля FROM users WHERE sex = CASE WHEN @sex = -1 THEN sex ELSE @sex END AND country_id = CASE WHEN @country_id = -1 THEN country_id ELSE @country_id END AND city_id = CASE WHEN @city_id = -1 THEN city_id ELSE @city_id END; RETURN; END;