Passing comma delimited parameter to SPRSS

• • Reply
  ech01

  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]

• • Reply
  AdamMachanic

  None

  0 Points

  198 Posts

  MVP

  Re: Passing comma delimited parameter to SP

  Apr 15, 2005 12:03 AM|AdamMachanic|LINK

  http://www.sommarskog.se/arrays-in-sql.html
  Adam Machanic
  SQL Server MVP
• • Reply
  Kulrom

  Contributor

  4012 Points

  1081 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

  Remember to click Mark As Answer when you get a reply which answers your question.
  My Blog: ASP.NET Stuff
  
• • Reply
  eralper

  Contributor

  6041 Points

  1428 Posts

  Re: Passing comma delimited parameter to SP

  Jun 01, 2015 08:18 AM|eralper|LINK

  hello ech01,

  Please try following dynamic sql execution

  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'

  You can build a dynamic SQL Select statement, then execute it by calling the sp_executesql SQL statement

  T-SQL programming SQL Server Tutorials
  SQL Server 2017
  
• • Reply
  jbkumar

  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:

  CREATE FUNCTION [dbo].[fnSplitString] 
  ( 
      @string NVARCHAR(MAX), 
      @delimiter CHAR(1) 
  ) 
  RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
  ) 
  BEGIN 
      DECLARE @start INT, @end INT 
      SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
      WHILE @start < LEN(@string) + 1 BEGIN 
          IF @end = 0  
              SET @end = LEN(@string) + 1
         
          INSERT INTO @output (splitdata)  
          VALUES(SUBSTRING(@string, @start, @end - @start)) 
          SET @start = @end + 1 
          SET @end = CHARINDEX(@delimiter, @string, @start)
          
      END 
      RETURN 
  END
  

  Executing with sample value:

  Select * from TableName where Columnname in (
  select Splitdata from dbo.fnSplitString('1,2,3',','))
  
  

  Executing with parameter: 

  Select * from TableName where Columnname in (
  select Splitdata from dbo.fnSplitString(@parametername,','))