SQL Query passing params to in() statement

[Guest_imported]

I have a stored proc I am writing in SQL Server2000 that needs to pass an unpredictably long number of values as a parameter to be used in an in() statement in the stored procedure. I have a stored proc that properly creates the string, but doesn't want to return the records. If I run a 'print' command to display the variable and then cut-and-paste it into the stored proc (as hard-coded), it works fine. What do I need to do to get SQL Server to understand that I am passing it a string to use in an in() statement?

example of stored procedure call:
ssDOCTORS_PATIENTS '1/1/2000', '12/31/2002', '23-24-28-67'

sample of stored procedure:
CREATE PROCEDURE test_proc

@StartDate As DateTime,
@EndDate As DateTime,
@aryProviders As varchar(20)

AS

/* Process the values in the array*/
DECLARE
@returnval varchar(50),
@nCntStart int,
@nCntEnd int,
@nLen int,
@nGetItem int,
@csfProviders varchar(20),
@sTmp Varchar(10),
@sItem VarChar(10)

BEGIN
select @returnval = 0
select @csfProviders = ''
select @aryProviders = @aryProviders + '-'
/* BEGIN TRANSACTION AddOrderTrans */
if (@@ERROR <> 0) Goto OnErrorTrap

/* get the length of the @items_ordered_Array string for later use. */
select @nLen = Len(@aryProviders)

/* set our counters to start with 1 as that will be the first position in the delimited string that we look at */
select @nCntStart = 1
select @nCntEnd = 1

/* enter into a loop where we will check each character in the @aryProviders string. */
WHILE (@nCntEnd <= @nLen)
BEGIN

/* Get the current character and store it in the temp variable @sTmp */
select @sTmp = SUBSTRING(@aryProviders,@nCntEnd,1)

/* if we find the '-' character, then we insert the value into the csfProviders variable*/
if (@sTmp = '-')
BEGIN

/* Get the value between our counter markers */
select @sItem = SUBSTRING(@aryProviders, @nCntStart, @nCntEnd - @nCntStart)

/* make sure there are no trailing blanks in the variable */
select @sItem = ltrim(rtrim(@sItem))

/* Insert this item into the storage array. */
/* select @csfProviders = @csfProviders + quotename(@sItem, '''') + ', '
*/ select @csfProviders = @csfProviders + '''' + @sItem + '''' + ', '

if (@@ERROR <> 0) Goto OnErrorTrap

/* reset the variables back to an empty string and prepare for another record */
select @sItem = ''
/* reset the start counter to the position after the current end position */
select @nCntStart = @nCntEnd + 1
END

/* increment our loop counter and move on to
the next value in the items_ordered_Array
string */
select @nCntEnd = @nCntEnd + 1

END

/* Strip the last 2 characters off the string */
select @csfProviders = Substring(@csfProviders, 1, datalength(@csfProviders) - 2)

/* if we get here (after the while loop),
all has gone well and we can commit the transaction */

/* COMMIT TRANSACTION AddOrderTrans */
goto OnExit

OnErrorTrap:

OnExit:


/* Enter values into the query */

/*select distinct res_last_name, res_first_name, res_mi_name, pr_address_line1, pr_address_line2, pr_city, pr_state, zip_pc
*/
select count(*)
from patients2 p, patient_alerts, responsible_parties, pat_resp_addresses, zips
where soc_sec = res_soc_sec
and res_soc_sec = pr_soc_sec
and pat_identity = pal_pat_identity
and pr_zip = zip_pc
and pr_address_label = 'H'
and primary_guarantor = 'Y'
and deceased_date is null
and pal_alert != 'deceased'
and exists (select patient_id from patservices
where patient_id = p.patient_id
and service_date >= @StartDate
and service_date <= @EndDate
and prov_id in (@csfProviders))
print @csfProviders
END
GO

>>>>Result<<<<<<<<
prints '23', '24', '28', '67'
Returns 0 records

 

[redlam]

Hi Special.
I briefly scanned your code so pardon me if I missed the boat...
I don't believe you can pass an array as described in your post. You will either have to parse the array into a temp table (table variable) and join it in your query or build your query dynamically. A simple example of a dynamic statement would be something like this:

declare @csfProviders varchar(100)
set @csfProviders = '1,2,3'
declare @sql varchar(8000)

select @sql =
'select * from patients where provider_id in (' + @csfProviders + ')'

exec(@sql)

Hope this helps.

 

[Guest_imported]

Redlam,

Thanks for your response. You are actually describing 2 different techniques. The technique of placing the text as a variable into the in() statement is the prefered method that I have been trying to develop and can not get working properly. The method you use in your code sample follows that route and we have tried that and several deviations of that method, but without any luck.

The second method you mention, saving the values to a temp table and then using them from there, has not been tried and I will pursue that as I can see where that would work. It's just strange that you can't place a variable of text inside of an in() statement and get that to work.

Thanks for the new idea!

Scott

 

[redlam]

Hey Scott.
Actually, I use the table variable method here - for some situations it works well, for others, a dynamic solution is better.
To implement a table variable solution I created a user defined function to parse the variable string and load it into a table variable. I then call this function in the FROM clause of my query. Like I said - sometimes this works beautifully, other times, not so much.
Good luck.

 

[tlbroadbent]

There is a very useful UDF available on the Microsoft website called fn_split. It accepts a delimited string as input and returns a table containing the values in the delimited string. It would suit your need very well.

Download the code for the function from Microsoft's website.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/TreatYourself.asp

Then create the function on your server. I created it in the master database. After you've created the function, you can use it in your SP. Here are a couple of examples using a sub-query from your SP.

select patient_id
from patservices
where service_date >= @StartDate
and service_date <= @EndDate
and prov_id In
(Select Value
From master.dbo.fn_split(@aryProviders,'-'))

select patient_id
from patservices s
join master.dbo.fn_split(@aryProviders,'-') f
on s.prov_id=f.value
where service_date >= @StartDate
and service_date <= @EndDate
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[tlbroadbent]

Check the replies provided in thread183-299906 and thread183-292139. If you are using SQL 2000, consider using the UDF, fn_split, that is described in those threads. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.