Link to home
Create AccountLog in
Avatar of shieldguy
shieldguyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Syntax Error in creating the stored procedure

I am getting the syntax error in creating the stored procedure.

I am using the T SQL in the sql server 2005


The sql server is showing that the error is comming on this line

"And + @FilterIsSet"

Error:
"Msg 4145, Level 15, State 1, Procedure GetReturnProductByQuantityMaxRows, Line 59
An expression of non-boolean type specified in a context where a condition is expected, near 'And'."

Thanks
Create PROCEDURE [dbo].[GetReturnProductByQuantityMaxRows]
 @StartDateAdded as nVarchar(50),
 @EndDateAdded as nVarchar(50),
 @IsFaulty as nVarchar(10),
 @IsSet as nVarchar(10),
 @maximumRows as int OUTPUT
AS
 
BEGIN
	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	DECLARE @FilterFaulty nVarchar(10)
	DECLARE @FilterIsSet nVarchar(10)
 
	SELECT @StartDate = DateAdd(day, 0, @StartDateAdded)       
	SELECT @EndDate = DateAdd(day, +1, @EndDateAdded)       
 
	IF UPPER(@IsFaulty) = 'Y'
	  BEGIN
		Set @FilterFaulty='cs.ReturnReason=''Faulty Product'''
	  END
	ELSE
	  BEGIN
		Set @FilterFaulty='cs.ReturnReason<>''Faulty Product'' '
	  END
 
	IF UPPER(@IsSet) = 'Y'
	  BEGIN
		Set @FilterIsSet='NOT EXISTS(Select 1 From ProductAssembly a Where o.ProductID = a.Products)'
	  END
	ELSE
	  BEGIN
		Set @FilterIsSet='EXISTS(Select 1 From ProductAssembly a Where o.ProductID = a.SubProduct)'
	  END
			-- SET NOCOUNT ON added to prevent extra result sets from
			-- interfering with SELECT statements.
			SET NOCOUNT ON;
 
	SELECT 
		@maximumRows=IsNull(Count(o.ProductID),0) 
	FROM 
		tblCustomerReturnsStatistics cs
	LEFT OUTER JOIN 
		OrderedItems_WithCatText o
		   ON o.OrderID = cs.OrderID
	WHERE
		cs.DateAdded >= + @StartDate
		And cs.DateAdded < + @EndDate
		And o.QuantityDespatched < 0
		And + @FilterFaulty
		And + @FilterIsSet
END

Open in new window

Avatar of pollock_d
pollock_d

your @FilterFaulty and @FilterIsSet are not booleans, they are nVarhchar
Avatar of shieldguy

ASKER

what do u mean by that ?
Avatar of Lowfatspread
if you want to use dynamic sql (BAD IDEA)

then you need to assign the sql command to a string and then execute it...


e.g.

declare @sql varchar(8000)
set @sql = 'select .... from ... where filterfaulty = ' + @filterfaulty
         + ' and filterisset = ' + @filterisset

--Print @sql    -- for debug
 Exec(@SQL)
       
Yeah you've got someting crazy going on with your where clause

And + @FilterFaulty
And + @FilterIsSet

SQL isn't going to be happy with that syntax.  
I had set it to nvarchar by myself so why I am getting booleanbs error where is it getting the idea of boolean?
actaully I need the select query to set value of the @maximumRows but if I used the exec is it going to work

Can you please provde me sample example thanks
e.g.


declare @sql varchar(8000)

set @sql='SELECT IsNull(Count(o.ProductID),0)  '
      +'FROM tblCustomerReturnsStatistics cs '
      +' LEFT OUTER JOIN OrderedItems_WithCatText o'
      +'   ON o.OrderID = cs.OrderID'
      +' WHERE cs.DateAdded >= ''' + convert(char(26),@StartDate,101) +''''
      +'   And cs.DateAdded <''' + convert(char(26),@EndDate,101) +''''
      +'   And o.QuantityDespatched < 0'
      +'   And ' + @FilterFaulty
      +'   And ' + @FilterIsSet
      
print @sql -- for debugging      
Exec(@SQL)
SOLUTION
Avatar of ptjcb
ptjcb
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I had now cretaed my stored procedure like this but still no luck I am unable to print the value of the output variable

What is going wrong here I guess its the exec its didnt support passing value to the output varaiable


Create PROCEDURE [dbo].[GetReturnProductByQuantityMaxRows]
 @StartDateAdded as nVarchar(50),
 @EndDateAdded as nVarchar(50),
 @IsFaulty as nVarchar(10),
 @IsSet as nVarchar(10),
 @maximumRows as int OUTPUT
AS
 
BEGIN
 
	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	DECLARE @FilterFaulty nVarchar(10)
	DECLARE @FilterIsSet nVarchar(10)
	DECLARE @sql Varchar(8000)
 
	SELECT @StartDate = DateAdd(day, 0, @StartDateAdded)       
	SELECT @EndDate = DateAdd(day, +1, @EndDateAdded)       
 
	IF UPPER(@IsFaulty) = 'Y'
	  BEGIN
		Set @FilterFaulty='cs.ReturnReason=''Faulty Product'''
	  END
	ELSE
	  BEGIN
		Set @FilterFaulty='cs.ReturnReason<>''Faulty Product'' '
	  END
 
	IF UPPER(@IsSet) = 'Y'
	  BEGIN
		Set @FilterIsSet='NOT EXISTS(Select 1 From table3 a Where o.ProductID = a.Products)'
	  END
	ELSE
	  BEGIN
		Set @FilterIsSet='EXISTS(Select 1 From table3 a Where o.ProductID = a.SubProduct)'
	  END
 
Set @sql = 'SELECT ' +
			Cast(@maximumRows As varchar(50)) + '=IsNull(Count(o.ProductID),0) ' +
	'FROM table1 cs ' +
	'LEFT OUTER JOIN ' +
		table2 o ' +
		   'ON o.OrderID = cs.OrderID ' +
	'WHERE ' +
		'cs.DateAdded >= ' + Cast(@StartDate As varchar(50)) +
		'And cs.DateAdded < ' + Cast(@EndDate As varchar(50)) +
		'And o.QuantityDespatched < 0 ' +
		'And ' + Cast(@FilterFaulty As varchar(50)) +
		'And ' + Cast(@FilterIsSet As varchar(50))
 
			-- SET NOCOUNT ON added to prevent extra result sets from
			-- interfering with SELECT statements.
			SET NOCOUNT ON;
 
		--print(@sql)  -- For Debugging
		exec(@sql)
END

Open in new window

Ok I got It worrking now, I just remove the @Isfaulty variable and its check as I think its not so important for me now. Here is my stored procedure I hope it will be helpfull for someone.

I really appreciate all of you guys help

If you guys see any errors or problem in my code please let me know

Thanks
Create PROCEDURE [dbo].[GetReturnProductByQuantity]
 @StartDateAdded as nVarchar(50),
 @EndDateAdded as nVarchar(50),
 @OrderBy as nVarchar(50),
 @isSet as nVarchar(3),
 @CurrentPageIndex as int,
 @totalRowsPerPage as int
AS
BEGIN
 
	--Decalaring the variables
	Declare @SqlOrderBy as nvarchar(200)
	DECLARE @StartDate datetime
	DECLARE @EndDate datetime
	Declare @endRowIndex as int
	Declare @startRowIndex as int
	Declare @maximumRows as int
 
	Set	@CurrentPageIndex = @CurrentPageIndex - 1
 
	--Selecting the page index
	If @CurrentPageIndex <= 0 
		Begin 
			Set @startRowIndex = 0
		End
	Else
		Begin
			Set @startRowIndex = @CurrentPageIndex * @totalRowsPerPage
		End
 
	--Selecting the end of row for the page	
	Set @endRowIndex = @startRowIndex + (@totalRowsPerPage -1)
 
	--Selecting the dates 
	SELECT @StartDate = DateAdd(day, 0, @StartDateAdded)       
	SELECT @EndDate = DateAdd(day, +1, @EndDateAdded)       
 
	--Selecting the order by statement
	If @OrderBy <> ''
		Begin
			SET  @SqlOrderBy = ' Order By ' + Cast(@OrderBy As varchar(50))
		End
    Else
		Begin
			SET  @SqlOrderBy = ' Order By RowNum' 
		End
 
 
	--Now selecting the maximunRows value in the @maximumRows acoording to the given filter
	IF UPPER(@isSet)='NO'
		BEGIN
			SELECT 
				@maximumRows = IsNull(Count(o.ProductID),0) 
			FROM 
				tblCustomerReturnsStatistics cs
			LEFT OUTER JOIN 
				OrderedItems_WithCatText o
				   ON o.OrderID = cs.OrderID
			Where
				cs.ReturnReason= 'Faulty Product'
				And cs.DateAdded >= + @StartDate
				And cs.DateAdded < + @EndDate
				And o.QuantityDespatched < 0
				And NOT EXISTS(Select 1 From ProductAssembly a where o.ProductID = a.Products)
		END
	ELSE IF UPPER(@isSet)='YES'
		BEGIN
			SELECT 
				@maximumRows = IsNull(Count(o.ProductID),0) 
			FROM 
				table1 cs
			LEFT OUTER JOIN 
				table2 o
				   ON o.OrderID = cs.OrderID
			Where
				cs.ReturnReason= 'Faulty Product'
				And cs.DateAdded >= + @StartDate
				And cs.DateAdded < + @EndDate
				And o.QuantityDespatched < 0
				And EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
		END
 
	
	--Now selecting the endrows and checking if we are selecting the value by keeping the end of total rows	
	--in mind to avoid errors
	If @endRowIndex >= @maximumRows 
		Begin	
			Set @endRowIndex = @maximumRows 
		End
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	 
	--If the value is yes then the query will select all the products except the product set
	IF UPPER(@isSet)='NO'
		BEGIN
			EXEC('Select RowNum, ProductID, Manufacturer, ProductName, QuantityDespatched
						 From  
							(SELECT 
								o.ProductID ProductID,
								Max(o.Manufacturer) Manufacturer, 
								Max(o.ProductName) ProductName, 
								Sum(abs(o.QuantityDespatched)) QuantityDespatched,
								ROW_NUMBER() OVER(ORDER BY o.ProductID) as RowNum
							FROM 
								table1 cs
							LEFT OUTER JOIN 
								table2 o
								   ON o.OrderID = cs.OrderID
							Where
								cs.ReturnReason= ''Faulty Product''
								And cs.DateAdded >= ''' + @StartDate + '''
								And cs.DateAdded < ''' + @EndDate + '''
								And o.QuantityDespatched < 0
								And NOT EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
							Group By
							o.ProductID) t1
						WHERE 
					t1.RowNum BETWEEN ' + @startRowIndex + ' AND ' + @endRowIndex + '
				  ' + @SqlOrderBy)
		END
	ELSE IF UPPER(@isSet)='YES'
		BEGIN
			EXEC('Select RowNum, ProductID, Manufacturer, ProductName, QuantityDespatched
					 From  
						(SELECT 
							o.ProductID ProductID,
							Max(o.Manufacturer) Manufacturer, 
							Max(o.ProductName) ProductName, 
							Sum(abs(o.QuantityDespatched)) QuantityDespatched,
							ROW_NUMBER() OVER(ORDER BY o.ProductID) as RowNum
						FROM 
							table1 cs
						LEFT OUTER JOIN 
							table2 o
							   ON o.OrderID = cs.OrderID
						Where
							cs.ReturnReason= ''Faulty Product''
							And cs.DateAdded >= ''' + @StartDate + '''
							And cs.DateAdded < ''' + @EndDate + '''
							And o.QuantityDespatched < 0
							And EXISTS(Select 1 From table3 a where o.ProductID = a.Products)
						Group By
						o.ProductID) t1
					WHERE 
				t1.RowNum BETWEEN ' + @startRowIndex + ' AND ' + @endRowIndex + '
			  ' + @SqlOrderBy)
		END
END

Open in new window

Thanks alot for all your support
I have one more question if I like to return the @maximumRows variable value as an output along with the resultset which I am getting right now with the select query

Is it possible?

Thanks