PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000)
AS
BEGIN
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable)
END
My problem is, the locationId will be null or length equals 0 some time, how can I make the statement "table.LocationId in (SELECT id FROM tempTable)" included into the WHERE condition dynamically depends on locationId is null or length equals 0?
Is that possible to do it at Database side? Or I have to do it at code side?
Thank you.
Perhaps something like this:
SELECT t.id
FROM Table t
WHERE ( t.id = @.Id
AND ( t.LocationID in (SELECT ID FROM TempTable)
OR t.LocationID IS NULL
)
)
END
Looks like you want dynamic search capabilities based on the parameters passed. You can take a look at the link below for various options:
http://www.sommarskog.se/dyn-search.html
You can do below in your case:
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and @.LocationId is not null and len(@.LocationId) > 0
UNION ALL
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)
Query optimizer will use startup expression filter to evaluate the expression containing @.LocationId at run-time. This will result in only one of the UNION ALL branches being executed. You could use this approach. You can also put this query in a inline TVF and use it.
|||Thank you for your advise.
For your code, my understanding is you list both situations, either parameter has value or not has value, run both and combine the results. Is that correct?
My question is, if @.LocationId is not null, the condition "(@.LocationId is null or len(@.LocationId) = 0)" will be false, and it will AND with condition "table.LocationId in (SELECT id FROM tempTable)", the result will also be false right? Finally, it will AND with "table.id = @.Id", which will be false again. That will make the result set of
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)
be null?
Cause I have about 8-9 parameters need to be passed in, follow your code, I guess the stored procedure will be very long, right?
The link you gave provides a coding way to do the if statement to build the query at database.
Thank you.
|||Try the following:
Code Snippet
PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000),
@.Parameter2 INT,
@.Parameter3 NUMERIC(18, 2)
AS
SELECT
table.id
FROM
table
WHERE
table.id = @.Id AND
(table.LocationId in (SELECT id FROM tempTable) OR LEN(ISNULL(@.LocationId, '')) = 0) AND
(table.Value2 = @.Parameter2 OR @.Parameter2 IS NULL) AND
(table.Value3 = @.Parameter3 OR @.Parameter3 IS NULL) etc...
If you're checking VARCHARs, NVARCHARs, CHARs, to see if they're either NULL or have length = 0, put an ISNULL around the parameter and compare the length to 0.
For every other parameter you want to check, compare it to the table value and OR it with a check to see if it's null.
Each of those OR'd NULL checks needs to be in brackets with AND clauses otherwise you'll get strange results.
