Problem with simple stored procedure

Question

Problem with simple stored procedure

michael.leach2015

SSCommitted

Points: 1597
More actions
September 4, 2019 at 4:29 am

#3677056
```
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.
- This topic was modified 5 years, 2 months ago by michael.leach2015.

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

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

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: 24381 More actions · Answer 2

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.

How to post data/code on a forum to get the best help.

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 3

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: 1004304 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

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

Steve Collins SSCrazy Points: 2152 More actions · Answer 5

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(
  @lengthint,
  @widthint)
returns int
as
begin
return (@length*@width);
end
go

drop proc if exists proc_area;
go
create proc proc_area
  @lengthint,
  @widthint
as
set nocount on;
declare
  @areaint=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: 1004304 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

Steve Collins SSCrazy Points: 2152 More actions · Answer 7

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: 1004304 More actions · Answer 8

Steve Collins 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.

Change is inevitable... Change for the better is not.

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

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 9

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.

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?
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)?
"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?
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?
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 SSCommitted Points: 1597 More actions · Answer 10

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 SSCommitted Points: 1597 More actions · Answer 11

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 SSCommitted Points: 1597 More actions · Answer 12

Steve Collins 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)?

Steve Collins SSCrazy Points: 2152 More actions · Answer 13

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 SSCommitted Points: 1597 More actions · Answer 14

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.