Thanks, Diego. On Apr 28, 1:49 pm, Diego Mijelshon <[email protected]> wrote: > 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 > 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.
