Author |
Topic |
asif372
Posting Yak Master
100 Posts |
Posted - 2012-12-26 : 04:37:14
|
i want to Split my parameter values in Sql Procedure for Condition Sattementmy Parameter value is '1,2,3,4,5,6'i want to use it like this Create Procedure abc @paramater VarcharASBEGINSELECT*FROMDEPARTMENTWHEREDEPARTMENTID IN (@Parameter)what is the best Solution for thisThanks in Advance |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-26 : 04:59:55
|
Method1: By splitting that input parameter into separate values and then putting condition in WHERE clauseWHERE DEPARTMENTID IN (SELECT Val FROM dbo.ParseValues(@paramer, ','))Note: Refer this link for UDF http://visakhm.blogspot.in/2010/02/parsing-delimited-string.htmlMethod2: Using LIKE operator ( Use only if your input data looks like 1,2,3,4 i.e. with out any spaces between input)WHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%'--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-26 : 05:03:01
|
quote: Originally posted by asif372 i want to Split my parameter values in Sql Procedure for Condition Sattementmy Parameter value is '1,2,3,4,5,6'i want to use it like this Create Procedure abc @paramater Varchar(<length>)ASBEGINSELECT*FROMDEPARTMENTWHEREDEPARTMENTID IN (@Parameter)what is the best Solution for thisThanks in Advance
Always remember to specify a length while casting to varcharseehttp://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2012-12-26 : 05:40:44
|
Bandi thanks for your Quick responceWHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%'This works fine if i want to add condition isnull in parameter then what is the procedure for that |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-26 : 06:35:44
|
quote: Originally posted by asif372 Bandi thanks for your Quick responceWHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%'This works fine if i want to add condition isnull in parameter then what is the procedure for that
In that case, Method1 is correct. Add one more condition DEPARTMENT_ID IS NULL--Chandu |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2012-12-26 : 06:54:45
|
how can we add this condition for isnull inWHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%' |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-26 : 07:00:10
|
quote: Originally posted by asif372 how can we add this condition for isnull inWHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%'
WHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%' OR DEPARTMENT_ID IS NULL--Chandu |
|
|
asif372
Posting Yak Master
100 Posts |
Posted - 2012-12-26 : 14:40:34
|
no man i mean if my parameter is null then what will be the code |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-26 : 15:04:02
|
If the parameter is null, what is the output you are expecting to get? All department_id's, or none at all, or all rows where department_id is null? If it is the last, WHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%' OR ( DEPARTMENT_ID IS NULL AND @Parameter IS NULL) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-26 : 23:12:25
|
quote: Originally posted by asif372 no man i mean if my parameter is null then what will be the code
WHERE (',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar(10)) + ',%' OR DEPARTMENT_ID IS NULL OR @Parameter IS NULL) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|