without using dynamic sql - 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

SQL Server 2008 Forums

Transact-SQL (2008)

without using dynamic sql

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-07-06 : 05:54:18

declare @varId varchar(50)
declare @sql varchar(50)
set @varId='1,2'

set @sql='select * from RS_RecordDetails
where RecordID in ('+@varId+')'
print @sql

how to avoid the dynamic sql and pass the parameters to query?

Pls let me know any way asap

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 05:57:57

use CSVTable or fnParseList


select *
from   RS_RecordDetails
where  RecordID in (select numberval from CSVTable(@varId)))

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-06 : 08:20:13

Search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-07-06 : 14:27:57

Here

http://tinyurl.com/m67um7

An infinite universe is the ultimate cartesian product.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 01:41:05

quote:
Originally posted by cat_jesus

Here

http://tinyurl.com/m67um7

An infinite universe is the ultimate cartesian product.

Good one

Madhivanan

Failing to plan is Planning to fail

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-07-07 : 02:04:26

quote:
Originally posted by madhivanan

quote:
Originally posted by cat_jesus

Here

http://tinyurl.com/m67um7

An infinite universe is the ultimate cartesian product.

Good one

Madhivanan

Failing to plan is Planning to fail

Hello All,
Thanks for posting, All posts are very useful for me.khtan's post helped me for solving major issue

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-07 : 04:38:07

u can use like operator or patindex function

select * from RS_RecordDetails
where '%,' + @varId + ',%' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

or PATINDEX('%,' + CAST(RecordID AS VARCHAR(32))+',%', ',' + @varId + ',') > 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 05:19:51

quote:
Originally posted by bklr

u can use like operator or patindex function

select * from RS_RecordDetails
where '%,' + @varId + ',%' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

or PATINDEX('%,' + CAST(RecordID AS VARCHAR(32))+',%', ',' + @varId + ',') > 0

You dont need % around the variable

select * from RS_RecordDetails
where ',' + @varId + ',' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-07 : 05:21:58

quote:
Originally posted by madhivanan

quote:
Originally posted by bklr

u can use like operator or patindex function

select * from RS_RecordDetails
where '%,' + @varId + ',%' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

or PATINDEX('%,' + CAST(RecordID AS VARCHAR(32))+',%', ',' + @varId + ',') > 0

You dont need % around the variable

select * from RS_RecordDetails
where ',' + @varId + ',' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

Madhivanan

Failing to plan is Planning to fail

k just copy & paste error
in patindex i didn't surrond the '%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 05:34:33

quote:
Originally posted by bklr

quote:
Originally posted by madhivanan

quote:
Originally posted by bklr

u can use like operator or patindex function

select * from RS_RecordDetails
where '%,' + @varId + ',%' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

or PATINDEX('%,' + CAST(RecordID AS VARCHAR(32))+',%', ',' + @varId + ',') > 0

You dont need % around the variable

select * from RS_RecordDetails
where ',' + @varId + ',' LIKE '%,' + CAST( RecordID AS VARCHAR(32)) +',%'

Madhivanan

Failing to plan is Planning to fail

k just copy & paste error
in patindex i didn't surrond the '%'

Ok. Fine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources