Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause.
Ex: Where x In(@parametername)
the x column is an integer. How can one work around this???
create procedure sp_storedprocedure1
(
@parametername nvarchar(max)
)
as
declare @sql nvarchar(max)
set @sql = 'select * from [dbo].[employees] Where id In (' + convert(nvarchar(max), @parametername) + ')'
exec sp_executesql @sql
go
exec sp_storedprocedure1 '1,3,6'
None
0 Points
282 Posts
Passing comma delimited parameter to SP
Apr 14, 2005 06:25 PM|ech01|LINK
Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause.
Ex: Where x In(@parametername)
the x column is an integer. How can one work around this???
Thanks![st]
None
0 Points
198 Posts
MVP
Re: Passing comma delimited parameter to SP
Apr 15, 2005 12:03 AM|AdamMachanic|LINK
SQL Server MVP
Contributor
4232 Points
1147 Posts
Re: Passing comma delimited parameter to SP
Jun 01, 2015 04:41 AM|Kulrom|LINK
Please be careful when you use a comma delimited parameter/list
My website: ASP.NET Custom Software Development
Contributor
6101 Points
1449 Posts
Re: Passing comma delimited parameter to SP
Jun 01, 2015 08:18 AM|eralper|LINK
hello ech01,
Please try following dynamic sql execution
You can build a dynamic SQL Select statement, then execute it by calling the sp_executesql SQL statement
SQL Server 2017
Participant
1172 Points
323 Posts
Re: Passing comma delimited parameter to SP
Jun 04, 2015 08:37 AM|jbkumar|LINK
Hi,
you can use a Table valued Function in your store procedure to solve your problem.
try this sample code:
Executing with sample value:
Executing with parameter: