CODEWITHSUNDEEP
sqlsql-server
Jamie McManus
Jamie McManus

Reputation: 43

SQL Sever stored procedure keeps returning value of 1

I've got the following code below, which I know should return a certain value, but regardless of the values passed in it always returns 1

What's going on?

CREATE PROC spConvert               
    @date date,
    @convertFrom varchar(20),
    @convertTo varchar(20),
    @value money output  
AS 
   SELECT @value = value 
   FROM Conversion 
   JOIN Currency AS c1 ON ToID = c1.currencyID 
   JOIN Currency AS c2 ON FROMID = c2.currencyID 
   WHERE c1.name = @convertTo 
     AND c2.name = @convertFrom 
     AND @date BETWEEN StartDate AND EndDate                      

   IF @value IS NULL
     (SELECT @value = Value 
      FROM Conversion 
      JOIN Currency AS c1 ON ToID = c1.currencyID 
      JOIN Currency AS c2 ON FromID = c2.currencyID 
      WHERE c1.name = @convertTo 
        AND c2.name = @convertFrom 
        AND Enddate IS NULL)

   PRINT @value

   RETURN @value

Then I execute 

  declare @print money

  exec @print= spConvert @date ='2017-05-25', 
                         @convertFrom='euro', @convertTo='dollar',
                         @value = @print output 
  print @print

And I get 

1.44    ----(which is correct)
1.0     ----(not correct)

I don't get it?

Upvotes: 0

Views: 376

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269813

In SQL Server, stored procedures return a success value which is an integer. I'm not sure what the stored procedure does when given a money type.

However, you should be executing it as:

declare @print money;

exec spConvert @date='2017-05-25', @convertFrom='euro', @convertTo='dollar', @value=@print output ;

print @print;

You should also adjust the stored procedure so it doesn't return anything or returns a status value.

EDIT:

I should also note that your stored procedure only consists of one statement. Use BEGIN and END:

CREATE PROC spConvert (               
    @date date,
    @convertFrom varchar(20),
    @convertTo varchar(20),
    @value money output
) AS 
BEGIN
    SELECT @value = value 
    FROM Conversion c JOIN
         Currency c1
         ON c.ToID = c1.currencyID JOIN
         Currency c2 
         ON c.FROMID = c2.currencyID 
     WHERE c1.name = @convertTo AND c2.name  =@convertFrom AND
           @date BETWEEN StartDate AND EndDate                    

    IF @value is null  -- This seems dangerous, because @value is not initialized in the stored procedure
    BEGIN  
        SELECT @value = Value 
        FROM Conversion c JOIN
             Currency c1
             ON c.ToID = c1.currencyID JOIN
             Currency c2
             ON c.FromID = c2.currencyID 
         WHERE c1.name = @convertTo AND c2.name = @convertFrom AND
               Enddate is null           
         )
    print @value;  -- I don't know what this is for
END;

Upvotes: 1

Related Questions