[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure Pass String Array for IN Clause

Posted on 2006-03-24
4
Medium Priority
?
1,254 Views
Last Modified: 2008-11-17
Hi,

I am trying to figure out if it is possible to pass a string array ie.(1,2,3,4) into a stored procedure parameter ie (@sList)
so that I dont have to build a SQL string dynamically from my C# code.

It would work as follows:

my_stored_procedure_call '1,2,3,4'

CREATE PROCEDURE my_stored_procedure_call
      @sList            varchar(255)
AS
      SELECT name,detail
      FROM table
      WHERE id IN(CAST(@sList AS int))
GO

I continuously get "Error converting data type varchar to int". Has anyone ever been successful in doing this through a stored procedure.

Please note that the example above is extremely simple compared to what I am actually trying to query, but the concept is the exact same.

TIA
0
Comment
Question by:deadferret01
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16283770
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16283814
or use dynamic sql

CREATE PROCEDURE my_stored_procedure_call
     @sList          nvarchar(255)
AS
     declare @nSQL nvarchar(2000)
     SET @sSql = '
     SELECT name,detail
     FROM table
     WHERE id IN(' + REPLACE(@sList, '''','') +')'
     
     exec sp_executeSQL @sSQL  -- or EXEC (@sSQL)
GO
0
 

Author Comment

by:deadferret01
ID: 16283901
Thank you very much! Worked perfectly.
0
 

Expert Comment

by:maplink
ID: 22978646
Perfect!

Just one thing, replace one line:
This
   declare @nSQL nvarchar(2000)
for
   declare @sSQL nvarchar(2000)

Thanks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question