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/techinf...2000/books.asp