| Author |
 Topic  |
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
 Posted - 12/26/2012 : 04:37:14
|
i want to Split my parameter values in Sql Procedure for Condition Sattement
my Parameter value is '1,2,3,4,5,6'
i want to use it like this
Create Procedure abc
@paramater Varchar
AS
BEGIN
SELECT
*
FROM
DEPARTMENT
WHERE
DEPARTMENTID IN (@Parameter)
what is the best Solution for this
Thanks in Advance
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/26/2012 : 04:59:55
|
Method1: By splitting that input parameter into separate values and then putting condition in WHERE clause
WHERE DEPARTMENTID IN (SELECT Val FROM dbo.ParseValues(@paramer, ','))
Note: Refer this link for UDF
http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html
Method2: 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
India
47189 Posts |
Posted - 12/26/2012 : 05:03:01
|
quote:
Originally posted by asif372
i want to Split my parameter values in Sql Procedure for Condition Sattement
my Parameter value is '1,2,3,4,5,6'
i want to use it like this
Create Procedure abc
@paramater Varchar(<length>)
AS
BEGIN
SELECT
*
FROM
DEPARTMENT
WHERE
DEPARTMENTID IN (@Parameter)
what is the best Solution for this
Thanks in Advance
Always remember to specify a length while casting to varchar
see
http://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
Posted - 12/26/2012 : 05:40:44
|
Bandi thanks for your Quick responce
WHERE ',' + @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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/26/2012 : 06:35:44
|
quote:
Originally posted by asif372
Bandi thanks for your Quick responce
WHERE ',' + @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
Yak Posting Veteran
Pakistan
94 Posts |
Posted - 12/26/2012 : 06:54:45
|
how can we add this condition for isnull in
WHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%'
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/26/2012 : 07:00:10
|
quote:
Originally posted by asif372
how can we add this condition for isnull in
WHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%'
WHERE ',' + @Parameter + ',' LIKE '%,' + CAST(DEPARTMENT_ID AS varchar) + ',%' OR DEPARTMENT_ID IS NULL
--
Chandu |
|
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
Posted - 12/26/2012 : 14:40:34
|
| no man i mean if my parameter is null then what will be the code |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/26/2012 : 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
India
47189 Posts |
Posted - 12/26/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Split in Procedure
