CODEWITHSUNDEEP
sqlstored-procedures
Dima R.
Dima R.

Reputation: 997

Stored procedure with parameter

I have to pass a parameter to stored procedure in SQL Server. Parameter value is to be used as comma separated list in the "IN" clause:

So something like this:

... ... 
@myParam as varChar(max)
.... .. 

select *
from myTable 
where someValue in (@myParam).

So, I think, I have to somehow pass in the parameter that is a sql string itself. Stored procedure is called from Java with Hibernate call.

Any advise is appreciated?

Upvotes: 0

Views: 173

Answers (4)

Santiago Regojo
Santiago Regojo

Reputation: 405

You can use a recursive function or cte to extract the data and then use a Join or a Where in (select ....) Notice the current algorithm needs a delimiter at the end. If you use a UDF you could pass @separator and @source as parameters

DECLARE @separator  varchar(1024) = ','
DECLARE @source varchar(1024) = 'valueA,ValueB,ValueC,'

;WITH cteDelimitedExtractor (item, delimitedString) AS
(
    SELECT
        SUBSTRING(@source, 1, CHARINDEX(@separator, @source ) - 1)  AS ititem,
        SUBSTRING(@source, CHARINDEX(@separator, @source ) + 1, LEN(@source) - CHARINDEX(@separator, @source))  as delimitedString
    UNION ALL
    SELECT
        SUBSTRING(delimitedString, 1, CHARINDEX(@separator, delimitedString ) - 1)  AS item,
        SUBSTRING(delimitedString, CHARINDEX(@separator, delimitedString ) + 1, LEN(delimitedString) - CHARINDEX(@separator, delimitedString ))  as delimitedString
    FROM cteDelimitedExtractor
    WHERE delimitedString <> ''
)
SELECT item FROM cteDelimitedExtractor

Upvotes: 1

Adarsh Shah
Adarsh Shah

Reputation: 6775

You can create a function to convert comma separated value to table and then you can use table to do a SELECT within an IN CLAUSE or you can also do a JOIN if you want. This way you can use it for any datatype i.e. Comma separated varchar or int value. (You will just have to make the function generic)

See below URL:

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

Upvotes: 1

Nrupesh
Nrupesh

Reputation: 63

You should be able to pass the string value from wherever you are calling the procedure.

Upvotes: 0

hanskishore
hanskishore

Reputation: 361

Try something like this:

DECLARE @SQL VARCHAR(5000)
SET @SQL = 'SELECT *
            FROM myTable
            WHERE somevalue IN ('+ @myParam+')'
EXEC (@SQL)

Hope this helps.

Upvotes: 2

Related Questions