evaluating a stored proc's string paramater as an argument for a query

[Norwich]

Hi,

I'm trying to write a stored procedure where one of the string parameters passed is 'evaluated' to be used within a query in that procedure.

Giving a simplified example may be clearer.

I have a table which has one of it's fields contain a 'code' from 1 to 99.

I want the sp to return all rows where that code is equal to a string containing all required 'code' values.

so, if I want the query to by run within the sp to be:

select * from mytable where code in ('2','23','34','35','36','37','67','68','69','90','91')

as you can see, it'll be useful to pass this criteria as a string paramater rather than have 100 boolean paramters for each possible value in the list.

However, I can't work out how to evaluate (if that's the right term) a string paramter within a query. Eg:

CREATE PROCEDURE [sp_dmRequest]
@code varchar(255)
AS
SELECTT * from mytable where code in ( EVALUATE(@code) )


Any thoughts on how to achieve this?

Thanks

 

[Zukkster]

Are you just looking to filter your results on a sub query that selects the paramters of the main query

SELECT * FROM table1 WHERE field IN (SELECT field FROM Table2)

Rather than passing all the cases that as a list of possibilities you create a subquery.

I may not have understood you clearly

 

[Norwich]

Hi,

Thanks for the reply but that's not quite what i meant.

I basically want the sp to be called with any number of 'code' arguments to be used in the query. Now the table the dataset is to be pulled from has a field called 'code' but the sp may be called looking for any number of code values.

eg. the first use may just be trying to pull a dataset where 'code' = '2'. Next time, they sp may require nearly all 100 possible values for 'code'.

eg:

select * from mytable where code in ('2')

and

select * from mytalbe where code in ('1','2','3','4','5','6','10','11',20','22','24',.....etc

what I'm after is the simplest way to pass the values used in the query.

I think the easiest way would be to pass the values as a string. But I don't know how to use that string parameter within the query itself.

Hope this makes sense.

 

[Zukkster]

Yes if you try and pass in an integer variable to the SQL statement you will get a 'data type mismatch' error when you try and create the sting variable by concatentating text with an integer.

Without knowing your user interface and how you are calling the procedure I guess you'll build the @code variable in a loop and pass it into the stored procedure. Something like.

CREATE [sp_dmRequest] @code nvarchar(255)

as

DECLARE @SQL nvarchar(255)

SET QUOTED_IDENTIFIER ON

SET @SQL = "SELECT * FROM tblA WHERE user_id in (" + @code + ")"
EXEC(@SQL)

SET QUOTED_IDENTIFIER OFF

The set quoted identifiers deals with ' and " quotes because you'll be using ' in the SQL statement

It took me a few frustrated hours to work that out the data type issue and the fact that you have to convert it to a text variable

 

[SQLSister]

This FAQ will help
Passing a list of values to a Stored Procedure
faq183-3979

 

[donutman]

I've seen several of these threads on creating dynamic SQL in SPs. What is the point? I don't know about other programming languages, but VB provides you with access to a "command object" for which you can specify its type as "command text" so that you can write your own SQL statement and submit the entire statement to the SQL engine for execution. Other than some "advanced" reason for avoiding the ADOs implementation of the command object what could possibly be the advantage of using a SP in that situation. The SP can't precompile an optimized dynamic SQL statement. Unless the DBA in your company insists on having all access to tables encapsulated by SPs...I just don't get it. It seems outright silly to build half of the SQL statement in frontend code and then the other half in a SP on the server.
-Karl