none
Stored Procedure

    Question

  • Hello team,

    I created this store procedure:

    Drop Proc Sales.GetBonus

    USE AdventureWorks2008

    Create Proc Sales.GetBonus

    @BusinessEntityID As INT,

    @SalesYTD AS Money,

    @SalesLastYear AS MONEY,

    @Difference As INT OutPut

    As

    SELECT ss.BusinessEntityID ,ss.SalesYTD, ss.SalesLastYear, (ss.SalesYTD - ss.SalesLastYear) As Difference

    FROM Sales.SalesPerson ss

    WHERE ss.BusinessEntityID = @BusinessEntityID

    AND     ss.SalesYTD = @SalesYTD

    AND     ss.SalesLastYear= @SalesLastYear

    AND   ss.SalesYTD - ss.SalesLastYear = @difference

    up to here it went well,

    I came to execute it:

    This is the code:

    Declare @MYDifference As MONEY;

    EXEC Sales.GetBonus

    @BusinessEntityID = 275,

    @SalesYTD = 4557045.0459,

    @SalesLastYear = 1750406.4785,

    @difference = @MYDifference;

    when I run the top code, it only shows the column headers, is this how it is supposed to be?

    Then I ran this code and I got Null, in the result screen.

    The screen is split into half:

    The first half shows the column headers and the second half shows a column header called difference and the value is Null,

    it has to bring back results, why doesn't give me any result?

    what is your idea? what is wrong? what changes should be made on the query to get results and value?

    Best,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, October 29, 2013 6:22 PM

Answers

  • I am not sure why you are passing @SalesYTD AS Money,@SalesLastYear AS MONEY parameters to the stored procedure .Do you need to make sure the values are same for the given BusinessEntityID.I removed them and gave some sample code.Try this.I added some comments to the code.

    Drop Proc Sales.GetBonus
    GO
    USE AdventureWorks2008
    GO
    Create Proc Sales.GetBonus
    @BusinessEntityID As INT,
    @Difference As INT OutPut
    As
    Begin
    /**1.Here you are selcting the Row from Sales.SalesPerson table where 
         the BusinessEntityID is equal to input parameter @BusinessEntityID
       2.Note that you are not assigning any value to the output parameter here .
       3.This one will print in the tabular format in the output
     **/
    SELECT ss.BusinessEntityID 
    		,ss.SalesYTD
    		, ss.SalesLastYear
    		, (ss.SalesYTD - ss.SalesLastYear) As Difference
    FROM Sales.SalesPerson ss
    WHERE ss.BusinessEntityID = @BusinessEntityID
    /** 1.In this statement you are assigning the difference to the @Difference output parameter.
    	2.This one will just assigns the value to the parameter.we have to capature the 
    	value when we call the procedure
     **/
    SELECT @Difference=(ss.SalesYTD - ss.SalesLastYear) 
    FROM Sales.SalesPerson ss
    WHERE ss.BusinessEntityID = @BusinessEntityID
    End
    
    GO
    Declare @MYDifference As MONEY;
    EXEC Sales.GetBonus
    @BusinessEntityID = 275,
    @difference = @MYDifference OUT;--This is how we capture the value
    SELECT @MYDifference--Print the captured value
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    • Marked as answer by GGGGGNNNNN Wednesday, October 30, 2013 8:12 PM
    Tuesday, October 29, 2013 10:01 PM

All replies

  • Hello,

    The last line of your select uses @difference but you're passing in a null value as you've declare @MYDifference but never give it a value. Since anything = null will be null, there is flawed logic there. You're also calculating the different but then checking it against the input difference, is there a point to that?

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, October 29, 2013 6:33 PM
  • you've not specifed it as OUT param. try this

    Declare @MYDifference As MONEY;
    EXEC Sales.GetBonus
    @BusinessEntityID = 275,
    @SalesYTD = 4557045.0459,
    @SalesLastYear = 1750406.4785,
    @difference = @MYDifference OUT;
    
    SELECT @MYDifference 

    Please Mark This As Answer if it solves your problem

    Tuesday, October 29, 2013 6:37 PM
  • I am trying to learn,

    The value of @difference comes from salesLastYear-SalesYTD

    then this variable has some value into it.

    I declare the variable @MyDifference which takes the value from @difference.

    This is what I think, as you say, there is flawed logic.

    I calculate the difference and then store the result @difference and then set @difference to @Mydifference by @difference=@Mydifference

    any tips? or any ideas? I need the process what/ how this store procedure is getting done.

    Thanks,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, October 29, 2013 9:35 PM
  • I added OUT as above to my code, but it is still not working.

    Thanks,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, October 29, 2013 9:40 PM
  • Hello,

    The value is not stored in @difference, it's not stored anywhere in your select query. The select query has a column alias set to Difference but it is not storing that value in any variables. If you want to assign a variable a value in a select statement then you must use @VariableName = Something where @VariableName is your variable and Something is whatever it is you want.

    Since you variable is also an output parameter it doesn't make sense to both calculate the difference and output the difference in a variable, nor does it make sense (to me anyway) to ask for the difference if you already know the difference.

    -Sean


    Sean Gallardy | Blog | Twitter

    Tuesday, October 29, 2013 9:44 PM
  • Change the datatype of the variable @Difference to MONEY. Currently you have set it as INT.

    Tuesday, October 29, 2013 9:57 PM
  • I have just modified your SQL to work in my system and it is returning the results.

    Declare
    @BusinessEntityID As INT = 275,
    @SalesYTD AS Money = 4557045.0459,
    @SalesLastYear AS MONEY = 1750406.4785,
    @Difference As MONEY = 2806638.5674

    SELECT ss.BusinessEntityID , ss.SalesYTD, ss.SalesLastYear, (ss.SalesYTD - ss.SalesLastYear)
    FROM (SELECT 275 AS BusinessEntityID, 4557045.0459 AS SalesYTD, 1750406.4785 AS SalesLastYear) SS
    WHERE ss.BusinessEntityID = @BusinessEntityID
    AND     ss.SalesYTD = @SalesYTD
    AND     ss.SalesLastYear= @SalesLastYear
    AND   (ss.SalesYTD - ss.SalesLastYear) = @difference

    Also, the last comparison you are performing is between a VALUE (ss.SalesYTD - ss.SalesLastYear) to NULL (@difference) that is also a reason why you are not getting results. Set the value correctly to get the output.

    Tuesday, October 29, 2013 10:01 PM
  • I am not sure why you are passing @SalesYTD AS Money,@SalesLastYear AS MONEY parameters to the stored procedure .Do you need to make sure the values are same for the given BusinessEntityID.I removed them and gave some sample code.Try this.I added some comments to the code.

    Drop Proc Sales.GetBonus
    GO
    USE AdventureWorks2008
    GO
    Create Proc Sales.GetBonus
    @BusinessEntityID As INT,
    @Difference As INT OutPut
    As
    Begin
    /**1.Here you are selcting the Row from Sales.SalesPerson table where 
         the BusinessEntityID is equal to input parameter @BusinessEntityID
       2.Note that you are not assigning any value to the output parameter here .
       3.This one will print in the tabular format in the output
     **/
    SELECT ss.BusinessEntityID 
    		,ss.SalesYTD
    		, ss.SalesLastYear
    		, (ss.SalesYTD - ss.SalesLastYear) As Difference
    FROM Sales.SalesPerson ss
    WHERE ss.BusinessEntityID = @BusinessEntityID
    /** 1.In this statement you are assigning the difference to the @Difference output parameter.
    	2.This one will just assigns the value to the parameter.we have to capature the 
    	value when we call the procedure
     **/
    SELECT @Difference=(ss.SalesYTD - ss.SalesLastYear) 
    FROM Sales.SalesPerson ss
    WHERE ss.BusinessEntityID = @BusinessEntityID
    End
    
    GO
    Declare @MYDifference As MONEY;
    EXEC Sales.GetBonus
    @BusinessEntityID = 275,
    @difference = @MYDifference OUT;--This is how we capture the value
    SELECT @MYDifference--Print the captured value
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    • Marked as answer by GGGGGNNNNN Wednesday, October 30, 2013 8:12 PM
    Tuesday, October 29, 2013 10:01 PM
  • Thank you very much, it is very thorough answer, but should I follow this structure everywhere? Should I have Begin and End in all my Stored Procedures?

    Thanks,

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, October 30, 2013 8:13 PM
  • I did not get the question on structure..

    If you are referring using the select first and assigning the value to parameter ...Then i just gave that to  explain.

    What you use in your procedures depends on the requirement ..

    Requirement states what is input to your procedure and what in expected output.If the requirement is to give the tabular result set back you might just have the first statement.

    For the Begin and End Question.It's just a programming style.I use them in coding my procedures.



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer




    • Edited by Vinay Valeti Thursday, October 31, 2013 12:29 AM
    Wednesday, October 30, 2013 8:56 PM