Maintaining Variable After EXEC

Hello,

I am fairly new at stored procedures. I have created some that will
go through a table and return a start date and an end date that is
dependent upon the fiscal period you want, but I then need to use
those dates in another stored procedure to retrieve the information I
need. My stored procedure looks like this.

================================================== ====================

CREATE PROCEDURE dbo.R920ExtTotal
@.MthsBack Decimal OUTPUT
AS

DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
as SMALLDATETIME

Exec @.StartDate = GetMthStart @.MthsBack

Exec @.EndDate = GetMthEnd @.MthsBack

SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
([date] BETWEEN "' +
CONVERT(nvarchar, @.StartDate) +
'" and "' +
CONVERT(nvarchar, @.EndDate) +
'")'

Select @.sSQL

EXEC (@.sSQL)

Return
GO

================================================== ===============

The problem is my variables @.StartDate and @.EndDate do not retain
their values after the EXEC statement and revert to 01/01/1900. How
can I get around this problem?

Thanks!!!!

ChipVariables are only available in the scope within which they are declared. If
you need to move values out of a stored procedure, you must use output
parameters for all of them. Also, why are you building dynamic SQL in your
procedure? You can use the variables directly in SQL queries - there is no
need to convert them to strings, and insert them into a SQL string.

On 6 Jan 2004 11:05:01 -0800, cmayan@.lesliecontrols.com (Chip Mayan) wrote:

>Hello,
>I am fairly new at stored procedures. I have created some that will
>go through a table and return a start date and an end date that is
>dependent upon the fiscal period you want, but I then need to use
>those dates in another stored procedure to retrieve the information I
>need. My stored procedure looks like this.
>================================================== ====================
>CREATE PROCEDURE dbo.R920ExtTotal
>@.MthsBack Decimal OUTPUT
>AS
>DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
>as SMALLDATETIME
>Exec @.StartDate = GetMthStart @.MthsBack
>Exec @.EndDate = GetMthEnd @.MthsBack
>SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
>([date] BETWEEN "' +
>CONVERT(nvarchar, @.StartDate) +
>'" and "' +
>CONVERT(nvarchar, @.EndDate) +
>'")'
>Select @.sSQL
>EXEC (@.sSQL)
>Return
>GO
>================================================== ===============
>The problem is my variables @.StartDate and @.EndDate do not retain
>their values after the EXEC statement and revert to 01/01/1900. How
>can I get around this problem?
>Thanks!!!!
>Chip|||Chip Mayan (cmayan@.lesliecontrols.com) writes:
> I am fairly new at stored procedures. I have created some that will
> go through a table and return a start date and an end date that is
> dependent upon the fiscal period you want, but I then need to use
> those dates in another stored procedure to retrieve the information I
> need. My stored procedure looks like this.
>================================================== ====================
> CREATE PROCEDURE dbo.R920ExtTotal
> @.MthsBack Decimal OUTPUT
> AS
> DECLARE @.sSQL AS NVARCHAR(255), @.StartDate as SMALLDATETIME, @.EndDate
> as SMALLDATETIME
> Exec @.StartDate = GetMthStart @.MthsBack
> Exec @.EndDate = GetMthEnd @.MthsBack
> SET @.sSQL = 'Select count(extension) as Total From r920f00 Where
> ([date] BETWEEN "' +
> CONVERT(nvarchar, @.StartDate) +
> '" and "' +
> CONVERT(nvarchar, @.EndDate) +
> '")'
> Select @.sSQL
> EXEC (@.sSQL)
> Return
> GO

I'm afraid that there are a couple of errors or strange things in this
procedure.

First: there is absolutely no reason to use dynamic SQL here. Just write:

SELKCT count(extension) AS Total
FROM r920f00
WHERE [date] BETWEEN @.StartDate AND @.EndDate

Second: the calls to set @.StartDate and @.EndDate looks funny. If
GetMthStart and GetMthEnd are user-defined functions it would be alright,
but you indicated that they were stored procedures. The return value from
a stored procedure is always an integer value, so you cannot return a
date here. And I would strongly recommend you to use return values solely
for indication of success/failure (with 0 meaning success, and about
everything else meaning failure.) So you would have to make the output
parameters:

EXEC GetMthStart @.MthsBack, @.StartDate OUTPUT

Third: the @.MthsBack parameter is declared as output, but you never assign
it any value, you only seem to use it for input.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp