How can I pass the contents of an IN() statement to a sp?

[Jerrycurl]

I have a stored procedure that runs a simple SELECT query.&nbsp;&nbsp;<br>It basically looks like this:<br><br>CREATE PROCEDURE sp_family @fam_ids varchar(255) AS<br>&nbsp;&nbsp;SELECT fam_id, name, address<br>&nbsp;&nbsp;FROM family <br>&nbsp;&nbsp;WHERE fam_id in ( @fam_ids )<br><br>In VB I construct a string of fam_ids that looks like this:<br>&quot;'100','101','432'&quot;<br>I want to pass that string to the stored procedure to use in the IN(@fam_ids) statement, but it doesn't work.&nbsp;&nbsp;It runs, but it doesn't return any records.&nbsp;&nbsp;I'd pass them as an array, but SQL Server doesn't allow that either.&nbsp;&nbsp;There must be a way to do this.&nbsp;&nbsp;Anyone?

 

[Jerrycurl]

Ok, I just found out that I can do this by using the EXEC() statement, but if anyone knows of another way, I'd still like to hear about it.&nbsp;&nbsp;Thanks.

 

[RichieLemons]

If you Build the select statement up as a stand alone string and then execute it then it should recognise the string items.<br><br>E.G.<br><br>SELECT @strSQL = ' SELECT fam_id, name, address '<br>SELECT @strSQL = @strSQL + ' FROM family '<br>SELECT @strSQL = @strSQL + ' WHERE fam_id in ( '<br>SELECT @strSQL = @strSQL + @fam_ids <br>SELECT @strSQL = @strSQL + ')'<br><br>EXEC (strSQL)

 

[foxdev]

Another thing that might be useful is using the ODBC Call statement, assuming you are going through ODBC.&nbsp;&nbsp;ODBC statements use the curly braces {} to differentiate them from server commands, and allow you to retrieve output variables. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[Invicta]

You could always pass each fam_id as a separate parameter but this would be more work than necessary and you'd be limited to 1024 fam_ids!&nbsp;&nbsp;<br><br>Another option would be send the string as a parameter and then use string functions to break it into individual components within the SP - again more work than necessary.<br><br>You could populate a separate table and use this in your query but this would require many more VB calls and, you've guessed it, it would be a lot more work.<br><br>You can think up any number of ways of achieving your goal but the EXEC statement is short and clean.<br>