SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can someone explain how this works


Can someone explain how this works

Author
Message
macbaze72
macbaze72
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 29
This code works, but I don;t understand it. The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate? As it is written it return the results of dbo.startdate().


ALTER PROCEDURE GetStartDate(
@StartDate Datetime OUTPUT
)
AS
DECLARE
@Severity int

SET @Severity = 0

IF @StartDate IS NULL
SET @StartDate = dbo.startdate(0)

RETURN @Severity


DECALRE @StartDate DateTime
EXEC GetStartDate @StartDate OUTPUT
SELECT @StartDate


Thanks
Everett
Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7089 Visits: 1343
Post the DDL for dbo.startdate
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385753 Visits: 42542
macbaze72 - Thursday, December 21, 2017 4:04 PM
This code works, but I don;t understand it. The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate? As it is written it return the results of dbo.startdate().


ALTER PROCEDURE GetStartDate(
@StartDate Datetime OUTPUT
)
AS
DECLARE
@Severity int

SET @Severity = 0

IF @StartDate IS NULL
SET @StartDate = dbo.startdate(0)

RETURN @Severity


DECALRE @StartDate DateTime
EXEC GetStartDate @StartDate OUTPUT
SELECT @StartDate


Thanks
Everett

The value generated by the procedure dbo.startdate(0) is returned in the OUTPUT parameter @StartDate. The value in @Severity would be made available if GetStartDate is execute this way:
declare @StartDate DateTime, @result int;
EXEC @result = EXEC GetStartDate @StartDate OUTPUT;
SELECT @result, @StartDate;

Does that help?


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sue_H
Sue_H
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69573 Visits: 14481
macbaze72 - Thursday, December 21, 2017 4:04 PM
This code works, but I don;t understand it. The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate? As it is written it return the results of dbo.startdate().


ALTER PROCEDURE GetStartDate(
@StartDate Datetime OUTPUT
)
AS
DECLARE
@Severity int

SET @Severity = 0

IF @StartDate IS NULL
SET @StartDate = dbo.startdate(0)

RETURN @Severity


DECALRE @StartDate DateTime
EXEC GetStartDate @StartDate OUTPUT
SELECT @StartDate


Thanks
Everett


Because @StartDate is the output parameter. In the beginning of the stored procedure it's declared as an output parameter and when executing the stored procedure, you are capturing the output parameter. Output parameters are explained in the documentation for Create Procedure - so it's not like it's easy to find.
CREATE PROCEDURE (Transact-SQL)

If you haven't worked with them much, you can find other posts and examples from other sites if you search on: output parameter in stored procedure

Sue



ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82832 Visits: 9350
macbaze72 - Thursday, December 21, 2017 4:04 PM
This code works, but I don;t understand it. The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate? As it is written it return the results of dbo.startdate(). ...
Thanks
Everett

The RETURN value from any proc is always a single integer value. This is intended to be the status of the proc, i.e., 0 if successful, something else if not.

You don't need to, or want to, return OUTPUT parameters directly. For one thing, you could have many OUTPUT parameters. For another, SQL already "knows" it needs to return that value because of the "OUTPUT" designation.


SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859579 Visits: 47093
macbaze72 - Thursday, December 21, 2017 4:04 PM
This code works, but I don;t understand it. The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate? As it is written it return the results of dbo.startdate().


ALTER PROCEDURE GetStartDate(
@StartDate Datetime OUTPUT
)
AS
DECLARE
@Severity int

SET @Severity = 0

IF @StartDate IS NULL
SET @StartDate = dbo.startdate(0)

RETURN @Severity


DECALRE @StartDate DateTime
EXEC GetStartDate @StartDate OUTPUT
SELECT @StartDate


Thanks
Everett


RETURN can only return integers and the RETURN in this code is basically useless someone considers a NULL to be a good return. Considering the spelling error in the code, I suspect a lot is missing. Please post the real code and post the code for the function that is called.

I also suspect that this should be a function rather than a stored procedure.

--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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search