none
How to execute stored procedure?

    Question

  • I have a stroed procedure below. I wonder how to execute it. Thanks GGGGGNNNNN

    Create Proc Sales.GetBonus
    @BusinessEntityID As INT,
    @SalesYTD AS Money,
    @SalesLastYear AS MONEY,
    @difference As INT OutPut

    As

    SELECT  @difference = ss.SalesYTD - ss.SalesLastYear
    FROM Sales.SalesPerson ss
    WHERE ss.BusinessEntityID = @BusinessEntityID
    AND      ss.SalesYTD = @SalesYTD
    AND      ss.SalesLastYear= @SalesLastYear

     


    GGGGGNNNNN

    Thursday, October 24, 2013 5:54 PM

Answers

All replies

  • DECLARE @DiffOUT int
    
    EXEC Sales.GetBonus @BusinessEntityID = <your value>,
    @SalesYTD = <your value>,
    @SalesLastYear = <your value>,
    @DiffOUT OUT
    
    SELECT @DIffOUT

    This will display you return value from the procedure 

    Thursday, October 24, 2013 6:01 PM
  • I can't enter values one by one, imagine that I have more than 10000 rows and I need to find out the difference, what the code would be?

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, October 24, 2013 8:40 PM
  • Hi GGGGGNNNNN,

    Based on the requirement, we need to create a function rather than a stored procedure. For example:

    CREATE FUNCTION dbo.ISOweek 
    (
     @BusinessEntityID As INT,
     @SalesYTD AS Money,
     @SalesLastYear AS MONEY
    )
    RETURNS @DifferenceTB TABLE
    (
        [difference] INT
    )
    WITH EXECUTE AS CALLER
    AS
    BEGIN
        DECLARE @difference int;
        SELECT  @difference  = ss.SalesYTD - ss.SalesLastYear
     FROM Sales.SalesPerson ss
     WHERE ss.BusinessEntityID  = @BusinessEntityID
     AND      ss.SalesYTD = @SalesYTD
     AND      ss.SalesLastYear= @SalesLastYear
     
     insert into @DifferenceTB values(@difference)
     return
    END;
    GO

    Then we can use Apply to get the final data.

    select ISOWeek.[difference]
    from SchemaName.TBName as TB
    cross apply dbo.ISOweek(TB.BusinessEntityID,TB.SalesYTD,TB.SalesLastYear) as ISOWeek

    For more detail information, you can refer to the following link:

    Using APPLY
    http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

    Best Regards,
    Allen Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Wednesday, October 30, 2013 8:35 AM
    Moderator
  • You answer also returns results, but I am looking for a simpler way, your solution has mixed a function and store procedure and since I am a rookie, I am looking for simpler solutions to understand.

    Thanks

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, October 30, 2013 8:17 PM
  • You have asked this similar question multiple times.  You are going about this entirely the wrong way.  SQL Server is a "set based" application.  You do not loop through all the rows to get and answer, which is what it sounds like you are trying to do.

    Please provide exactly what you are trying to accomplish and your table structures and sample data, and someone can help you with an entire solution.


    • Edited by Tom Phillips Wednesday, October 30, 2013 8:22 PM
    Wednesday, October 30, 2013 8:22 PM
  • I alreay got my answer. Please remember I am a rookie, I have done two courses in this topic. which question is not right, how to execute stored procedure? We can't loop through all the rows? Please let me know.

    What is set based application? what does it mean?

    Thanks,

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, October 30, 2013 10:10 PM
  • What is set based application? what does it mean?

    That is something you will learn in an appropriate educational setting.  Contrary to popular belief, you cannot really "learn" relational dbms programming (and learn to do it well) by just jumping into coding.  If you are a rookie, then you should be coding and learning as a part of a team - and the team should be mentoring you and guiding you as you learn. 

    Thursday, October 31, 2013 1:14 PM
  • Despite what you think that people can learn these topics in an appropriate educational setting, I think we can learn these topic by self studying and participating in forums on top of being in educational settings. As I said I have been into two course and i am planning to take one more.

    What you say is ideal to code as a part of team, but this condition doesn't exist for every body to be in a team and learn, and learn from team.

    I have learned many things from forums, not only this forum, but also from other forums,

    you can always stop answering to team members that you think they shouldn't be in this forum.....

    Thanks,

    GGGGGNNNNN


    GGGGGNNNNN

    Friday, November 01, 2013 9:10 PM