Issue with Sql Dynamic Query

Question

i'm trying to write a dynamic sql.following is my stored procedure and it's having a error with appending sql query to @SQLSTATEMENT can some one help me to solve this? it shows error under the + mark

EX - SET @SQLSTATEMENT + ' AND TA.AreaID='+@AreaID+' '

ALTER PROCEDURE spGetOverDueDocuments
@AreaID INT =NULL,
@DepartmentID INT= NULL,
@ABSID INT =NULL,
@DisciplineID INT = NULL,
@CPOID INT = NULL,
@DocumentTypeID INT = NULL,
@DueType VaRCHAR(20) = NULL
AS
BEGIN
DECLARE @SQLSTATEMENT VARCHAR(MAX)


SELECT @SQLSTATEMENT = 'SELECT * '+ 
'FROM tblActionHeader AH INNER JOIN '+
'tblDocumentRevisionActionHeader DAH ON AH.ActionHeaderID=DAH.ActionHeaderID INNER JOIN '+
'tblDocumentRevision DR ON DAH.DocumentRevisionID=DR.DocumentRevisionID INNER JOIN '+
'tblDocumentHeader DH ON DR.DocumentHeaderID=DH.DocumentHeaderID INNER JOIN tblABS TB ON DH.ABSID=TB.ABSID ' +
'INNER JOIN tblArea TA ON TA.AreaID=TB.AreaNo INNER JOIN  tblContractPODocumentHeader CDH ON '+
' CDH.DocumentHeaderID=DH.DocumentHeaderID INNER JOIN tblDiscipline DC ON '+
' DH.DisciplineID=DC.DisciplineID INNER JOIN tblContractPO CPO ON CPO.CPOID=DH.CPOID INNER JOIN tblDocumentType DT '+
' ON DT.DocumentTypeID=DH.DocumentTypeID '+
'WHERE AH.ActionTypeID=4 '
IF @AreaID<>'0'
BEGIN
SET @SQLSTATEMENT + ' AND TA.AreaID='+@AreaID+' '
END

IF @ABSID<>'0' 
BEGIN
SET @SQLSTATEMENT + ' AND TB.ABSID='+@ABSID+' '
END

IF @DisciplineID<>'0' 
BEGIN
SET @SQLSTATEMENT + ' AND DC.DisciplineID='+@DisciplineID+' '
END

IF @CPOID<>'0' 
BEGIN
SET @SQLSTATEMENT + ' AND CPO.CPOID='+@CPOID+' '
END

IF @DocumentTypeID<>'0' 
BEGIN
SET @SQLSTATEMENT + ' AND DT.DocumentTypeID='+@DocumentTypeID+' '
END

EXEC(@SQLSTATEMENT)

END

Sparky · Accepted Answer · 2011-10-08 17:10:23Z

2

Try

SET @SQLSTATEMENT = @SQLSTATEMENT  +  <rest>

i.e.

   SET @SQLSTATEMENT = @SQLSTATEMENT + ' AND TA.AreaID='+STR(@AreaID)+' '

answered Oct 8, 2011 at 17:10

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Icarus · Accepted Answer · 2011-10-08 17:12:31Z

2

@Area id is declared as INT, you need to cast it to varchar in order to be able to append it.

Try this:

SET @SQLSTATEMENT + ' AND TA.AreaID='+cast(@AreaID as varchar(30))+' '

answered Oct 8, 2011 at 17:12

Icarus

64k14 gold badges102 silver badges116 bronze badges

Comments

RyanHennig · Accepted Answer · 2011-10-08 22:34:39Z

Another option would be to use a query that doesn't require dynamic SQL

One possible way to do this would be:

SELECT * 
FROM tblActionHeader AH 
INNER JOIN tblDocumentRevisionActionHeader DAH ON AH.ActionHeaderID=DAH.ActionHeaderID 
INNER JOIN tblDocumentRevision DR ON DAH.DocumentRevisionID=DR.DocumentRevisionID 
INNER JOIN tblDocumentHeader DH ON DR.DocumentHeaderID=DH.DocumentHeaderID 
INNER JOIN tblABS TB ON DH.ABSID=TB.ABSID  
INNER JOIN tblArea TA ON TA.AreaID=TB.AreaNo 
INNER JOIN  tblContractPODocumentHeader CDH ON CDH.DocumentHeaderID=DH.DocumentHeaderID 
INNER JOIN tblDiscipline DC ON DH.DisciplineID=DC.DisciplineID 
INNER JOIN tblContractPO CPO ON CPO.CPOID=DH.CPOID 
INNER JOIN tblDocumentType DT ON DT.DocumentTypeID=DH.DocumentTypeID 
WHERE TA.AreaID = (CASE WHEN @AreaID<>'0' THEN @AreaID ELSE TA.AreaId END) 
AND TA.ABSID = (CASE WHEN @ABSID<>'0' THEN @ABSID ELSE TA.ABSID END) 
AND DC.DisciplineID = (CASE WHEN @DisciplineID<>'0' THEN @DisciplineID ELSE DC.DisciplineID END) 
AND CPO.CPOID = (CASE WHEN @CPOID<>'0' THEN @CPOID ELSE TA.ABSID END) 
AND DT.DocumentTypeID = (CASE WHEN @DocumentTypeID<>'0' THEN @DocumentTypeID ELSE TA.ABSID END)

In this query, the search predicates you were adding dynamically are now set to compare to themselves, unless another value is specified. This will work as long as none of them are null (because testing for NULL=NULL or NULL=x will always be false).

This may very well result in a better query which can be cached and reused. One thing to watch out for, however is parameter sniffing: If you run this SP the first time with all parameters = 0, the optimizer will choose (and cache) a very different execution plan than it would if most parameters are nonzero.

Collectives™ on Stack Overflow

Issue with Sql Dynamic Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related