We help IT Professionals succeed at work.

SQL Server Error while building a sql query using concatenate statements

85 Views
Last Modified: 2017-04-06
I am having a procedure to check if the values is already in the table column and if it is found, I am returning the primary key.

In my case I need to pass the table name to the procedure and hence I need to construct the SQL to be executed. I am getting the following error

Incorrect syntax near 'PARENT_KEYWORD_ID'.

Here is my stored procedure.

CREATE  PROCEDURE [dbo].[MultiList_Actions_CheckDuplicates]
(
      @NAME varchar(500)  ,
      @TABLE_NAME varchar(500) ,
      @PARENT_KEYWORD_ID int
)
AS SET NOCOUNT OFF;  
declare @sql nvarchar(4000)
SET @sql= 'SELECT CASE WHEN  b.ID  IS NOT NULL OR  b.ID <>'''' THEN b.ID ELSE 0 END TOTAL_COUNT FROM (SELECT 1 DUMMYCOL) a LEFT OUTER JOIN ' + @TABLE_NAME + ' b ON  b.NAME= ' + ''''+ CAST(@NAME  AS VARCHAR) +''''  + ' PARENT_KEYWORD_ID = ' + ''''+ convert(varchar(6),@PARENT_KEYWORD_ID)   +''''
exec sp_executesql @sql
Comment
Watch Question

Software Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Looks like a missing AND operator:

CREATE  PROCEDURE [dbo].[MultiList_Actions_CheckDuplicates] 
( 
      @NAME varchar(500)  ,
      @TABLE_NAME varchar(500) ,
      @PARENT_KEYWORD_ID int
) 
AS SET NOCOUNT OFF;  
declare @sql nvarchar(4000)
SET @sql= 'SELECT CASE WHEN  b.ID  IS NOT NULL OR  b.ID <>'''' THEN b.ID ELSE 0 END TOTAL_COUNT 
FROM (SELECT 1 DUMMYCOL) a 
LEFT OUTER JOIN ' + @TABLE_NAME + ' b ON  b.NAME= ' + ''''+ CAST(@NAME  AS VARCHAR) +''''  + ' AND PARENT_KEYWORD_ID = ' + ''''+ convert(varchar(6),@PARENT_KEYWORD_ID)   +''''
exec sp_executesql @sql

Open in new window

Author

Commented:
Thank you.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.