paging and output parms - SQL Server Forums

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

SQL Server Development (2000)

paging and output parms

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-09 : 11:40:23

Hi I recently started using Paul Wilson's sproc for paging. It is very good but I have tried modifying it to ouput the TotalRecord count in an OUTPUT PARM or RETURN value. When I execute it I get the following error:

Server: Msg 245, Level 16, State 1, Procedure uspPaging2, Line 45
Syntax error converting the varchar value 'SELEC' to a column of data type int.

My code is below:

CREATE PROCEDURE uspPaging(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL,
@TotalRecords VARCHAR(5) OUTPUT
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
SELECT @TotalRecords = ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END
ELSE
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
SELECT @TotalRecords = ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END

RETURN @TotalRecords
GO

I know this is probably straight fwd but I have racked spent hours on this and am unable to get it going without the error. The exec code I have used is below also:

DECLARE @TotalCount Varchar(5)
EXEC uspPaging2 'business', 'business_id', 'rank', 10, 1, 'business_id between 1 AND 10',
@TotalRecords = @Total OUTPUT
Select TotalCount = @TotalCount

Any ideas where I am going wrong?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 12:12:19

use sp_executeSql for this:

declare @TotalRecordsint
exec sp_executeSql N''SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName, N'@TotalRecords int output', @TotalRecords output

select @TotalRecords

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-09 : 12:13:29

I think your problem is with this line:

SELECT @TotalRecords = ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

You're trying to assing a local variable to the result of an exec'd string. you can't do it like that. You could, however use sp_executesql with output parameters.

Here is an example:


declare @stmnt nvarchar(2000)
	,@parms nvarchar(500)
	,@retVal varchar(20)

--declare parameter as output
set @parms = N'@outVar varChar(20) output'

--assign value to output parameter in dynamic sql
set @stmnt = N'Select @outVar = ''TG'''

--exec sql, get return value from output variable
exec sp_executesql @stmnt, @parms, @outVar=@retVal output

Select @retVal

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-09 : 12:14:03

Dang you Spirit1 !!

edit: I've got to learn to hit refresh before I post :)

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 12:28:19

for this occasiong let me show you the

picture, TG

that's usually used when one finds himself in similar situation

Go with the flow & have fun! Else fight the flow

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-09 : 12:48:16

Thanks for the tips guys. It looks a bit complicated. I think I'll have to go study-up on this 'sp_executesql'. In the mean time I might try and find a workaround of some sort.

cheers

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 13:01:40

there's no workaround.
it's not complicated at all.
if you have a dynamic table name that's the only sane option... we can talk about insane ones if you wish...

Go with the flow & have fun! Else fight the flow

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-09 : 13:35:58

Spirit1, I am just a little confused. I am not sure how it works. Do I have to call my normal proc "uspPaging", and then call the "sp_executesql" as well? Do I also have to specify somewhere in the sp_executesql statement which proc it is reffering to? That is what I am not sure of? Would it be too much trouble if you could put the code you wrote above next to my proc, just so I can get a better idea of where it fits in?

Puno Hvala

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 14:01:39

put
exec sp_executeSql N''SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName, N'@TotalRecords int output', @TotalRecords output
instead of
SELECT @TotalRecords = ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

and that's it.

Go with the flow & have fun! Else fight the flow

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-09 : 14:18:47

Hi again Spirit1.

I tried as you suggested but am getting the following error:

Server: Msg 170, Level 15, State 1, Procedure uspPaging2, Line 28
Line 28: Incorrect syntax near ' + @TableName, N'.
Server: Msg 170, Level 15, State 1, Procedure uspPaging2, Line 37
Line 37: Incorrect syntax near ' + @TableName + '.
Server: Msg 170, Level 15, State 1, Procedure uspPaging2, Line 38
Line 38: Incorrect syntax near ' + @SizeString + '.
Server: Msg 170, Level 15, State 1, Procedure uspPaging2, Line 39
Line 39: Incorrect syntax near ' + @PrevString + '.

-----------------------------------------------------

CREATE PROCEDURE uspPaging(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL,
@TotalRecords VARCHAR(5) OUTPUT
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
exec sp_executeSql N''SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName, N'@TotalRecords int output', @TotalRecords output

END
ELSE
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)

exec sp_executeSql N''SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName, N'@TotalRecords int output', @TotalRecords output

END

RETURN @TotalRecords
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 14:26:44

had 2 quotes...
change
exec sp_executeSql N''SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName, N'@TotalRecords int output', @TotalRecords output
to
exec sp_executeSql N'SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName, N'@TotalRecords int output', @TotalRecords output

Go with the flow & have fun! Else fight the flow

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-09 : 14:32:10

Spirit1, it is nearly there. I made the change as you suggested. I am only getting the two following errors now:

Server: Msg 170, Level 15, State 1, Procedure uspPaging2, Line 28
Line 28: Incorrect syntax near '+'.
Server: Msg 170, Level 15, State 1, Procedure uspPaging2, Line 42
Line 42: Incorrect syntax near '+'.

I am not sure as nothing looks out of place to me but it is still not happy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-09 : 14:52:56

try this then:
declare @sqltemp nvarchar(100)

set @sqltemp = 'SELECT @TotalRecords = (COUNT(*) - 1)/' + @SizeString + ' + 1 FROM ' + @TableName

exec sp_executeSql @sqltemp , N'@TotalRecords int output', @TotalRecords output

Go with the flow & have fun! Else fight the flow

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-09 : 20:53:37

Yay it works!

Thanks for all of your help and patience Spirit1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources