CODEWITHSUNDEEP
c#sql
raja
raja

Reputation: 11

How to pass multiple values in a parameter with stored procedure

This is my stored procedure.Someone says using TVP i can get ouput i want. But i am new to sql. So please someone help me, how to pass multiple disposeid's and multiple receiveid's which shown below.Right now i need output only using that I can able to understand what is tvp so someone please edit my SP to pass multiple values in it.

 USE [Test]
    GO

    /****** Object:  StoredProcedure [dbo].[up_Get_Customers]    Script Date: 09/23/2015 19:10:23 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO




    ALTER PROC [dbo].[up_Get_Customers]
    (
        @DisposeId int

    )
    AS
    BEGIN

        SELECT C1.DisposeDate,C1.DisposeID,C1.ReceiveDate,C1.ReceiveID
        FROM Customers C1 
        LEFT JOIN Customers C2 ON C1.DisposeID=C1.ReceiveID 
        WHERE @DisposeId IS NULL OR (C1.DisposeID in (@DisposeId,','))
       END

    GO

Upvotes: 1

Views: 1083

Answers (1)

Goody
Goody

Reputation: 391

Well, this is something new I've learned today, with a tiny amount of Googling I came up with this

https://msdn.microsoft.com/en-us/library/bb510489%28SQL.100%29.aspx

TVP being Table Valued Parameters.

So based on the information in the link:

CREATE TYPE DisposeIdsTable AS TABLE (
    DisposeId VARCHAR(50));
GO

and then

USE [Test]
GO

/****** Object:  StoredProcedure [dbo].[up_Get_Customers]    Script Date: 09/23/2015 19:10:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[up_Get_Customers]
(
    @DisposeIds DisposeIdsTable READONLY
)
AS
BEGIN

    SELECT C1.DisposeDate,C1.DisposeID,C1.ReceiveDate,C1.ReceiveID
    FROM Customers C1 
    LEFT JOIN Customers C2 ON C1.DisposeID=C1.ReceiveID 
    WHERE (SELECT COUNT(*) FROM @DisposeIds) = 0
        OR
        (C1.DisposeID IN (
            SELECT DisposeID FROM @DisposeIds
            UNION ALL
            SELECT ',')))
   END

GO

N.B. The code posted above is not tested at all, so may contain mistakes. Also I've made an assumption about the datatype of DisposeId, so that's unlikely to be correct and would need amending to suit your datatype. Also, I've attempted to preserve the existing logic of the query, whatever that may be.

When executing the stored procedure you will instead need to declare a variable of type DisposeIdsTable rather than the text datatype that you're currently using. That table variable will then need to be populated with your IDs.

Upvotes: 1

Related Questions