pass parameter of sp in condition in seelct query

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

Transact-SQL (2005)

pass parameter of sp in condition in seelct query

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-04 : 04:20:45

Dear Member

i have write a following store procedure in sql
create sp
@cust varchar(200)

AS
BEGIN

select user from tab where user in (@cust)

end
so how i pass parameter two or more user names in table

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-04 : 04:56:36

different ways .. one of them is to send the usernames as a string with commas e.g.
'username1,username2,...,usernameN'

However there should be a conversion code in your SP which convert this comma separated string into tabular form....
e.g.

create sp
@cust varchar(200)
AS
BEGIN

CSV to Table conversion code should be here ... and once the data is pushed into the dummy table use that table in the below select clause

select user from tab where user in (select usernames from dummyTable)
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-04 : 05:20:26

or

select user from tab where ','+@cust+',' like '%,'+cast(user as varchar(100))+',%'

Madhivanan

Failing to plan is Planning to fail

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-04 : 05:39:33

@mad perfect use of the positions :D thumbs up

Cheers!

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-04 : 05:42:42

thnaks both of repaly
but my query we are write a select query in sql like

select user from tab where user in('a','b','c')
this same query write in sp and i have pass parameter to username in condition in select query so how can i pass parameter

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-04 : 06:01:04

quote:
Originally posted by MIK_2008

@mad perfect use of the positions :D thumbs up

Cheers!

Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources