Discussion between Biju jose and Mr.Brownstone

Feeds

09:42

A: Creating temporary table dynamically using condition

You are thinking about the problem the wrong way. You can use dynamic SQL to do this - by defining your result shape within the statement, but creating the temporary table outside of it. Like so: CREATE PROC test @var1 CHAR(1) as BEGIN -- USING dynamic sql DECLARE @sql VARCHAR(MAX) IF(@var1 =...

Biju jose

this doesn't create the #result table dynamically right, we have to declare it beforehand.

Mr.Brownstone

No, you don't need to declare it beforehand, it'll get created when the statement executes.

Biju jose

I did test your code and it's throwing the error. Am I missing something?

Mr.Brownstone

What error is it throwing?

Biju jose

,Msg 208, Level 16, State 0, Procedure dbo.test, Line 67 Invalid object name '#result'.

Mr.Brownstone

09:42

Thanks, updated my answer for you.

Biju jose

hey

we are still back on the same page

Mr.Brownstone

How so?

Biju jose

ALTER PROC test @var1 CHAR(1)
as
BEGIN
-- USING dynamic sql
DECLARE @sql VARCHAR(MAX)
IF(@var1 = 'X')
BEGIN
SELECT *
INTO #results
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;', -- replace with actual server/instance
'SELECT * FROM sys.tables'); -- replace with query
END
IF(@var1 = 'Y')
BEGIN
SELECT *
INTO #results
FROM OPENROWSET(
'SQLNCLI',
'Server=(local);Trusted_Connection=yes;', -- replace with actual server/instance
'SELECT * FROM sys.tables'); -- replace with query

the script will be somthing like this right

the SELECT *
INTO #results is not allowed

having a create table multiple times

my workaround was to create a global temp table

but that have an issue of concurrency

Mr.Brownstone

You are correct. Which means that what you want to achieve cannot be done. You must either use a global temporary table or refactor your code to work around the restrictions placed upon you by the system.

One moment, I may have an idea

No, what I wanted to do wont work either.

Biju jose

10:11

yeah

anyway thanks

for helping out

next day → last day (18 days later) »

all rooms