Run string without Execute - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Run string without Execute

Author

Topic

tomasjons
Starting Member

17 Posts

Posted - 2010-03-09 : 05:45:41

Hi!

I'm trying to do the following:

set @columns = 'field2 INT, field3 INT'

Set @sqlString = 'Create table #table1 field1 Int, ' + @columns + ')'

Execute(@sqlString)

So far so good....

But what I want to do is this: select * from #table1.
But of course, the scope is done and the temp #table1 is gone.
I know I can use global temp tables, but that is not an option for me.

What I want to do is to get this string to run without execute.
I want the string to generate the following syntax into the stored procedure: Create table #table1 field1 Int, field2 INT, field3 INT

So is it possible to get @sqlString to run without execute, much like a include file in HTML?

I know this is a long shot :)

Cheers.

Tomas

Cheers,
Tomas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 05:53:09

It is not possible
Why do you want to execute it dynamically?

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 05:58:01

there will be better ways to do whatever you are trying to do.

If you absolutely needed to you could create a temp table with a dummy column (or an autonumber column) and then use dynaamic SQL to Add columns to the table. That way you don't have to muck around with global temp tables.

But -- what are you actually trying to do? We'll be able to suggest an alternative way of doing it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tomasjons
Starting Member

17 Posts

Posted - 2010-03-09 : 06:08:30

Hi madhivana, thanks for replying

He he, I can understand why you wonder why I want to execute it dynamically :)

Here is the reason.
I have a table with several rows, and the rows include the name of the columns I want to use. I have used normal pivot function to get these as columns in temp table #Tomas2 (just a testname)
I have also got the correct data into that table with the matching columns. I do all this in a @sqlstreng and than execute it. Than I want to use the #Tomas2 temp table to join with another quyer later in the same procedure, but after execute the temp table is deleted (out of scope, of course).

Here is some of the code:

Declare @Tomas TABLE
(id Int IDENTITY(1,1), featPivotkol varchar(200))

INSERT INTO @Tomas SELECT featPivotkol FROM vgen_Hentkolonner order by featPivotkol DESC
--SELECT * FROM @Tomas

DECLARE @starter INT
DECLARE @Antallinjer INT
DECLARE @featPivotkol varchar(200);
DECLARE @sqlstreng varchar(8000);

SET @sqlstreng = ''
SET @starter = 1
SELECT @Antallinjer=max(id) from @Tomas
WHILE @starter <= @Antallinjer
BEGIN
SELECT @featPivotkol = featPivotkol FROM @Tomas WHERE id = @starter order by featPivotkol DESC
SET @sqlstreng = ',' + @featPivotkol + ' numeric(20, 4)' + @sqlstreng
SET @starter = @starter + 1;

END

SET @sqlstreng = 'CREATE TABLE #Tomas2 (bereID INT, feltID INT ' + @sqlstreng + ')'
+
'INSERT INTO #Tomas2 SELECT bereID, feltID, '+
@cols +'
FROM (
SELECT dbo.xxTemp.bereID, dbo.xxTemp.feltID, dbo.xxTemp.featPivotkol, dbo.xxTemp.verdi, dbo.xxTemp.moduID
FROM dbo.xxTemp INNER JOIN dbo.tblk_brukID_bereID ON dbo.xxTemp.bereID = dbo.tblk_brukID_bereID.bereID
WHERE dbo.tblk_brukID_bereID.brukID = 218 AND moduID <> 20

) p
PIVOT
(
MAX([verdi])
FOR featPivotkol IN
( '+
@cols +' )
) AS pvt'

+ ' print #Tomas2'

Execute(@sqlstreng)

--SELECT * FROM #Tomas2 (HERE THE TABLE IS OUT OF SCOPE)

Cheers,
Tomas

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-09 : 06:17:30

Ey, a fellow Norwegian! Velkommen kompis!

I gotta say that this method of fetching data is pretty obscure but I guess it has something to do with a EAV-system or something. Will this work for you...? ->

...
) p
PIVOT
(
MAX([verdi])
FOR featPivotkol IN
( '+
@cols +' )
) AS pvt; ' 
+ 'SELECT * FROM #Tomas2'

Execute(@sqlstreng)

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

tomasjons
Starting Member

17 Posts

Posted - 2010-03-09 : 07:25:12

Hi Lumbago, takk for det :)

I know that this method may seem somewhat strange, but I can't find another way to achieve what I want to do.

I have already tried what you are suggesting, but when I execute the @sqlstreng the table #Tomas2 is out of scope and I can't use it, unfortunately.
The problem is: I don't know the number, the name of the columns or the data I want to place in corresponding columns (EAV).

Summarize: I want to make a temp data table og table variable from the above, and later join the result in another query in the same procedure.

My procudre is running fine, but slow like this:
DECLARE @query varchar(8000)
SET @query = N'SELECT bereID, feltID, '+
@cols +'
FROM (
SELECT dbo.xxTemp.bereID, dbo.xxTemp.feltID, dbo.xxTemp.featPivotkol, dbo.xxTemp.verdi, dbo.xxTemp.moduID
FROM dbo.xxTemp INNER JOIN dbo.tblk_brukID_bereID ON dbo.xxTemp.bereID = dbo.tblk_brukID_bereID.bereID
WHERE dbo.tblk_brukID_bereID.brukID = ' + Convert(nvarchar,@brukID) +' AND moduID <> 20

) p
PIVOT
(
MAX([verdi])
FOR featPivotkol IN
( '+
@cols +' )
) AS pvt'--;'

where I later in the procedure join @query with another query in a loop (pivot x 1000 times). This takes some time to run for each row, so I want to create a table from @query and join that in the query in loop.... puhhh.

Cheers,
Tomas

tomasjons
Starting Member

17 Posts

Posted - 2010-03-09 : 07:29:38

forgot to mention that all this will run fine with ##table, that is...global temp table (wich is available outside the scope), but I am not sure if it smart to use global tables. Maybe I can, if I can generate global temp with dynamic table name.

Cheers,
Tomas

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-09 : 07:51:20

Maybe you should check out madhi's dynamic pivot instead...don't know the join possibilities but maybe it'll open some doors...?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

tomasjons
Starting Member

17 Posts

Posted - 2010-03-09 : 08:17:07

Where can i find the pivot? Is it not the same?

quote:
Originally posted by Lumbago

Maybe you should check out madhi's dynamic pivot instead...don't know the join possibilities but maybe it'll open some doors...?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Cheers,
Tomas

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 08:22:37

quote:
Originally posted by tomasjons

Where can i find the pivot? Is it not the same?

quote:
Originally posted by Lumbago

Maybe you should check out madhi's dynamic pivot instead...don't know the join possibilities but maybe it'll open some doors...?

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

please see
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Cheers,
Tomas

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-09 : 08:33:26

Crap, I forgot to add the link hehe...sorry about that! Haroon got it for me though:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

tomasjons
Starting Member

17 Posts

Posted - 2010-03-09 : 09:22:27

Thanks! I will try :) Will get back to you with my solution.

Cheers,
Tomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources