May 18, 2006 at 6:46 am
Hey folks, I think I already know the answer to this one, but I'm hoping you may have another way of looking at this.
I'm creating some dynamic SQL that creates a record set which I would like to join to another record set in a final query.
This is what I’m trying to do…
SELECT t1.col1, t1.col2, t2.col3
FROM mytable t1 INNER JOIN (EXEC(@strSQL) t2 ON t1.ID=t2.ID
Is this possible? I can’t seem to find the correct syntax to make it work.
Thanks in advance.
May 18, 2006 at 7:00 am
Put all the sql in one sql string and EXEC that
or
use a temp table
CREATE TABLE #temp (col1, col2, col3)
INSERT INTO #temp (col1, col2, col3)
SELECT t1.col1, t1.col2, t2.col3 EXEC(@strSQL
SELECT t1.col1, t1.col2, t2.col3
FROM mytable t1 INNER JOIN #temp t2 ON t1.ID=t2.ID
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 7:37 am
Unfortunately the SQL String is too long to fit inside 1 Varchar(8000) variable. The statement has to have the ability to handle close to 16000 characters. That's why I broke it into smaller chunks.
I suppose I will have to use temp tables, but I thought when I included the SELECT INTO inside of my exec() statement that the temp table was only available to the scope of the exec() and wasn't there when Focus returned back to the main procedure. I am wrong?
Thanks.
-Luke.
May 18, 2006 at 7:45 am
You can EXEC more than 8000 chars by supplying more than one variable, eg
EXEC(sql1 + sql2 + sql3)
Why SLECT INTO ?
And why inside the EXEC ?
I just converted your query as is
can you post your whole query so that we can see the whole problem!
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 8:27 am
I guess this is all mute because I was able to acomplish it using Temp Tables, but I'd still be interested in any ideas on what I was doing wrong see below.
Thanks.-Luke.
I was using Select Into primarily because I'm lazy. Instead of writing out a Create Table statement, do a select into and be done with it. The issue I was having was that when I did use EXEC(@strSql+@strSQL2+@strSQL3) that I was ending up with Server: Msg 8155, Level 16, State 2 No column was specified for column 1 of 't1', even though I was using
Select @strSQL = 'SELECT col1 AS col1, col2 AS col2'
SELECT @strSQL = @strSQL + ' The rest of my code with a while loop and such'
SELECT @strSQL2 = ' More code and another loop to get the columns crosstabbed and in the correct order'
SELECT @strSQL3 = ' From tabe 1'
EXEC((@strSql+@strSQL2+@strSQL3)
SELECT t1.col1, t1.col2, t2.col3
FROM mytable t2
INNER JOIN (EXEC(@strSql+@strSQL2+@strSQL3)) t1
ON t1.ID=t2.ID
Server: Msg 8155, Level 16, State 2 Line 75 No column was specified for column 1 of 't1'
May 18, 2006 at 8:42 am
SET @strSQL1 = 'SELECT t1.col1, t1.col2, t2.col3 FROM mytable t2 INNER JOIN ('
SET @strSQL2 = 'SELECT col1 AS col1, col2 AS col2'
SET @strSQL3 = ''
...code here to add to strSQL3 with a while loop and such
SET @strSQL4 = ''
... code here to add to strSQL4 and another loop to get the columns crosstabbed and in the correct order
SET @strSQL5 = ' From tabe 1'
SET @strSQL6 = ') t1 ON t1.ID=t2.ID'
EXEC(@strSql1+@strSQL2+@strSQL3+@strSQL4+@strSQL5+@strSQL6)
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 8:52 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply