none
Dynamic SQL Query RRS feed

  • Question

  • Hello,

    I have a sql code below that I want to work. What I am trying to do is get values from the table and use a while loop to insert into temp table  with some conditions

      



      DECLARE 	@table_name nvarchar(max) = 'table1'
    	DECLARE @schema nvarchar(max) = 'schema1'
    Declare @column_name TABLE ( columnid int identity,columnname varchar(max))
     INSERT INTO @column_name(columnname) VALUES ( 'Asset_Lifecycle_Status'),('AuditRule'),('CI_Name'),('Environment'),('FirstDate_Failed'),('LoadDateTime'),('OSVersion'),('Risk_Level'),('RiskScore'),('Rule_Status'),('Days_Not_Compliant');
    
    create table #temp (id int identity ,columnname nvarchar(max),valuess nvarchar(max))
    
    
    Declare @cnt int = 1
    
    While @cnt <= (select count(columnid) from @column_name)
    BEGIN
    DECLARE @COLUMN NVARCHAR(MAX) 
    SET @COLUMN =  (SELECT  COLUMNNAME  FROM @column_name  where columnid = @cnt)
    declare @rule1 varchar(500)
    SELECT @RULE1 = 'Insert into #temp(columnname,valuess) VALUES ('+@column+'),(SELECT ' + @COLUMN + ' FROM '+ @schema +'.' + @table_name + ' Where ' + @COLUMN + ' is null);'
    EXEC(@rule1)
    SET @CNT = @CNT+1;
    
    end ;
    
    DROP TABLE #TEMP
    
    
    SELECT * FROM #TEMP

    When I try to run this code, I get an error - 

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'SELECT'.

    Please help.

    Tuesday, June 25, 2019 8:28 PM

Answers

  • Hi AmitSSRS,

    Here's a tip:

    If you don't know where's the issue, you can try to print it.

    In following script, I create ‘table1’ and insert some sample data to test. You can see below code for your reference. As I'm not sure about your outcome, you can offer your final output you want if this cannot meet your requirement.

    IF OBJECT_ID('table1') IS NOT NULL drop table  table1
    go 
    create table table1(
    id int,
    Asset_Lifecycle_Status varchar(20),
    AuditRule varchar(20),
    CI_Name varchar(20),
    Environment varchar(20),
    FirstDate_Failed varchar(20),
    LoadDateTime varchar(20),
    OSVersion varchar(20),
    Risk_Level varchar(20),
    RiskScore varchar(20),
    Rule_Status varchar(20),
    Days_Not_Compliant varchar(20)) 
    insert into table1 values (1,'a','b','c','d','e','f',null,'h','i','j',null)
    go
     
    DECLARE @table_name nvarchar(max) = 'table1'
    DECLARE @schema nvarchar(max) = 'schema1'
    Declare @column_name TABLE ( columnid int identity,columnname varchar(max))
    INSERT INTO @column_name(columnname) VALUES ( 'Asset_Lifecycle_Status'),('AuditRule'),('CI_Name'),('Environment'),('FirstDate_Failed'),('LoadDateTime'),('OSVersion'),('Risk_Level'),('RiskScore'),('Rule_Status'),('Days_Not_Compliant');
     
    create table #temp (id int identity ,columnname nvarchar(max),valuess nvarchar(max))
     
    Declare @cnt int = 1
    declare @rule1 varchar(500)
    DECLARE @COLUMN NVARCHAR(MAX)
     
    While @cnt <= (select count(columnid) from @column_name)
    BEGIN
    SET @COLUMN =  (SELECT  COLUMNNAME  FROM @column_name  where columnid = @cnt)
    SELECT @RULE1 = 'Insert into #temp(columnname,valuess) 
    SELECT ''' + @COLUMN + ''', ' + @COLUMN + ' FROM '+ @schema +'.' + @table_name + ' Where ' + @COLUMN + ' is null;'
    ------print (@rule1)
    EXEC(@rule1)
    SET @CNT = @CNT+1;
    end ;
    SELECT * FROM #TEMP
    DROP TABLE #TEMP
    /*
    id          columnname            valuess
    ----------- --------------------------------------
    1           OSVersion             NULL
    2           Days_Not_Compliant    NULL
    */

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AmitSSRS Wednesday, June 26, 2019 7:58 PM
    Wednesday, June 26, 2019 9:12 AM

All replies

  • Every time you work with dynamic SQL, you should always have this line in your code:

    IF @debug = 1
       PRINT @sql

    And I mean ALWAYS!

    If you don't see the code you have generated, how would you be able to spot the error?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 25, 2019 9:58 PM
  • Hi AmitSSRS,

    Here's a tip:

    If you don't know where's the issue, you can try to print it.

    In following script, I create ‘table1’ and insert some sample data to test. You can see below code for your reference. As I'm not sure about your outcome, you can offer your final output you want if this cannot meet your requirement.

    IF OBJECT_ID('table1') IS NOT NULL drop table  table1
    go 
    create table table1(
    id int,
    Asset_Lifecycle_Status varchar(20),
    AuditRule varchar(20),
    CI_Name varchar(20),
    Environment varchar(20),
    FirstDate_Failed varchar(20),
    LoadDateTime varchar(20),
    OSVersion varchar(20),
    Risk_Level varchar(20),
    RiskScore varchar(20),
    Rule_Status varchar(20),
    Days_Not_Compliant varchar(20)) 
    insert into table1 values (1,'a','b','c','d','e','f',null,'h','i','j',null)
    go
     
    DECLARE @table_name nvarchar(max) = 'table1'
    DECLARE @schema nvarchar(max) = 'schema1'
    Declare @column_name TABLE ( columnid int identity,columnname varchar(max))
    INSERT INTO @column_name(columnname) VALUES ( 'Asset_Lifecycle_Status'),('AuditRule'),('CI_Name'),('Environment'),('FirstDate_Failed'),('LoadDateTime'),('OSVersion'),('Risk_Level'),('RiskScore'),('Rule_Status'),('Days_Not_Compliant');
     
    create table #temp (id int identity ,columnname nvarchar(max),valuess nvarchar(max))
     
    Declare @cnt int = 1
    declare @rule1 varchar(500)
    DECLARE @COLUMN NVARCHAR(MAX)
     
    While @cnt <= (select count(columnid) from @column_name)
    BEGIN
    SET @COLUMN =  (SELECT  COLUMNNAME  FROM @column_name  where columnid = @cnt)
    SELECT @RULE1 = 'Insert into #temp(columnname,valuess) 
    SELECT ''' + @COLUMN + ''', ' + @COLUMN + ' FROM '+ @schema +'.' + @table_name + ' Where ' + @COLUMN + ' is null;'
    ------print (@rule1)
    EXEC(@rule1)
    SET @CNT = @CNT+1;
    end ;
    SELECT * FROM #TEMP
    DROP TABLE #TEMP
    /*
    id          columnname            valuess
    ----------- --------------------------------------
    1           OSVersion             NULL
    2           Days_Not_Compliant    NULL
    */

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AmitSSRS Wednesday, June 26, 2019 7:58 PM
    Wednesday, June 26, 2019 9:12 AM