It's possible. My suggestion is that you create an empty SP that does a simple select to see if it works, and then add the "complicated" stuff a piece at a time, to see what breaks it.
Diego On Wed, Apr 28, 2010 at 03:07, Andrey Selitsky <[email protected]>wrote: > 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]> > > > > > <nhusers%[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]> > <nhusers%[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]> > <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.
