SQL Server Performance Forum – Threads Archive

stroed procedure doubt

I am writing the following SP, on the query analyzer, it runs correctly and returns 2, which is the expected result, but when I use ASP.NET and call the SP from the form side, it returns 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 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 @itemvalue<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) 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 @itemvalue<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) 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 ) –print @count1 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 –print @count2 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 –print @count3 declare @temp int select @temp = @count1 – @count2 –select @count = 4 –print @count –return @count –select @retval = @count select @retval = @temp – @count3 print @retval –commit return @retval GO

Remove all PRINT lines from your stored procedures. Client apps often have problems dealing with those PRINTs. Only use PRINT for debugging through QA.

You have to explicitly define retValue parameter as output parameter in stored procedure call. How do you actuall check @retValue value? For output parameter it is enough to assign a value. RETURN keyword is used to return an integer value as kind of execution status code. So this value is defined on the client as return code or return value type parameter (not sure). There is no need to define it both as output parameter and return it with return statement.

Removed all the print statements, also used just "return" instead of "return @retval" as suggested in other post, but didnt work

Did you follow up on Mirko’s first remark? DECLARE @1 INT, @2 DATETIME, @3 DATETIME
SET …………………………. EXEC dbo.odItemValuesCount1 @1 OUTPUT, @2, @3 After the EXEC, your local variable @1 will contain the value returned by the procedure.

Have you addressed the problem of how paramaters are specified in asp.Net code?

This is how I call it from the form side availableQuantity=Db.odItemCollection.AvailableQuantityOfItem(DateTime.Parse(txtReserveDate.Text),
DateTime.Parse(txtDueDate.Text), drowItem["ItemCategory"].ToString(), properties);
And odItemCollection has the SP as: 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 = SqlHelper.ExecuteNonQuery(IUOA.Db.SQLConnection.GetConnectString(),CommandType.StoredProcedure, "dbo.odItemValuesCount1",prams);
RetVal = (int) prams[0].Value;
}

]]>