Problem with simple stored procedure

  • michael.leach2015

    Default port

    Points: 1486

    CREATE PROCEDURE sp_area

    @length int,
    @width int,
    @area int OUTPUT

    AS
    -- BEGIN is optional
    SELECT @area = @length * @width;
    -- END is optional

    DECLARE @totalArea int

    EXECUTE sp_area
    @length = 100, @width = 50,
    @area = @totalArea OUTPUT

    SELECT @totalArea as totalArea;

    For my stored procedure above, I have the following questions:

    1. I highlighted all of the code and pressed F5.  It executed successfully, but the SELECT statement didn’t display anything in the lower window pane.  Why not?
    2. The name of the output variable inside the stored procedure is @area.  After the execute statement, to handle the output value, I am declaring another variable called @totalArea to accept the outputted value, then I am assigning that to a variable (@area) which has the same name as the output variable defined in the stored procedure.  In terms of designating variable names to handle output variables, is this the best way to do it in terms of making the code easier to read, or is there a better way to choose names for these variables?
    3. Is it a good idea to precede the name of the stored procedure with “sp_” or should I drop that part?  Why or why not?
    4. The keywords BEGIN and END are optional.  Is it better to include them or exclude them?
    5. Please leave any other suggestions.

     

  • BrainDonor

    SSCoach

    Points: 19202

    As you have created the stored procedure 'sp_area', go back and modify it - I suspect you'll be a bit surprised at what you see.

    Then have a look at 'batch separators'  - usually 'GO' (but not always).

    Steve Hall
    Linkedin
    Blog Site

  • DesNorton

    SSC-Insane

    Points: 22713

    Also, do not prefix your stored procedure with sp_.

    This has a performance impact, as SQL assumes that your proc is in the master DB.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715889

    apart from the items above, begin and end are controversial. I like them, many do not. In any case, if you use them, you still want to ensure you have a GO after the end of your procedure definition.

    No sp_, as noted above. You can do spArea or uspArea, but really, I prefer more normal names, like CalculateArea.

    For parameters, you can use @area as the parameter you are calling. The call to the procedure doesn't remove @area from your namespace, as it's a parameter. I can do this:

    CREATE PROCEDURE sp_area

    @length int,
    @width int,
    @area int OUTPUT

    AS
    BEGIN -- is optional
    SELECT @area = @length * @width;
    END -- is optional
    GO

    DECLARE @Area int

    EXECUTE sp_area
    @length = 100, @width = 50,
    @area = @Area OUTPUT

    SELECT @Area as totalArea;

    Think about error handling if the procedure has an issue. Do you want the OUTPUT variable to return something here or remain as NULL?  You might think about a RETURN value or some check before you start using @totalarea later in calculations.

    OUTPUT variables aren't used a lot in the real world. More people return a result set and operate on that from a procedure.

  • Jeff Moden

    SSC Guru

    Points: 994680

    Micheal,

    Unless you're just trying something simple out to learn how to use stored procedures, this should definitely NOT be a stored procedure.  Which is it?  Just a simple example for learning or something a bit more serious?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • scdecade

    Old Hand

    Points: 399

    If you're looking for something simple a scalar function could work.  The calculation requires no rows or columns.  If you know the function is accessed by query(s) in a proc then the value could be directly assigned to a variable.  Something like this:

    drop function if exists dbo.calc_area;
    go
    create function dbo.calc_area(
    @length int,
    @width int)
    returns int
    as
    begin
    return (@length*@width);
    end
    go

    drop proc if exists proc_area;
    go
    create proc proc_area
    @length int,
    @width int
    as
    set nocount on;
    declare
    @area int=dbo.calc_area(@length, @width);

    select
    @area variable_area,
    dbo.calc_area(@length, @width) function_area;
    go

    exec proc_area 10, 12;

    This pattern works great for json passed into procedures.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden

    SSC Guru

    Points: 994680

    A simple Scalar Function will cause the code to take about 7 times longer than either inline code or an iTVF that acts like a scalar function.  Please see the following article for more information on that.

    https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • scdecade

    Old Hand

    Points: 399

    If you run it a million times.  In this case here the OP has no 'from' clause so it only executes once.  I actually did read your article a while back.  As a result I keep the number of times a udf runs per proc as low as possible, preferably once.  Mostly udf is for code readability.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden

    SSC Guru

    Points: 994680

    scdecade wrote:

    If you run it a million times.  In this case here the OP has no 'from' clause so it only executes once.  I actually did read your article a while back.  As a result I keep the number of times a udf runs per proc as low as possible, preferably once.  Mostly udf is for code readability.

    I absolutely agree that's how his code is written.  The trouble is that he's new to this game and he wrote a RBAR stored procedure simply because he may not know any better, at this point.  What I want to do is make sure that he knows better as a part of his "training".   The way I know he's new at this is because he's said so in previous posts and also has stated that he wants to learn the best ways possible.

    As for me, I can't wait for iSFs (automatic Inline Scalar Functions) in 2019 so, in the meantime, I'll continue to avoid Scalar and mTVFs like the plague and continue to write iTVFs.  It's also my nature to teach what I do and to never justify not doing something just because someone is doing something with a low row count. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • michael.leach2015

    Default port

    Points: 1486

    Steve Jones - SSC Editor wrote:

    In any case, if you use them, you still want to ensure you have a GO after the end of your procedure definition.

    For parameters, you can use @area as the parameter you are calling.  The call to the procedure doesn't remove @area from your namespace, as it's a parameter.

    Steve,

    Thank you for your reply.  I have some questions about some parts that you brought up.

    1. You said to use GO at the end of the definition of the procedure.  I presume the purpose of this is to signal to SSMS that this is the end of the procedure?  If so, why not use a " ; " at the end of the procedure?
    2. When you say name space, I think I know what you mean.  Does that mean the space in memory where the PC holds the variable (or maybe should I say the value of the variable)?
    3. "The call to the procedure doesn't remove @area from your namespace, as it's a parameter."  Can you explain this a little bit more?  Do you mean that (on the 2nd to last line) if I use @Area in place of @totalArea so that I have @area = @Area, that the first @area will not be removed?
    4. I also noticed that when you replaced @totalArea with @Area, you have a capital A instead of using the lowercase 'a' like I had.  Was this intentional, i.e. if the variable that captures the output variable (or output value whichever it is) has the same name as the output variable itself, do you have to change the casing of the variable name?
    5. When I use @totalArea, is that actually capturing the output variable from the procedure or is it capturing the value of the output variable returned by the stored procedure?

     

  • michael.leach2015

    Default port

    Points: 1486

    Jeff Moden wrote:

    Micheal,

    Unless you're just trying something simple out to learn how to use stored procedures, this should definitely NOT be a stored procedure.  Which is it?  Just a simple example for learning or something a bit more serious?

    Jeff,

    Good to hear from you.  I was hoping you would jump in to offer your insights.  This stored procedure is merely for testing or practicing purposes so I can learn how these work.  This is not something that I actually intend to use.

    I'm just curious, why do you say this should definitely not be used as a stored procedure?

    I'm glad I asked, because I thought it was pretty much ok.

  • michael.leach2015

    Default port

    Points: 1486

    Jeff Moden wrote:

    A simple Scalar Function will cause the code to take about 7 times longer than either inline code or an iTVF that acts like a scalar function.  Please see the following article for more information on that.

    https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

     

    I'm going to find out what I can about stored procedures first.  Then I will focus on functions.  When I am working on functions, I will read the article you recommended.  Thanks for posting the link.

  • michael.leach2015

    Default port

    Points: 1486

    scdecade wrote:

    If you run it a million times.  In this case here the OP has no 'from' clause so it only executes once.  I actually did read your article a while back.  As a result I keep the number of times a udf runs per proc as low as possible, preferably once.  Mostly udf is for code readability.

    To scdecade,

    I think I see what you are saying about how many times the function could execute and the fact that I don't have a FROM clause.  Are you saying that if I had a FROM clause that references a table with 100 rows, that the function would execute 100 times (one for each row)?

  • scdecade

    Old Hand

    Points: 399

    michael.leach2015 wrote:

    I think I see what you are saying about how many times the function could execute and the fact that I don't have a FROM clause.  Are you saying that if I had a FROM clause that references a table with 100 rows, that the function would execute 100 times (one for each row)?

    Generally speaking Jeff is more correct and scalar functions are not well suited for data access use.  His article is definitive on that.  I should've explained better the code I was posting.  Your case here is not typical.  When it comes to accessing data from tables there are always drastically better ways to do things than scalar functions.

    Where I use scalar functions is to clean up repetitive elements of code.  A few weeks ago someone posted a procedure with about 1/2 the lines of code were the 'procedure help instructions'.  I might move that to a scalar function and assign it on declaration in the procedure.  That way it's only 1 row instead of half the procedure.  It's purely aesthetics I guess.

    Would it execute 100 times?  If the function was in the select list I would guess the answer is yes.  It could be looked up in the documentation.  Or maybe just run it a few different ways and see what happens.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • michael.leach2015

    Default port

    Points: 1486

     

    I tried another simple stored procedure above, which I executed.  It didn't display the results window for the SELECT statement result at this time.

    Then I right clicked the procedure and did execute procedure and got this code (with the results window this time):

    USE [Amargosa Hotel]
    GO

    DECLARE @return_value int

    EXEC @return_value = [dbo].[getData]

    SELECT 'Return Value' = @return_value

    GO

    The procedure is returning an int value to @return_value.  But I don't understand the purpose of this SELECT statement and what it is doing.  Why is SQL taking a text value ('Return Value') and assigning the value of @return_value to the text string?  I guess I could understand if it were stated with the variable first, i.e. @return_value = 'Return Value' but then that wouldn't make sense since @return_value is supposed to be of type int instead of text.

     

     

     

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply