CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Passing parameters for stored procedure

I have this stored procedure:

CREATE PROCEDURE [Test]
    (@ID INT,
     @month VARCHAR(10),
     @Low INT,
     @standard FLOAT = 0)
AS
    IF EXISTS (SELECT 1 FROM [Table1] 
               WHERE ID = @ID AND month = @month 
                 AND TYPE = 'S' AND TYPEID = @Low)
    BEGIN
        UPDATE [Table1] 
        SET Add = @standard
        WHERE ID = @ID AND month = @month 
          AND TYPE = 'S' AND TYPEID = @Low
    END
    ELSE
    BEGIN
        INSERT INTO [Table1] (ID, month, Add, TYPE, TYPEID) 
        VALUES (@ID, @month, @standard, 'S', @Low)
    END
GO

This is the table which is missing March dates

ID     Month    Add    Type    TypeID
---------------------------------------
333    feb      4       S        111
333    April    4       S        111

So I thought by refreshing the stored procedure this would fill in for the month of March.

But when I execute the procedure using 

Use [DB1]
GO

DECLARE @RV int
DECLARE @ID int
DECLARE @month varchar(10)
DECLARE @Low int
DECLARE @Add int
DECLARE @standard float

EXECUTE @RV = [Test] 
              @ID int,
              @month varchar(10),
              @Low int,
              @Add int,
              @standard float
GO

I get

  ID        Month      Add        Type     TypeID
  -----------------------------------------------
  333       feb         4           S        111
  333       April       4           S        111
  NULL      NULL        NULL        S        NULL

Although I am looking for 

  ID        Month      Add        Type      TypeID
  ------------------------------------------------
  333       feb         4           S        111
  333       April       4           S        111
  333       Mar         4           S        111

I am assuming I am not passing the parameters correctly or something. Can anyone advise on this? Thanks

Upvotes: 0

Views: 92

Answers (1)

squillman
squillman

Reputation: 13641

You're declaring your parameters to pass into the stored procedure but you're not assigning them a value anywhere, so the procedure has nothing to work with.

You need to change the execution statement to something like this:

Use [DB1]
GO
DECLARE @RV int = 0;
DECLARE @ID int = 333;
DECLARE @month varchar(10) = 'Mar';
DECLARE @Low int = 111;
DECLARE @Add int = 4;
DECLARE @standard float = 4;

EXECUTE @RV = [Test] 
@ID = @ID,
@month = @month,
@Low = @Low,
@Add = @Add,
@standard = @standard;
GO

Replace the 0's and 1.0 with whatever values want to use.

Also, month is a string column in your table but you're passing in an INT to the procedure. You need to change the @month parameter to match the datatype of your table column.
(removed after your edit)

Upvotes: 3

Related Questions