locked
Passing comma delimited parameter to SP RRS feed

  • Question

  • User136215837 posted

    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]

    Thursday, April 14, 2005 6:25 PM

All replies

  • Friday, April 15, 2005 12:03 AM
  • User-595703101 posted

    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

    Monday, June 1, 2015 8:18 AM
  • User1255597977 posted

    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,','))

    Thursday, June 4, 2015 8:37 AM