Search code examples
sqlsql-servert-sqltsqlt

how to pass more values for a single parameter in storedprocedure in sql server

MY text:- 

CREATE proc usp_delete    
        @tranid int           
        as 
        begin 
    delete from customer where tranid in(@tranid)
     end

Note:- I want to delete records more than 1 records through this stored procedure like:- if I pass 1,2,3,4,5,6 that time all 6 records should be deleted

Solution

  • You can use table-valued parameter for that you need to declare that 

    CREATE TYPE EntityId AS TABLE  
( Id INT )
 GO

CREATE PROCEDURE usp_delete    
    @tranid EntityId READONLY
AS 
 BEGIN 
     DELETE c
     FROM customer c
     JOIN @tranid t ON t.Id=c.tranid 
 END

    For Executing with TVP declare a varible of type and pass it to the stored procedure 

     DECLARE @entityId EntityId 

 INSERT INTO @entityId 
 VALUES(1),(2),(3),(4),(5)

 EXEC usp_delete @entityId