Split in Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split in Procedure

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 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
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 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

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 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
Posting Yak Master

100 Posts

Posted - 2012-12-26 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-26 : 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
Posting Yak Master

100 Posts

Posted - 2012-12-26 : 06:54:45

how can we add this condition for isnull in

WHERE ',' + @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 in

WHERE ',' + @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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources