But as you can see, I do not try to use OUTPUT params. It is just legacy SP, that can't be refactored easily, so I added a SELECT in the end of the procedure. Or the existence of an output parameter may cause problems I have?
On Apr 27, 1:38 pm, Diego Mijelshon <[email protected]> wrote: > NHibernate does not support stored procedures with output parameters. > > Another thread on this > subject:http://groups.google.com/group/nhusers/browse_thread/thread/d74611f75... > > Diego > > On Tue, Apr 27, 2010 at 04:02, Andrey Selitsky > <[email protected]>wrote: > > > > > SQL: > > > ALTER PROCEDURE [dbo].[IsProductBelongsCategories] > > �...@categoryids nvarchar(100), > > �...@productid nvarchar(30), > > �...@isexist bit OUTPUT, -- whether product ID exist in the > > product > > table > > �...@isbelongs bit OUTPUT, -- whether product belong to the > > indicated > > categories, > > -- or if categories are not > > indicated then whether product belong > > to any category at all > > �...@isdiscontinued bit OUTPUT, -- if product is discontinued, valid > > value is returned only if @isExist = 1 > > �...@debug bit = 0 > > AS > > DECLARE @sql nvarchar(2000) > > DECLARE @isBelongsProd bit > > DECLARE @stockStatus varchar(10) > > DECLARE @rowCount int > > > SET @isExist = 0 > > SET @isBelongs = 0 > > SET @isDiscontinued = 0 > > > SET @stockStatus = '' > > SELECT �...@stockstatus = stockstatus > > FROM product > > WHERE productid = @productID > > SET @rowCount = @@ROWCOUNT > > IF @rowCount > 0 > > SET @isExist = 1 > > IF UPPER(RTRIM(ISNULL(@stockStatus, ''))) = 'D' > > SET @isDiscontinued = 1 > > > IF @isExist = 1 > > BEGIN > > IF @categoryIDs = '' > > SET @categoryIDs = NULL > > > SET @sql = ' > > SET @isBelongs = 0 > > IF EXISTS( > > SELECT p.productid > > FROM product p INNER JOIN productcategory pcat > > ON pcat.productid = p.productid > > INNER JOIN category cat > > ON cat.categoryid = pcat.categoryid > > WHERE p.productid = ''' + @productID + ''' > > AND (''' + ISNULL(@categoryIDs, '') + ''' = > > '''' > > OR cat.CategoryID IN (' + > > ISNULL(@categoryIDs, 0) > > + ')) > > ) > > SET @isBelongs = 1 > > ' > > EXEC sp_executesql @sql, N'@isBelongs bit OUTPUT', > > �...@isbelongsprod OUTPUT > > SET @isBelongs = @isBelongsProd > > END > > > IF @debug = 1 > > BEGIN > > PRINT 'IsExist=' + CONVERT(varchar(1), @isExist) + ', > > IsBelong=' > > + CONVERT(varchar(1), @isBelongs) + ', StockStatus=' > > + @stockStatus > > PRINT @sql > > END > > > SELECT �...@isexist, > > �...@isbelongs, > > �...@isdiscontinued > > > GO > > > C#: > > > public bool ProductBelongsToCategories(string categoryIDs, > > string productID, ref bool isExist, > > ref bool > > isDiscontinued) > > { > > var sqlQuery = > > Dao.CreateSQLQuery(StoredProcedureName.FormatProcedureCall( > > > BelongsToCategories.Name, > > > BelongsToCategories.CategoryIDs, > > > BelongsToCategories.ProductID, > > > BelongsToCategories.IsExist, > > > BelongsToCategories.IsBelongs, > > > BelongsToCategories.IsDiscontinued)); > > sqlQuery.SetParameter(BelongsToCategories.CategoryIDs, > > categoryIDs); > > sqlQuery.SetParameter(BelongsToCategories.ProductID, > > productID); > > sqlQuery.SetParameter(BelongsToCategories.IsExist, false); > > sqlQuery.SetParameter(BelongsToCategories.IsBelongs, > > false); > > sqlQuery.SetParameter(BelongsToCategories.IsDiscontinued, > > false); > > var result = sqlQuery.UniqueResult<object[]>(); > > sqlQuery.SetCacheable(false); > > isExist = (bool) result[0]; > > isDiscontinued = (bool) result[2]; > > return (bool) result[1]; > > } > > > On Apr 26, 6:13 pm, Diego Mijelshon <[email protected]> wrote: > > > You know what would help? the actual SQL and both resultsets. > > > > Diego > > > > On Mon, Apr 26, 2010 at 11:33, Andrey Selitsky < > > [email protected]>wrote: > > > > > hi, I am trying to call stored procedure from NHibernate via > > > > SQLQuery. > > > > The problem is that when I copy the sql generated by Nhibernate (I see > > > > it in SQL Profiler) and execute it directly in SQL server the result > > > > is different from the result returned by Nhibernate? > > > > > What is wrong here? > > > > > -- > > > > You received this message because you are subscribed to the Google > > Groups > > > > "nhusers" group. > > > > To post to this group, send email to [email protected]. > > > > To unsubscribe from this group, send email to > > > > [email protected]<nhusers%[email protected]> > > <nhusers%[email protected]<nhusers%[email protected]> > > > > > . > > > > For more options, visit this group at > > > >http://groups.google.com/group/nhusers?hl=en. > > > > -- > > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > [email protected]<nhusers%[email protected]> > > . > > > For more options, visit this group athttp:// > > groups.google.com/group/nhusers?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]<nhusers%[email protected]> > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group > athttp://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
