CODEWITHSUNDEEP
sqlsql-serverasp.net-mvcc#-4.0
Khan
Khan

Reputation: 271

Stored Procedure with multi value parameter

I am trying to create a stored procedure which will accept multi value parameter for the where condition like below

ALTER PROC spGetExpectedSalesOrActiveSales @Divisions NVARCHAR(200), @var1 int
AS
BEGIN
    SELECT *
    FROM ABC
    WHERE x = '0' and z=@var1 
        AND y IN (@Divisions) 

END
exec spGetExpectedSalesOrActiveSales 'EE, MM' ,1

In-place of @Divisions if I use 'ABC' or 'ABC,XYZ' then its working good and when I am passing a single value through the API for the @Divisions then its executing that but when passing more than one value like 'ABC, XYX' then its simply displaying no records. The values I am passing through my API, its an array of string. I am not taking the @Divisions values from a query but from the API.

Upvotes: 1

Views: 4825

Answers (3)

RedgoodBreaker
RedgoodBreaker

Reputation: 310

there is something like Table Valued Parameters https://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

It allows passing array of elements to stored procedures.

DataTable _dt;
// create data table to insert items
_dt = new DataTable("Items");
_dt.Columns.Add("ItemID", typeof(string));
_dt.Columns.Add("Name", typeof(string));
_dt.Rows.Add(4, "SuperBowl 9 Hat");
_dt.Rows.Add(5, "SuperBowl 10 T-Shirt");
_dt.Rows.Add(6, "SuperBowl 13 Towel");
_dt.Rows.Add(7, "SuperBowl 14 Helmet");

then in stored procedure you have something like this

CREATE PROCEDURE [dbo].[InsertItemsTVP] @ItemTVP TVP_Items READONLY
AS
BEGIN
 INSERT INTO dbo.Items (ItemID, Name)
 SELECT ItemID, Name
 FROM @ItemTVP
END
GO

read about performace because sometimes its a lot faster using comma separated string than TVP

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You are trying to fetch query like this 

SELECT *
    FROM ABC
    WHERE x = '0'
        AND y IN ('ABC,XYZ')

And obviously you can't do select like above.

In this case you can go in 2 ways. One is Dynamic coding and another one is with User defind function.

Dynamic:

CREATE PROC spGetExpectedSalesOrActiveSales @Divisions NVARCHAR(200), @var1 int
AS
BEGIN
    DECLARE @QRY VARCHAR(MAX);
    SELECT @DIVISIONS = REPLACE (@DIVISIONS,' ','');
    SELECT  @DIVISIONS=''''+REPLACE(@DIVISIONS,',',''',''')+''''

    SELECT @QRY  = '
    SELECT *
    FROM ABC
    WHERE x = ''0'' AND z='+@var1 
        +'AND y IN ('+@DIVISIONS+')'

    --PRINT @QRY
    EXEC (@QRY)
END

Upvotes: 0

SQL_Deadwood
SQL_Deadwood

Reputation: 521

SQL Server does not support arrays. There are multiple ways to fake arrays, however, if you really must.

Upvotes: 1

Related Questions