Variables 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