Dynamic SQL results into temp table in SQL Stored procedure

Question

The code is as follows    ALTER PROCEDURE dbo pdpd DynamicCall   SQLString varchar 4096    null  AS  Begin      create TABLE  T1   column 1 varchar 10    column 2 varchar 100         insert into  T1          execute   execute      SQLString        select   from  T1   End   The problem is that I want to call different procedures that can give back different columns  Therefore I would have to define the table  T1 generically  But I don t know how   Can anyone help me on this problem

User · Answer

INSERT INTO  TempTable EXEC  SelectStatement

User · Answer

CREATE PROCEDURE dbo pdpd DynamicCall  AS DECLARE  SQLString 2 NVARCHAR 4000  SET NOCOUNT ON Begin     --- Create global temp table     CREATE TABLE   T1   column 1 varchar 10    column 2 varchar 100         SELECT  SQLString 2    INSERT INTO   T1  column 1  column 2  SELECT column 1    123   column 2    MUHAMMAD IMRON       SELECT  SQLString 2   REPLACE  SQLString 2                  EXEC SP EXECUTESQL  SQLString 2      --- Test Display records     SELECT   FROM   T1      --- Drop global temp table      IF OBJECT ID  tempdb    T1   u   IS NOT NULL     DROP TABLE   T1 End

User · Answer

Be careful of a global temp table solution as this may fail if two users use the same routine at the same time as a global temp table can be seen by all users

User · Answer

Not sure if I understand well  but maybe you could form the CREATE statement inside a string  then execute that String  That way you could add as many columns as you want

User · Answer

create a global temp table with a GUID in the name dynamically  Then you can work with it in your code  via dyn sql  without worry that another process calling same sproc will use it  This is useful when you dont know what to expect from the underlying selected table each time it runs so you cannot created a temp table explicitly beforehand  ie - you need to use SELECT   INTO syntax  DECLARE  TmpGlobalTable varchar 255     SomeText     convert varchar 36  NEWID     -- select  TmpGlobalTable   -- build query     SET  Sql             SELECT   INTO         TmpGlobalTable      FROM SomeTable  EXEC   Sql  EXEC   SELECT   FROM         TmpGlobalTable         EXEC   DROP TABLE         TmpGlobalTable        PRINT  Dropped Table      TmpGlobalTable

User · Answer

DECLARE  EmpGroup INT  3            IsActive BIT 1  DECLARE  tblEmpMaster AS TABLE          EmpCode VARCHAR 20  EmpName VARCHAR 50  EmpAddress VARCHAR 500    INSERT INTO  tblEmpMaster EXECUTE SPGetEmpList  EmpGroup  IsActive  SELECT   FROM  tblEmpMaster

User · Answer

Try   SELECT into  T1 execute   execute      SQLString     And this smells real bad like an sql injection vulnerability     correction  per  CarpeDiem s comment    INSERT into  T1 execute   execute      SQLString     also  omit the  execute  if the sql string is something other than a procedure

User · Answer

You can define a table dynamically just as you are inserting into it dynamically  but the problem is with the scope of temp tables  For example  this code   DECLARE  sql varchar max  SET  sql    CREATE TABLE  T1  Col1 varchar 20    EXEC  sql  INSERT INTO  T1  Col1  VALUES   This will not work    SELECT   FROM  T1   will return with the error  Invalid object name   T1    This is because the temp table  T1 is created at a  lower level  than the block of executing code  In order to fix  use a global temp table   DECLARE  sql varchar max  SET  sql    CREATE TABLE   T1  Col1 varchar 20    EXEC  sql  INSERT INTO   T1  Col1  VALUES   This will work    SELECT   FROM   T1   Hope this helps  Jesse

[sql-server] Dynamic SQL results into temp table in SQL Stored procedure

Examples related to sql-server

Examples related to tsql

Examples related to dynamic-sql