locked
insert into select prevent empty results RRS feed

  • Question

  • Hi All,

    Lets see if someone can help. I have the following into a stored procedure:

     set @QuerySQL='insert into #TablaTMP select [Dimension Value Code], [Dimension Code] from.....

     exec(@QuerySQL)

    So if the select statement returns no results (it is not NULL, its no results) It raises error.

    I tried @@rowcount  by doing a select and exec() before those but seems not working. Is there any sentence I can include into @QuerySQL variable?

    thanks!

               
    Monday, January 7, 2013 4:05 PM

Answers

  • What error do you get?

    Here is an example with no error:

    CREATE TABLE #TablaTMP (CategoryId int)
    CREATE TABLE tblProject (CategoryId int)
    
    --INSERT INTO tblProject VALUES (1),(NULL),(5)
    
    DECLARE @QuerySQL varchar(500)
    set @QuerySQL='insert into #TablaTMP select CategoryId from tblProject WHERE 1=2'
    
    exec(@QuerySQL)
    
    SELECT * FROM tblProject p
    DROP TABLE tblProject
    DROP TABLE #TablaTMP

    • Proposed as answer by Gert-Jan Strik Monday, January 7, 2013 7:42 PM
    • Marked as answer by Kalman Toth Friday, January 11, 2013 12:28 AM
    Monday, January 7, 2013 4:12 PM

All replies

  • It will not generate a Error. Can you post the error and the full SQL Statement you are forming?

    Monday, January 7, 2013 4:10 PM
  • What error do you get?

    Here is an example with no error:

    CREATE TABLE #TablaTMP (CategoryId int)
    CREATE TABLE tblProject (CategoryId int)
    
    --INSERT INTO tblProject VALUES (1),(NULL),(5)
    
    DECLARE @QuerySQL varchar(500)
    set @QuerySQL='insert into #TablaTMP select CategoryId from tblProject WHERE 1=2'
    
    exec(@QuerySQL)
    
    SELECT * FROM tblProject p
    DROP TABLE tblProject
    DROP TABLE #TablaTMP

    • Proposed as answer by Gert-Jan Strik Monday, January 7, 2013 7:42 PM
    • Marked as answer by Kalman Toth Friday, January 11, 2013 12:28 AM
    Monday, January 7, 2013 4:12 PM
  • sorry, seems like it was some other fetch over there, so this is not the problem.

    Monday, January 7, 2013 4:14 PM