CODEWITHSUNDEEP
sql-serversql-server-2005t-sqltriggers
Marshal
Marshal

Reputation: 6661

SQL Trigger Execution problem

I have following trigger. On Insertion of a new row, the Stored Procedure is unable to get the parameter value of variable @ItemID. I am trying to pass the value of ItemID Column of newly inserted Row to the stored procedure CalculateCurrentStock

ALTER TRIGGER UpdateCurrentStock
   ON StockIn
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    EXEC CalculateCurrentStock (SELECT ItemID From INSERTED)
END

The error text reads

Procedure or function 'CalculateCurrentStock' expects parameter '@ItemID', which was not supplied. The statement has been terminated.

Thank you for help.

EDIT: Answer

I have altered the trigger as per Derek Kromm's and KM's suggestion

ALTER TRIGGER UpdateCurrentStock
   ON StockIn
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @CSV varchar(max)
    SELECT @CSV=ISNULL(@CSV+',','')+ItemID From INSERTED
    EXEC CalculateCurrentStock @CSV


END

Thanks for help :)

Upvotes: 2

Views: 279

Answers (2)

KM.
KM.

Reputation: 103607

I'd personally poke out my eye before using a cursor, especially when you can pass in a set of values (table values parameter, or CSV) all at one time in a single procedure call.

if you are running SQL Server 2008 you can create your procedure using a Table-Valued Parameters and should be able to pass in the INSERTED table.

If your are using SQL Server 2005 you can create a comma separated value (csv) list of values within a varchar(max) variable and pass that in to your procedure.

EDIT, here is how to pass in a CSV

ALTER TRIGGER UpdateCurrentStock
   ON StockIn
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @CSV varchar(max)
    SELECT @CSV=ISNULL(@CSV+',','')+ItemID From INSERTED
    EXEC CalculateCurrentStock @CSV
END

now, within you'll need to split apart the @CSV values, so look here: Pass a list-structure as an argument to a stored procedure

Upvotes: 2

Derek
Derek

Reputation: 23238

Edit: As Martin pointed out, you can pass a table-valued parameter if you're using SQL Server 2008. If you're using 2005 or prior, you can't do that. KM suggests using a comma-delimited value in this case, which I personally disagree with.

In reality, you should probably copy the stored procedure logic directly into the trigger and avoid the whole mess altogether.

Upvotes: 3

Related Questions