March 18, 2005 at 1:55 pm
Hello there,
I have adapted a "paging records feature using T-SQL" script to my needs, but I still need to count the whole amount of records(not just the 25 or 50 that are going to be retrieved).
I used this dynamic query in the same store proc:
...
SET @Query=N' DECLARE @Total INT
SELECT @Total=COUNT('+@PrimaryKey+N') FROM '+@TableName
exec sp_executesql @Query
return @Total --Error here, and if I declare this variable, then returns 0
Is there any way I could return the selected records and this value?
Regards
Orlando
P.S. Sorry about my English
March 18, 2005 at 2:05 pm
See sp_executesql system stored procedure in BOL, for examples of returning output variables from dynamic SQL.
March 19, 2005 at 6:13 am
Hi,
Declare a variable as OUTPUT. You can get the value even from the front end.
Refer this book " Develop an accounting package using VB" for more exercises. http://www.vkinfotek.com
regards
bharathi
March 21, 2005 at 3:28 am
Orlando,
Syntax should sit something like this:-
DECLARE @Query nvarchar(4000),
@Total int,
@PrimaryKey int,
@TableName varchar(255)
SET @primarykey-2 = 1
SET @TableName = 'ATableName'
SET @Query = N'SET @Total = (SELECT COUNT('+ CAST(@PrimaryKey as NVarchar(4)) + N') FROM ' + @TableName + N')'
EXEC sp_executesql @Query, N'@Total int OUTPUT', @Total OUTPUT
Have fun
Steve
We need men who can dream of things that never were.
March 21, 2005 at 7:14 am
Thanks all,
I did this way:
Declare @TotalRegs INT
...
SET @Query=N'SELECT @Total=COUNT('+@PrimaryKey+N') FROM '+@TableName
...
exec sp_executesql @Query, N'@Total INT OUTPUT', @Total=@TotalRegs OUTPUT
RETURN @TotalRegs
Regards,
Orlando Otero
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply