Hello there!
For sake of the example; I have a stored procedure that is supposed to return
a result set, normal select statement, and a value as a parameter output.
My Table looks like this:
Car
ID CarName
1 Ferrari
2 Porsche
.. ..
Code:
USE [Temp]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcedureCars3]
(
@startIndex int,
@pageSize int,
@sortBy nvarchar(30),
@equals int,
@total int OUTPUT
)
AS SET NOCOUNT ON
DECLARE @upperBound int
IF @startIndex<1 SET @startIndex = 1
IF @pageSize<1 SET @pageSize = 1
SET @startIndex = (@startIndex-1)* @pageSize
IF @startIndex < 1 SET @startIndex = 1
IF @startIndex > 1 SET @startIndex = @startIndex + 1
SET @upperBound = @startIndex + @pageSize
DECLARE @table TABLE(rowNumber int, id int)
INSERT INTO @table(id)
SELECT E.ID /*SORTING OUT A FEW ROWS AT A TIME, FOR PAGING*/
FROM ( /*BY USING THE BUILT-IN ROW_NUMBER()*/
SELECT ROW_NUMBER() OVER(ORDER BY @sortBy) AS rowNumber, car.ID
FROM car
WHERE ID > CONVERT(nvarchar(9),@equals)
) AS E
WHERE E.rowNumber >= CONVERT(nvarchar(9),@startIndex) AND
E.rowNumber < CONVERT(nvarchar(9),@upperBound)
SET @total = (SELECT COUNT(id) FROM @table) /*SETS THE AMOUNT OF ROWS WITHIN @table, WORKS! THIS RETURNS @total as a parameter with correct value*/
SELECT t1.ID, t2.carname /*RETURNS JUST A SET OF DATA, WORKS*/
FROM @table AS t1
LEFT JOIN car as t2 ON t1.id = t2.ID
This procedure, returns all the cars, who has a greater ID than the input "equal" value. But not more results than the @UpperBound... Works as expected, so far.
Now (which also is my problem); I want to return an INT value, number of rows who actually has ID greater than @equals, so simply counting @table cannot be done (because I limit rows within it to @upperBound, sort of like a Top(@upperBound) statement..), unless I do some changes:
So I do this instead, at least trying (removed some code, declaring variables, creating procedure)...:
Code:
DECLARE @table TABLE(rowNumber int, id int)
INSERT INTO @table(id) /*INSERTING ALL ID'S (ROWS) WHO MATCHES MY QUERY INTO TABLE*/
SELECT car.ID FROM car
WHERE ID > CONVERT(nvarchar(9),@equals)
/*NOW; @table should contain a lot of rows, more than @upperbound*/
/*TRYING TO GET THE COUNT FROM @table now like this:*/
SET @total = (SELECT COUNT(id) FROM @table) /*DOES NOT WORK, returns nothing*/
SELECT @total = COUNT(id) from @table /*DOES NOT WORK, returns nothing*/
SET @total = 10 /*DOES NOT WORK, returns nothing*/
SELECT E.id, E.carname /*THEN RETURNING DATA; JOINING ON THE ID STORED IN @table*/
FROM /*THIS WORKS; RETURNS WHAT I WANT...*/
(
SELECT ROW_NUMBER() OVER(ORDER BY @sortBy) AS rowNumber, t1.id, t2.carname
FROM @table as t1
LEFT JOIN car as t2 ON t1.id = t2.ID
) as E
WHERE E.rowNumber >= CONVERT(nvarchar(9),@startIndex) AND
E.rowNumber < CONVERT(nvarchar(9),@upperBound)
This does not return my @total as a parameter, output. What in the blue hell am I doing wrong?
When I say it returns nothing... I cannot get the parameter value, it is NULL, code below is C#
Code:
procedure.Parameters["@total"].Direction = ParameterDirection.Output; /*Initialize the sql command to be executed*/
.****n query...
value = procedure.Parameters["@total"].Value.ToString(); /*Get output parameters*/
But in the first query shown, the value is correct to what I set it to be: @table count(ID), which is the @upperBound limit (5, if anyone wonders...)
Even this fails:
Code:
SET @total =
(
SELECT COUNT(ID) FROM car where ID > CONVERT(nvarchar(9),@equals)
)
It just will not return the @total as a parameter anymore... Seems like I need to set @total, a temp variable, to the count of a new @tempTableVariable?...
So I need actually two temp tables for this simple task? Hm... I'll go test it out...
Note: I do know I have three/four different "Set @total = ..." in the example above. But I have tried them one by one...
dBforums
>
SQL - Stored Procedure - Output parameter - I do not get this...
06-15-11, 07:08
SQL - Stored Procedure - Output parameter - I do not get this...
Linear Mode
