Click here to Skip to main content
15,747,356 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to create a stored procedure and pass in a parameter, here it is:
SQL
create procedure GetEmployeeRole @role varchar(20)
as 
select * from Employee where employeeRole in ('Tech', 'Manager')
GO


I want to use @role to replace ('Tech', 'Manager'), so the query will be
SQL
select * from Employee where employeeRole in (@role)


So I can pass in whatever role I want, I passed in
SQL
@role = 'Tech' + ',' + 'Manager'
, It didn't work, so what did I do wrong? Thanks.
Posted
Updated 18-Sep-14 7:02am
v3
Comments
PIEBALDconsult 18-Sep-14 13:05pm    
If SQL Server, look into using a Table Valued Parameter.
http://www.codeproject.com/Tips/93248/SQL-Server-User-Defined-Table-Types-and-Table

Try this
Approach 1:
SQL
create procedure GetEmployeeRole @role varchar(20)
as
declare @sqlQuery nvarchar(max) 
set @sqlQuery = 'select * from Employee where employeeRole in ('''+replace(@role,',',''',''')+''')'
exec sp_executesql @sqlQuery
GO



Approach 2:

Passing comma delimited parameter to stored procedure[^]

Hope it helps.
 
Share this answer
 
v2
Comments
Manas Bhardwaj 18-Sep-14 15:34pm    
Yes, this would work +5!

But, please see my alternative!
Member 1284721 19-Sep-14 22:14pm    
thank you, this is very helpful
ChauhanAjay 21-Sep-14 19:21pm    
request you kindly accept the answer as solved.
I would do it bit differently.

First, create a function to split the input variable:

CREATE FUNCTION split(
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) 
RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END


Next, the query would become something like this:

SQL
SELECT * INTO #SplitResults FROM dbo.split(@role, ',')

SELECT
    *
FROM
    Employee INNER JOIN #SplitResults ON
        Employee.employeeRole = #SplitResults.Val


DROP TABLE #SplitResults


And you can pass the @role as:

@role = 'Tech,Manager'
 
Share this answer
 
Comments
ChauhanAjay 18-Sep-14 21:30pm    
Thanks for the upvote.
Your approach will also work and +5 for that too.
Member 1284721 19-Sep-14 22:13pm    
Thank you for the solution, it's very helpful
SQL
DECLARE @role VARCHAR(MAX)
SET @role = '''Tech''' + ',' + '''Manager'''
SELECT @role
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900