Problem In Cursor

Hi all :wave:
I am facing some problem in cursor.

Code:

---

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

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

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

I did this and it worked

Code:

---

declare c cursor for select * from tcs_t
deallocate c

---

But this gets a syntax error

Code:

---

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

Quote:

---

Originally Posted by 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

Code:

---

declare c cursor for select * from tcs_t
deallocate c

---

But this gets a syntax error

Code:

---

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.