Hello friends,
I'm trying to create dynamic SQL statement, but I'm getting following error
Conversion failed when converting the varchar value to data type int
What does this error actually means and where is this error in my StoredProcedure
I paste down my StoredProcedure
DECLARE
@JOBTYPE INT,
@JOBID INT,
@SQL VARCHAR(2000),
@CLIENTID INT,
@BRANDCATEGORYID INT,
@BRANDID INT
SET @JOBTYPE=2
SET @JOBID=57032
SELECT @JOBTYPE AS JOBTYPE,@JOBID AS JOBID
IF @JOBTYPE IN (2,5)
BEGIN
SELECT @CLIENTID=CAST(I.clientid AS INT),@BRANDCATEGORYID=CAST(M.brandcategoryid AS INT),
@BRANDID=CAST(I.brandid AS INT)FROM tbl_iteration I
INNER JOIN tbl_mechanicalbrief M ON M.mechanicalid = I.mechanicalid
INNER JOIN tbl_client C ON C.clientid = M.clientid
INNER JOIN tbl_brandcategory BC ON BC.brandcategoryid = M.brandcategoryid
INNER JOIN tbl_brand B ON B.brandid = I.brandid
WHERE I.iterationid = @JOBID
END
SELECT @CLIENTID AS CLIENTID,@BRANDCATEGORYID AS BRANDCATEGORYID, @BRANDID AS BRANDID --THESE ARE ALL INTEGER VALUE
SET @SQL = 'select Q.noticeid clientname,brandcategoryname,brandname,comment from tbl_QCNotice Q
INNER JOIN tbl_client C on C.clientid = Q.clientid
LEFT OUTER JOIN tbl_brandcategory BC on BC.brandcategoryid = Q.brandcategoryid
LEFT OUTER JOIN tbl_brand B on B.brandid = Q.brandid '
SELECT @SQL --UPTO HERE ITS WORKING FINE
IF @CLIENTID <> 0
SET @SQL = @SQL + ' WHERE Q.clientid IN (' + @CLIENTID + ')'
SELECT @SQL --HERE IT GIVES ERROR
--IF @BRANDCATEGORYID <> 0
SET @SQL = @SQL + ' AND Q.brandcategoryid IN (0,'+ @BRANDCATEGORYID +')'
SET @SQL = @SQL + ' AND Q.brandid IN (0,'+ @BRANDID +')'
SET @SQL = @SQL + ' ORDER BY noticeid DESC'
SELECT @SQL
EXEC(@SQL)
ANY IDEA HOW TO PASS VALUES TO DYNAMIC SQL??
THANKS IN ADVANCE