March 5, 2008 at 2:14 am
hello,
is this acceptable in a stored procedure?
CREATE Procedure Search
@TicketID int
AS
BEGIN
SELECT * from Tickets
WHERE UserID= '10' AND (@TicketID>0?TicketID=@TicketID:true)
END
GO
i am having a problem with the ?: (Incorrect syntax near '?'.)
thanks in advance
Abd
March 5, 2008 at 2:23 am
Are you looking for something like this?
IF (@TicketID>0)
BEGIN
SELECT * from Tickets
WHERE UserID= '10'
AND TicketID=@TicketID
END
ELSE IF (@TicketID<=0)
BEGIN
SELECT * from Tickets
WHERE UserID= '10'
END
March 5, 2008 at 2:40 am
You could also use
SELECT *
FROM Tickets
WHERE UserID = '10'
AND ( @TicketID <= 0
OR ( @TicketID > 0
AND TicketID = @TicketID
)
)
A few comments:
1: If the UserID is a numeric type, you may want to use UserID = 10.
2: Select * is a bad practice (although I assume you included it only for the sake of the example, having shorter examples is more important :))
3: This is a controversial topic, but it is common to use NULLs for extremal values (instead of a TicketID that is 0)
Regards,
Andras
March 5, 2008 at 2:54 am
Thank you Andras and Nisha for your replies
In fact Andras, yes, this is a short example of my sp
but the problem here i can not use the IF statement, because i will have a 2-3 pages code, so i went to the '?:'
my code would rather be more like:
...
AND (@TicketID>0?TicketID=@TicketID:true) AND (@Type is null?true:Type=@Type) AND ...
what am doing is that i have more than 5 input parameters, and if an input parameter is null, i don't want to include it in the where statement, note that i dont know which input parameter(s) would be null (they, all, also can have values)
i used true just to skip the statement in the parenthesis (AND true => won't hurt anyone ;))
i hope i made it clear
i appreciate any new suggestions
🙂
Abd
March 5, 2008 at 3:04 am
--try with this code
CREATE Procedure Search
@TicketID int
AS
BEGIN
IF (@TicketID>0)
BEGIN
SELECT * from Tickets
WHERE UserID= '10' AND TicketID=@TicketID
END
ELSE
BEGIN
SELECT * from Tickets
WHERE UserID= '10'
END
END
GO
March 5, 2008 at 3:15 am
abd (3/5/2008)
Thank you Andras and Nisha for your repliesIn fact Andras, yes, this is a short example of my sp
but the problem here i can not use the IF statement, because i will have a 2-3 pages code, so i went to the '?:'
my code would rather be more like:
...
AND (@TicketID>0?TicketID=@TicketID:true) AND (@Type is null?true:Type=@Type) AND ...
what am doing is that i have more than 5 input parameters, and if an input parameter is null, i don't want to include it in the where statement, note that i dont know which input parameter(s) would be null (they, all, also can have values)
i used true just to skip the statement in the parenthesis (AND true => won't hurt anyone ;))
i hope i made it clear
i appreciate any new suggestions
🙂
If you can use NULLs then there are other solutions, e.g.:
ISNULL(@TicketID-TicketID, 0) = 0
If the @TicketID is null, the first expression will be null, and the left hand will be set to 0 because of the ISNULL, so the condition will evaluate to true. If @TicketID is not null, it will check the difference, if @TicketID and TicketID equal, the condition will evaluate to true, otherwise to false (I assume that TicketID is never null).
This will keep your expression compact, but performance may become an issue (you are using a function in the where clause, so indexes will not be properly used). Personally I'd just rewrite the condition with proper ANDs and ORs 🙂
Regards,
Andras
March 5, 2008 at 3:19 am
nice solution
but does it work for nvarchar parameters?
Abd
March 5, 2008 at 3:47 am
abd (3/5/2008)
nice solutionbut does it work for nvarchar parameters?
No, it does not 🙂 nvarchars seems to have become a new requirement for this problem 🙂
There is of course another alternative if your statement gets even more complex, and it is to use dynamic SQL.
Regards,
Andras
March 5, 2008 at 3:53 am
😀
I will go with dynamic SQL, but implicitly i hope to find another way ! :hehe:
Thanks anyway 🙂
Abd
March 5, 2008 at 5:54 am
abd (3/5/2008)
Thank you Andras and Nisha for your repliesIn fact Andras, yes, this is a short example of my sp
but the problem here i can not use the IF statement, because i will have a 2-3 pages code, so i went to the '?:'
my code would rather be more like:
...
AND (@TicketID>0?TicketID=@TicketID:true) AND (@Type is null?true:Type=@Type) AND ...
what am doing is that i have more than 5 input parameters, and if an input parameter is null, i don't want to include it in the where statement, note that i dont know which input parameter(s) would be null (they, all, also can have values)
i used true just to skip the statement in the parenthesis (AND true => won't hurt anyone ;))
i hope i made it clear
i appreciate any new suggestions
🙂
We use this approach for variable parameters that may or may not be supplied. It works very well. It's set based. It will take advantage of indexes. It doesn't cause excessive recompiles.
... AND CASE WHEN @MyParam IS NULL THEN 1
WHEN a.ColumnName = @MyParam THEN 1
ELSE 0
END = 1
Best of all, no dynamic SQL is used.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 5, 2008 at 7:04 am
Thank you :w00t:
You are the best, Grant.
Abd
March 5, 2008 at 12:26 pm
Grant Fritchey (3/5/2008)
We use this approach for variable parameters that may or may not be supplied. It works very well. It's set based. It will take advantage of indexes. It doesn't cause excessive recompiles.... AND CASE WHEN @MyParam IS NULL THEN 1 WHEN a.ColumnName = @MyParam THEN 1 ELSE 0 END = 1
Best of all, no dynamic SQL is used.
Did the 2 DBAs with the 8 years of experience even come close to answering something like that, Grant? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2008 at 12:30 pm
Now you're being mean.
😎
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2008 at 4:31 am
Well... maybe a little evil 🙂 It is a good "tie breaker" question.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 5:23 am
For complex searches or reports with many optional parameters dynamic SQL is often the best solution. I would carefully evaluate the query plans that you get with dynamic SQL vs. what you get with something like this:
AND
CASE
WHEN @MyParam IS NULL THEN 1
WHEN a.ColumnName = @MyParam THEN 1
ELSE 0 END = 1
Also, since no one else has suggested it, you might use a hybrid approach where you identify the most common parameter combinations that are actually used, code selects specifically for those, and use the dynamic SQL or CASE approach for the rarely used combinations.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply