To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q120999-2.shtml

The SQL Guru Answers your Questions...


Today's question comes from Eric L.:

I have a question for you. I have been working on this one for a very long time and still have no answers. I have a comma delimited string that I wish to a SQL Server 7.0 Stored Procedure, ex. '1,2,3,4'. I wish to then use this within the where clause like, select * from table where idno in (@stringpassed). I have not been able to get this to work with any methods that I have tried. Can this be done?

Eric,

Yep, you can use the EXEC command to do this. In addition to prefixing stored procedure calls, this command can be used to execute a SQL statement in a string or a string variable. I've included a sample stored proc below...

CREATE PROC Foo
  @stringpassed
AS
BEGIN
  DECLARE @SQL varchar(100)
  SET @SQL = 'SELECT * FROM table WHERE idno IN ' + @stringpassed

  EXEC(@SQL)
END

You can find more information in SQL Server books online.

Sean


Article Information
Article Title: SQL Guru: Using the IN Notation in Stored Procedures
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q120999-2.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers