Problem In Cursor

[shakti5385]

Hi all
I am facing some problem in cursor.

ALTER PROCEDURE [dbo].[Product_Despatch_Return]
@Search_Transaction_ID int,
@Search_Product_Name nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE 	@QueryString nvarchar(MAX)

SET @QueryString='SELECT Product_Transction.Transaction_ID AS Transaction_ID, Product_Transction.Transaction_Date AS Transaction_Date,Product_Transaction_Details.Product_Id AS Product_Id, Product_Master.Product_Name AS Product_Name,Product_Transaction_Details.Qty AS OrderQuantity,Product_Transaction_Details.Details_Id AS Product_Transaction_Details_Id FROM Product_Transction INNER JOIN  Product_Transaction_Details ON Product_Transction.Transaction_ID = Product_Transaction_Details.Transaction_Id INNER JOIN  Product_Master ON Product_Transaction_Details.Product_Id = Product_Master.Product_ID WHERE Product_Transction.Is_Deleted=0 AND Product_Transaction_Details.Is_Deleted=0 '
	-- CHECKING THE @Search_Transaction_ID
	IF NOT (@Search_Transaction_ID=NULL OR @Search_Transaction_ID=0)
	BEGIN
		SET @QueryString=@QueryString+ ' Product_Transction.Transaction_ID='+CAST(@Search_Transaction_ID AS NVARCHAR)
	END
	-- CHECKING THE @Search_Product_Name
	IF NOT (@Search_Product_Name=NULL OR @Search_Product_Name='')
	BEGIN
		SET @QueryString=@QueryString+ ' Product_Master.Product_Name='''+@Search_Product_Name+''''
	END

	DECLARE C CURSOR FOR @QueryString ' Error is Here
	OPEN C

I am facing error in the next line.
DECLARE C CURSOR FOR @QueryString ' Error is Here
Incorrect syntax near '@QueryString

[brin351]

guessing this is sql server? could the problem be a few lines up at (@Search_Transaction_ID=NULL or... should be (@Search_Transaction_ID IS NULL or ....

Just a thought...

[szlamany]

And what is making you think that the CURSOR can be set to a variable string of SQL?

I did this and it worked

declare c cursor for select * from tcs_t
deallocate c

But this gets a syntax error

declare @str varchar(100)
set @str='select * from tcs_t'
declare c cursor for @str
deallocate c

You will have to change your logic so that the WHERE clause has OR's and what not...

[Sedna]

And what is making you think that the CURSOR can be set to a variable string of SQL?

I did this and it worked

declare c cursor for select * from tcs_t
deallocate c

But this gets a syntax error

declare @str varchar(100)
set @str='select * from tcs_t'
declare c cursor for @str
deallocate c

You will have to change your logic so that the WHERE clause has OR's and what not...

Learn something new every day!

Also, your if conditions don't append an 'AND' or 'OR' so that won't work as a regular query anyways... I think.