In clause in stored 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

SQL Server 2000 Forums

SQL Server Development (2000)

In clause in stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-27 : 09:05:21

Debashri writes "


I have a table with columns id(integer) & name(varchar)
and values as
id    name
===   ====
1      a
2      b
3      c
4      d

I have stored proc say USP_GetNamesForIDs as
========================================================
CREATE PROCEDURE [dbo].[USP_GetNamesForIDs]
@id varchar(30)
 AS
declare @str as varchar(100)
set @str='select * from table1 where id in(' + @id  + ')'
exec(@str)
GO
==============================================================
This works fine now my question is how do i write the stored proc to make it work for USP_GetIDsForNames
my requirement is to make the following query work through a stored proc with the in clause values as parameter.

select * from table1 where name in('a','b')

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-27 : 13:39:07

something like
set @str='select * from table1 where name in (''' + replace(@name, ',', ''',''') + ''')'
might need to make @str bigger.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources