Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unhappy Unanswered: SQL - Stored Procedure - Output parameter - I do not get this...

    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...
    Last edited by ManyTimes; 06-15-11 at 08:29.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Just to clarify, does the output parameter work from T-SQL alone (without the C# bits)?
    as an example (making up a bunch of inputs):
    Code:
    declare @out int
    exec ProcedureCars3 1, 10, 'sort', 1, @out output
    
    select @out

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    MCrowley, thank you so much!

    I could do that, which led me to what I actually should have figured out a long time ago, the SQL were correct while the query I built up through C# was not!
    Thanks!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •