NHibernate does not support stored procedures with output parameters. Another thread on this subject: http://groups.google.com/group/nhusers/browse_thread/thread/d74611f75608d582/2cdeeaf83ab237a6
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 at http://groups.google.com/group/nhusers?hl=en.
