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]>
> > .
> > 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.
