Temp Tables question

Question

Post reply

Temp Tables question

rjjh78

SSCrazy

Points: 2787

March 31, 2022 at 3:29 pm

#4008866

Is it possible to create a temp table when setting a query?

For example:

DECLARE 
@Category    varchar(100), 
@SubCategory varchar(100),
@Query       varchar(1000)

SET @Query = 'SELECT TestName FROM tblTest WHERE CategoryID = (CategoryID =' +  @CategoryID + ')  AND (SubCategory IN (' + @SubCategory + '))'

EXEC(@Query)

How could I place the results into a temp table?

Many thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246964 More actions · Answer 1

Here's an example

DECLARE @Query VARCHAR(1000) = 'SELECT TOP (10) object_id, name FROM sys.columns';

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
    object_id INT
   ,name NVARCHAR(128)
);

INSERT #SomeTab
(
    object_id
   ,name
)
EXEC (@Query);

SELECT *
FROM #SomeTab st;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

rjjh78 wrote:

Is it possible to create a temp table when setting a query?
For example:
DECLARE 
@Category    varchar(100), 
@SubCategory varchar(100),
@Query       varchar(1000)

SET @Query = 'SELECT TestName FROM tblTest WHERE CategoryID = (CategoryID =' +  @CategoryID + ')  AND (SubCategory IN (' + @SubCategory + '))'

EXEC(@Query)
How could I place the results into a temp table?
Many thanks in advance.

I see no reason to use Dynamic SQL in this case. In fact, what you've posted is actually dangerous code because it concatenates user character based variables into the dynamic SQL making it extremely susceptible to SQL-Injection.

Yep... I do realize it's "just" and example but other readers need to be warned and maybe you do to because I find that most examples are born from the limits of what someone knows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)