Dynamic SQL with Output parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL with Output parameter

Author

Topic

rite237
Starting Member

1 Post

Posted - 2006-05-15 : 21:20:41

Hi, I am trying to use an output parameter with dynamic sql. But I am always getting an error. Below is my stored procedure:

CREATE PROCEDURE [Get_Users_By_Page]
@SortField varchar(100),
@CurrentPage int,
@PageSize int,
@QueryFilter varchar(100) = NULL,
@TotalRecords int OUTPUT
AS

--Turn off count return.
Set NoCount On

--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
Emp_Num numeric(9)
)

--Set maxRow to the last required row in the result set
DECLARE @maxRow int
SET @maxRow = @CurrentPage*@PageSize

--Fill the table variable with the primary key data
IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN
EXEC(
'INSERT INTO #TempTable (Emp_Num)
SELECT TOP ' + @maxRow + ' Emp_Num
FROM
Resource
ORDER BY ' + @SortField)

--Return the total number of records available as an output parameter
EXEC('SELECT @TotalRecords = COUNT(Emp_Num) FROM Resource')
END
ELSE
BEGIN
EXEC(
'INSERT INTO #TempTable (Emp_Num)
SELECT TOP ' + @maxRow + ' Emp_Num
FROM
Resource
WHERE ' + @QueryFilter + '
ORDER BY ' + @SortField)

--Return the total number of records available as an output parameter
EXEC('SELECT @TotalRecords = COUNT(Emp_Num) FROM Resource WHERE ' + @QueryFilter)
END

--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)

--Select one page of data based on the record numbers above
SELECT
r.*
FROM
#TempTable t
INNER JOIN Resource r ON
r.Emp_Num = t.Emp_Num
WHERE
t.ID > @FirstRec
AND
t.ID < @LastRec

GO

The problem is in statement EXEC('SELECT @TotalRecords = COUNT(Emp_Num) FROM Resource'). Please let me know what wrong I am doing here. I am getting following error if I try to exexute above SP:

Must declare the variable '@TotalRecords'.

Regards
ricky

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-16 : 01:40:33

instead of using Exec .. check out for Sp_ExecuteSql in Book online

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 07:13:48

http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources