March 21, 2012 at 12:17 pm
Hello Everyone,
I need some help with optional comma separated value to be passed to a parameter.
CREATE PROCEDURE dbo.usp_XXXXXXXXX_Search
@distance int,
@first_name varchar(35) = NULL,
@last_name varchar(35)= NULL,
@OPTIONAL_CSV_PARAMETER VARCHAR(30) = NULL
AS
BEGIN
(SELECT TOP 100 ABC, XYZ, ... etc
from Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
LEFT JOIN TABLE3 on Table2.ID = Table3.ID and Table3.ID in
(SELECT * FROM dbo.fn_Split( @OPTIONAL_CSV_PARAMETER ,','))
)
Now if the parameter is passed with a value like '10, 20, 30', this works fine.
But if the parameter is NULL, then it doesn't return anything. which is not the expected result if the parameter is optional.
Any thoughts on how these kind of scenarios should be handled?
I do not want to go with Dynamic SQL for performance and security reasons.
Please help me with your suggestions.
March 21, 2012 at 12:23 pm
Something like this:
(SELECT TOP 100
ABC,
XYZ,
... etc
from
Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID
LEFT JOIN TABLE3
on (Table2.ID = Table3.ID
and (Table3.ID in (SELECT * FROM dbo.fn_Split( @OPTIONAL_CSV_PARAMETER ,','))
OR @OPTIONAL_CSV_PARAMETER is null))
)
March 21, 2012 at 12:57 pm
kalyan.ch (3/21/2012)
I do not want to go with Dynamic SQL for performance and security reasons.
Depending on your split function you might take a look at the string splitter link in my signature. It will very likely help you quite a bit more with performance on your splitting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply