Using RETURN [Programming SQL]

Prev: CTE and DDL
Next: Using single character wildcards in stored procedure and allow

From: Scott on 22 Jul 2008 15:33

I'm trying to RETURN an integer variable called @iReturnCode after comparing
2 columns from 2 different tables after comparing the columns. I'm not real
familiar with using RETURN for this type of purpose. If the 2 columns aren't
equal, I'd like to RETURN a 1, if they are equal, just RETURN 0.

Can some shed some light? I commented the 2 places below where I'm trying to
RETURN the @iReturnCode variable.

-- CODE: ****************************************

BEGIN

declare @dtStartDate datetime, @dtEndDate datetime
set @dtStartDate = '20080717'
set @dtEndDate = '20080717'

declare @TimeRun1 int, @TimeRun2 int, @iReturnCode int

IF @dtStartDate > @dtEndDate
SELECT 50002 As iRetCode,
'Start Date must be before
than End Date' As sErrMsg
ELSE
BEGIN

SET @TimeRun1 = (SELECT SUM(time_run) FROM myTable1)
SET @TimeRun2 = (SELECT SUM(time_run) FROM myTable2)

If @TimeRun1 <> @TimeRun2
-- RETURN @iReturnCode = 1
ELSE
-- RETURN @iReturnCode = 0

PRINT @TimeRun1

END

END

From: Aaron Bertrand [SQL Server MVP] on 22 Jul 2008 15:46

Seems more appropriate to use an OUTPUT parameter for this purpose.
However, if you want to continue using RETURN, simply change your syntax:

IF @TimeRun1 <> @TimeRun2
RETURN 1;
ELSE
RETURN 0;

There is no point using a returncode variable here, and note that anything
after RETURN (e.g. your print statement) is not processed.

On 7/22/08 3:33 PM, in article #vX4yID7IHA.4108(a)TK2MSFTNGP04.phx.gbl,
"Scott" <sbailey(a)mileslumber.com> wrote:

> I'm trying to RETURN an integer variable called @iReturnCode after comparing
> 2 columns from 2 different tables after comparing the columns. I'm not real
> familiar with using RETURN for this type of purpose. If the 2 columns aren't
> equal, I'd like to RETURN a 1, if they are equal, just RETURN 0.
>
> Can some shed some light? I commented the 2 places below where I'm trying to
> RETURN the @iReturnCode variable.
>
> -- CODE: ****************************************
>
> BEGIN
>
> declare @dtStartDate datetime, @dtEndDate datetime
> set @dtStartDate = '20080717'
> set @dtEndDate = '20080717'
>
> declare @TimeRun1 int, @TimeRun2 int, @iReturnCode int
>
> IF @dtStartDate > @dtEndDate
> SELECT 50002 As iRetCode,
> 'Start Date must be before
> than End Date' As sErrMsg
> ELSE
> BEGIN
>
> SET @TimeRun1 = (SELECT SUM(time_run) FROM myTable1)
> SET @TimeRun2 = (SELECT SUM(time_run) FROM myTable2)
>
> If @TimeRun1 <> @TimeRun2
> -- RETURN @iReturnCode = 1
> ELSE
> -- RETURN @iReturnCode = 0
>
> PRINT @TimeRun1
>
> END
>
> END
>

From: Alex Kuznetsov on 22 Jul 2008 15:47

On Jul 22, 2:33 pm, "Scott" <sbai...(a)mileslumber.com> wrote:
> I'm trying to RETURN an integer variable called @iReturnCode after comparing
> 2 columns from 2 different tables after comparing the columns. I'm not real
> familiar with using RETURN for this type of purpose. If the 2 columns aren't
> equal, I'd like to RETURN a 1, if they are equal, just RETURN 0.
>
> Can some shed some light? I commented the 2 places below where I'm trying to
> RETURN the @iReturnCode variable.
>
> -- CODE: ****************************************
>
> BEGIN
>
> declare @dtStartDate datetime, @dtEndDate datetime
> set @dtStartDate = '20080717'
> set @dtEndDate = '20080717'
>
> declare @TimeRun1 int, @TimeRun2 int, @iReturnCode int
>
> IF @dtStartDate > @dtEndDate
> SELECT 50002 As iRetCode,
> 'Start Date must be before
> than End Date' As sErrMsg
> ELSE
> BEGIN
>
> SET @TimeRun1 = (SELECT SUM(time_run) FROM myTable1)
> SET @TimeRun2 = (SELECT SUM(time_run) FROM myTable2)
>
> If @TimeRun1 <> @TimeRun2
> -- RETURN @iReturnCode = 1
> ELSE
> -- RETURN @iReturnCode = 0
>
> PRINT @TimeRun1
>
> END
>
> END

It is not clear what is your problem. Are you getting an error message?

From: Scott on 22 Jul 2008 20:25

no errors, i'm just trying to compare two values and return a 1 or 0
depending on the comparison. Aaron gave me what I was asking for.

Thanks for your interest.

"Alex Kuznetsov" <alkuzo(a)gmail.com> wrote in message
news:07ad37f7-710e-459b-8827-cac51600b8f2(a)k37g2000hsf.googlegroups.com...
On Jul 22, 2:33 pm, "Scott" <sbai...(a)mileslumber.com> wrote:
> I'm trying to RETURN an integer variable called @iReturnCode after
> comparing
> 2 columns from 2 different tables after comparing the columns. I'm not
> real
> familiar with using RETURN for this type of purpose. If the 2 columns
> aren't
> equal, I'd like to RETURN a 1, if they are equal, just RETURN 0.
>
> Can some shed some light? I commented the 2 places below where I'm trying
> to
> RETURN the @iReturnCode variable.
>
> -- CODE: ****************************************
>
> BEGIN
>
> declare @dtStartDate datetime, @dtEndDate datetime
> set @dtStartDate = '20080717'
> set @dtEndDate = '20080717'
>
> declare @TimeRun1 int, @TimeRun2 int, @iReturnCode int
>
> IF @dtStartDate > @dtEndDate
> SELECT 50002 As iRetCode,
> 'Start Date must be before
> than End Date' As sErrMsg
> ELSE
> BEGIN
>
> SET @TimeRun1 = (SELECT SUM(time_run) FROM myTable1)
> SET @TimeRun2 = (SELECT SUM(time_run) FROM myTable2)
>
> If @TimeRun1 <> @TimeRun2
> -- RETURN @iReturnCode = 1
> ELSE
> -- RETURN @iReturnCode = 0
>
> PRINT @TimeRun1
>
> END
>
> END

It is not clear what is your problem. Are you getting an error message?

From: Scott on 22 Jul 2008 20:58

I keep getting the error listed below. All my sql statements are returning
correct values, it's the RETURN statement. I can't even RETURN one of the
variables.

Any ideas?

-- CODE: ****************************

BEGIN

declare @dtStartDate datetime, @dtEndDate datetime
set @dtStartDate = '20080717'
set @dtEndDate = '20080717'

declare @accTimeRun int, @sqlTimeRun int

IF @dtStartDate > @dtEndDate

SELECT 50002 As iRetCode,
'Start Date must be before
than End Date' As sErrMsg
ELSE

SET @accTimeRun = (SELECT SUM(p.time_run) FROM myTable1)

SET @sqlTimeRun = (SELECT SUM(p.time_run) FROM myTable2)

If @accTimeRun > @sqlTimeRun
RETURN 1;
ELSE
RETURN 0;

END

-- Error: ****************************
A RETURN statement with a return value cannot be used in this context.

"Aaron Bertrand [SQL Server MVP]" <ten.xoc(a)dnartreb.noraa> wrote in message
news:C4ABB172.D1F0%ten.xoc(a)dnartreb.noraa...
> Seems more appropriate to use an OUTPUT parameter for this purpose.
> However, if you want to continue using RETURN, simply change your syntax:
>
> IF @TimeRun1 <> @TimeRun2
> RETURN 1;
> ELSE
> RETURN 0;
>
> There is no point using a returncode variable here, and note that anything
> after RETURN (e.g. your print statement) is not processed.
>
>
> On 7/22/08 3:33 PM, in article #vX4yID7IHA.4108(a)TK2MSFTNGP04.phx.gbl,
> "Scott" <sbailey(a)mileslumber.com> wrote:
>
>> I'm trying to RETURN an integer variable called @iReturnCode after
>> comparing
>> 2 columns from 2 different tables after comparing the columns. I'm not
>> real
>> familiar with using RETURN for this type of purpose. If the 2 columns
>> aren't
>> equal, I'd like to RETURN a 1, if they are equal, just RETURN 0.
>>
>> Can some shed some light? I commented the 2 places below where I'm trying
>> to
>> RETURN the @iReturnCode variable.
>>
>> -- CODE: ****************************************
>>
>> BEGIN
>>
>> declare @dtStartDate datetime, @dtEndDate datetime
>> set @dtStartDate = '20080717'
>> set @dtEndDate = '20080717'
>>
>> declare @TimeRun1 int, @TimeRun2 int, @iReturnCode int
>>
>> IF @dtStartDate > @dtEndDate
>> SELECT 50002 As iRetCode,
>> 'Start Date must be before
>> than End Date' As sErrMsg
>> ELSE
>> BEGIN
>>
>> SET @TimeRun1 = (SELECT SUM(time_run) FROM myTable1)
>> SET @TimeRun2 = (SELECT SUM(time_run) FROM myTable2)
>>
>> If @TimeRun1 <> @TimeRun2
>> -- RETURN @iReturnCode = 1
>> ELSE
>> -- RETURN @iReturnCode = 0
>>
>> PRINT @TimeRun1
>>
>> END
>>
>> END
>>
>

| Next | Last
Pages: 1 2
Prev: CTE and DDL
Next: Using single character wildcards in stored procedure and allow