asked on

Stored procedure returning values

I have the following stored procedure: it prints value of @retval as 2 in the query analyzer. But, when I call the SP from ASP .Net form, it returns the value of @retval as 0.

CREATE PROCEDURE [dbo].[odItemValuesCount1]
(
      @retval int output,
      @regdate datetime,
      @duedate datetime,
      @categoryname varchar(50),
      @itemvalue1 varchar(50) = '%',
      @itemvalue2 varchar(50) = '%',
      @itemvalue3 varchar(50) = '%',
      @itemvalue4 varchar(50) = '%',
      @itemvalue5 varchar(50) = '%',
      @itemvalue6 varchar(50) = '%',
      @itemvalue7 varchar(50) = '%',
      @itemvalue8 varchar(50) = '%',
      @itemvalue9 varchar(50) = '%',
      @itemvalue10 varchar(50) = '%'
)
AS
--begin transaction
set nocount on
select @retval = 0
declare @count int
select @count =0

declare itemcatid1 cursor for
select System_itemCategoryID from odItemCategory where names = @categoryname

declare @itemcatid1 int
open itemcatid1
fetch itemcatid1 into @itemcatid1
close itemcatid1
deallocate itemcatid1

declare @count1 int

/* counts all items whethere they are available, reserved or rented from the database*/

set @count1 = (select count (*) from odItem where System_itemID IN (
SELECT distinct (odItemProperties.System_itemID)
            FROM odItemProperties
            WHERE (
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue1))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue2))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue3))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue4))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue5))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue6))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue7))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue8))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue9))
            AND
            (System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue10))



) and (System_itemStatusID = 1 or System_itemStatusID = 2 or System_itemStatusID = 5) and System_itemCategoryID = @itemcatid1 and deleted = 0))

/* */
declare itemid1 cursor read_only for

SELECT distinct (odItemProperties.System_itemID)
            FROM odItemProperties, odItem
            WHERE (
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue1))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue2))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue3))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue4))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue5))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue6))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue7))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue8))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue9))
            AND
            (odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue10))
            and
            odItem.System_itemCategoryID = @itemcatid1

)

declare @count2 int
select @count2 = 0

declare @count3 int
select @count3 = 0

declare @itemid as int
open itemid1
fetch next from itemid1 into @itemid

declare itemcatid cursor for

select System_itemCategoryID from odItem where System_itemID = @itemid

declare @itemcatid as int
open itemcatid
fetch itemcatid into @itemcatid
close itemcatid
deallocate itemcatid

while (@@fetch_status <> -1)
begin
if( (select System_itemID from odTripsItem where System_itemID = @itemid and deleted = 0 and odTripsItem.System_tripID IN
(select odPersonalTrips.System_tripID from odPersonalTrips where @duedate >= registrationDate and @regdate <= dueDate )) = 1 )
begin
set @count2 = @count2 + 1
end
fetch next from itemid1 into @itemid
end
close itemid1
deallocate itemid1

declare count3 cursor for
select reservedQuantity from odTripsItemCategory where System_itemcategoryID = @itemcatid and deleted = 0 and odTripsItemCategory.System_tripID IN
(select odPersonalTrips.System_tripID from odPersonalTrips where @duedate >= registrationDate and @regdate <= dueDate)

open count3
fetch count3 into @count3
close count3
deallocate count3

declare @temp int
select @temp =0

set @count = @count1 - @count2 - @count3

set @retval = @count
--print @retval
return
GO

The form side code that calls this SP is:

int RetVal = 0;
                  for (int i=0; i<properties.Length;i++)
                  {
                        if(properties[i]==null)
                              properties[i]="";
                  }

                  SqlParameter[] prams = { SqlHelperUtil.MakeOutParam("@retval", SqlDbType.Int, 4),
                                                       SqlHelperUtil.MakeInParam("@regdate", SqlDbType.DateTime,8, regdate),
                                                       SqlHelperUtil.MakeInParam("@dueDate", SqlDbType.DateTime,8,dueDate),
                                                       SqlHelperUtil.MakeInParam("@categoryname", SqlDbType.VarChar,50,categoryName),
                                                       SqlHelperUtil.MakeInParam("@itemvalue1", SqlDbType.VarChar,50, properties[0]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue2", SqlDbType.VarChar,50, properties[1]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue3", SqlDbType.VarChar,50, properties[2]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue4", SqlDbType.VarChar,50, properties[3]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue5", SqlDbType.VarChar,50, properties[4]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue6", SqlDbType.VarChar,50, properties[5]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue7", SqlDbType.VarChar,50, properties[6]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue8", SqlDbType.VarChar,50, properties[7]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue9", SqlDbType.VarChar,50, properties[8]),
                                                       SqlHelperUtil.MakeInParam("@itemvalue10", SqlDbType.VarChar,50, properties[9])};

                  try
                  {
                        //int RunProcReturn =
                        int RunProcReturn = SqlHelper.ExecuteNonQuery(IUOA.Db.SQLConnection.GetConnectString(),CommandType.StoredProcedure, "dbo.odItemValuesCount1",prams);
                        RetVal = (int) prams[0].Value;
                  }

Can someone please tell why this is happening?

Anthony Perkins

Change this:
return

To:
return @retval

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

m1tk4

When you try to run it through Query Analyser/Enterprise manager uses MSDASQL as ODBC Provider. ASP probably uses SQLOLEDB.

The difference between the two I was fighting with is that with SQLOLEDB any stored procedure that first executes an insert or update and then select does not return a recordset. If you select first, then insert/update it does. I was never able to figure how to fix it and ended up rewriting the stored procs.

If this is your problem and you find a way to fix it, let me know ;)

Anthony Perkins

>>I was never able to figure how to fix it and ended up rewriting the stored procs.<<
You simply forgot to add the line at the top of your Stored Procedure:
SET NOCOUNT ON

Either that or you could have used the NextRecordset to cycle through all the empty recordsets until you got to the one you needed.

That is the big difference between OLEDB and ODBC.

But we are getting side-tracked this is unrelated to the question posed here as:
A. They are not using ADO, but rather ADO.NET and more importantly
B. Are not returning (nor do they appear to wish to return) any resultset whatsoever.

m1tk4

acperkins,

The weird thing is it did not return any recordsets - I tried that. Didn't try NOCOUNT, thanks!

Anthony Perkins

>>The weird thing is it did not return any recordsets - I tried that.<<
Trust me, they are there.

IUAATech

ASKER

according to 1 st comment, changed return to return @retval, when tried using
RetVal = (int) prams[1].Value;

it obviously threw an exception of typecast as prams[1] is a datetime. none of the above worked

Anthony Perkins

Fair enough. Good luck.

dlarlick

I would like to question your intended method of retrieving the "return value". There are two ways you can get this out. As a true return value or as an output variable. Your stored procedure has the @retval specified as an output variable. This means that your client must have the parameter direction set appropriately to output, NOT ReturnValue. Should you be attempting to use it as ReturnValue. Remove it from your parameter list on the stored procedure and follow acperkins recommendation.

please post your client side source as this will provide additional information in diagnosing your situation.

Also you say it works in query analyzer?

call it from query analyzer

exec odItemValuesCount1 @param1, @param2, @param3 (etc)

or

Dim @TestValue int

set @TestValue = odItemValuesCount1 @param1, @param2, @param3 (etc)

make sure your return value "works" from query analyzer, saying the "print" statement in your example works is not sufficient to indicate your problem.

syntax is not confirmed... please double check prior to attempting.

IUAATech

ASKER

There was a problem with the form side code, the stored procedure was perfect, the error was in passing the properties as array