passing multiple varchar values to parameter in procedure

Question

sp_panelistid1 '585','201401','108972',''4','5''

alter procedure sp_panelistid1
 (
  @branch int,
  @yearweak int,
  @id int ,
  @branchid varchar(10))   
as 
print @branchid   
select f.lydelse as QuestionText, f.id as QuestionID, i.artal as Year, i.vecka as Week, i.id as Intervjuperson,   
 b.beskrivning as Branch, b.id as BranchID, v.beskrivning as Brand, v.id as BrandID, s.regperson as Buss, f.land as CountryID   
  , vi.NepaVikt as Weight , cp.CintPanelistId  
from fraga f   

inner join svar s on s.fraga = f.id   
inner join bransch b on b.id = f.bransch   
inner join varumarke v on v.id = s.varumarke   
inner join intervjuperson i on i.id = s.intervjuperson   
inner join vikt vi ON f.Bransch = vi.Bransch AND s.Intervjuperson = vi.Intervjuperson   
inner join CintPanelistIntervjuperson cp on s.Intervjuperson=cp.Intervjuperson  

where f.bransch = @branch 
and (100*i.artal)+i.vecka > @yearweak  
and f.land = 1 and f.id=@id 
and  v.beskrivning in  (@branchid)

I need to pass multiple values in @branch id how do I pass parameters such that it works in 'IN condition ' like v.beskrivning in ('4','5','6','7 = Stämmer helt')

Duplicate question [See here](http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure) and [here](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) — Max, Sep 16 '14 at 09:31
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Sep 16 '14 at 09:57

score 0 · Answer 1 · answered Sep 16 '14 at 09:36

If you pass comma separated ids like this - '1, 2, 3' and if you want to use with "In" clause then you have to use Split() function. like this -

SELECT f.lydelse AS QuestionText
    ,f.id AS QuestionID
    ,i.artal AS Year
    ,i.vecka AS Week
    ,i.id AS Intervjuperson
    ,b.beskrivning AS Branch
    ,b.id AS BranchID
    ,v.beskrivning AS Brand
    ,v.id AS BrandID
    ,s.regperson AS Buss
    ,f.land AS CountryID
    ,vi.NepaVikt AS Weight
    ,cp.CintPanelistId
FROM fraga f
INNER JOIN svar s ON s.fraga = f.id
INNER JOIN bransch b ON b.id = f.bransch
INNER JOIN varumarke v ON v.id = s.varumarke
INNER JOIN intervjuperson i ON i.id = s.intervjuperson
INNER JOIN vikt vi ON f.Bransch = vi.Bransch
    AND s.Intervjuperson = vi.Intervjuperson
INNER JOIN CintPanelistIntervjuperson cp ON s.Intervjuperson = cp.Intervjuperson
WHERE f.bransch = @branch
    AND (100 * i.artal) + i.vecka > @yearweak
    AND f.land = 1
    AND f.id = @id
    AND v.beskrivning IN (SELECT items FROM dbo.split(@branchid, ','))

Note: Split function is not in-built function. Its used defined table-valued function. and here is the split function.

and here is the function.

CREATE FUNCTION [dbo].[Split] (
    @String NVARCHAR(4000)
    ,@Delimiter CHAR(1)
    )
RETURNS @Results TABLE (Items NVARCHAR(4000))
AS
BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE NVARCHAR(4000)

    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    SELECT @INDEX = 1

    IF @String IS NULL
        RETURN

    WHILE @INDEX != 0
    BEGIN
        -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
        SELECT @INDEX = CHARINDEX(@Delimiter, @STRING)

        -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
        IF @INDEX != 0
            SELECT @SLICE = LEFT(@STRING, @INDEX - 1)
        ELSE
            SELECT @SLICE = @STRING

        -- PUT THE ITEM INTO THE RESULTS SET
        INSERT INTO @Results (Items)
        VALUES (@SLICE)

        -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
        SELECT @STRING = RIGHT(@STRING, LEN(@STRING) - @INDEX)

        -- BREAK OUT IF WE ARE DONE
        IF LEN(@STRING) = 0
            BREAK
    END

    RETURN
END

how will i pass parameter cause my value is ('4','5','6','7 = Stämmer helt') i am getting jumbled with the single Quotes '' — user2583182, Sep 16 '14 at 09:53
@user2583182: In this case you just have to pass value with single comma separate - like this - `('4,5,6')`. so that you can use it in split function. — Krishnraj Rana, Sep 16 '14 at 09:56
sp_panelistid1 '585','201401','108972',('4,5,6') is this the way to pass value im gettin error :( — user2583182, Sep 16 '14 at 10:28
@user2583182: Remove the bracket. it should be like this - `sp_panelistid1 '585','201401','108972','4,5,6'` — Krishnraj Rana, Sep 16 '14 at 10:32

passing multiple varchar values to parameter in procedure

1 Answers1