SP Parameters 2

[victork]

How can one pass a parameter into a stored procedure where the parameter is an indirect reference, not a value itself...

For example, in the SP, we would like to receive a parm, defined as @strList varchar(100), which will be in the form of various values....' '66', '67', '68' '...so on. The string could contain one or more of these values, known only at runtime.

Now the SP receiving this parameter does a select as follows:

SELECT col1, col2 WHERE col3 in (@strList)

Using this method finds 0 records. However, if you pass in the literal values. '66' and '67', the the select finds two rows.

Is there a way of passing in a string as a parameter which can be substituted in the where clause???

 

[jkb3]

You should be able to do this by taking in the column as a varchar(xx). Once the parameter is inside the stored procedure, use the LEN function to determine the length of the parameter, and then parse through the parameter using a for loop to obtain the values for col3 in the expression :
'select col1, col2 where col3 in (sqlparam)'

as the values are found, replace them with integer values using cast or convert. Place these values in a temp table.

Once all values are found for the input parameter, use a select from the temp table in your original expression.

so your final expression would be :
select col1, col2 from table1 where col3 in (select colA from TempTable1)



jkb3

 

[victork]

jkb3 -

Thanks, I'll give it a shot....