December 20, 2007 at 4:37 am
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
Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.
December 20, 2007 at 4:44 am
If you want to use dynamic SQL then you to use it for the whole statement. Something like this:
EXEC 'DECLARE C CURSOR FOR ' + @QueryString + ' '
I can't see why you need to use dynamic SQL, though... could you please explain?
John
December 20, 2007 at 5:36 am
Try this instead... No dynamic SQL needed! You should avoid it like the plague unless it's absolutely necessary. Your use of dynamic SQL introduced the definite possibility of sql injection attacks (search for that in google or on this site to see what such an attach is).
ALTER PROCEDURE [dbo].[Product_Despatch_Return]
@Search_Transaction_ID int,
@Search_Product_Name nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
Declare C cursor FOR
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
AND (
(@Search_Transaction_ID=NULL OR @Search_Transaction_ID=0) or (Product_Transction.Transaction_ID = @Search_Transaction_ID)
)
AND (
(@Search_Product_Name=NULL OR @Search_Product_Name='') OR (Product_Master.Product_Name = @Search_Product_Name )
)
I presume that you were just going to iterate through the cursor and return the data to the client app. If so, do not even use the cursor. Otherwise, perhaps you could provide info on what you want to do with the cursor once it has been created.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply