Dynamic SQL and Adding Columns to a Table Var

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)

Dynamic SQL and Adding Columns to a Table Var

Author

Topic

AsherSQL
Starting Member

3 Posts

Posted - 2010-01-20 : 15:59:10

I am attempting to write a query which will, effectively, run the same query 9 times (with a slightly different filter each time(called a 'bucket' which is basically a string representing a sales category)) and then take those query results and load them into a 'Results' table variable 4 columns at a time; the end goal (which I have yet to implement) is to export this 'results' table to a text file that can be imported into excel for easy reporting.

Essentially I'm trying to create a table with the 'same' 4 columns iterated 8 times (one for the results of each filter) with a concatenated name.

Here's the code (with some comments so you all can have some clue as to what I am attempting to accomplish) containing the problem:

SET @Query =
N'ALTER TABLE @Results
--physically adds the columns to the table
ADD
WEEKS' +@Bucket+ ' INT,
TotaledOrders' +@Bucket+ ' INT,
Total' +@Bucket+ ' INT,
Bucket' +@Bucket+ ' VARCHAR(50)
ADD CONSTRAINT DefaultRule' +@Bucket+ '
DEFAULT WEEKS FOR WEEKS' +@Bucket+'
INSERT INTO @Results
(TotaledOrders'+@Bucket+', Total'+@Bucket+', Weeks'+@Bucket+', Bucket'+@Bucket+')
--fills the new columns with the following query
SELECT DISTINCT bw.TotaledOrders, bw.Total, bw.Weeks, @Bucket AS Bucket'+@Bucket+'
FROM @ByWeek bw

INNER JOIN @FilteredOrders fol
ON bw.Weeks = ((DATEDIFF(day, '+@startdate+', fol.orderdate)/ 7)+1)
INNER JOIN @BucketsList as bl
ON fol.orderID = bl.orderID
WHERE bl.Bucket = @Bucket'
--This is where the block of SQL, @Query, ends

EXEC sp_executesql @Query,
N'@Results AS TABLE, @Bucket VARCHAR(255), @StartDate VARCHAR(255)',
@Results = @Results,
@BucketIN = @Bucket,
@StartDateIN = @StartDate
--This is where I attempt to use the sproc to execute the dynamically created statement
END
SET @BucketCounter = @BucketCounter +1
--the above block of code is actually part of a loop
END

This produces a long slew of errors, which are, in fact, the same 4 errors repeated for each iteration of the loop. They are:
Msg 156, Level 15, State 1, Line 1 (is actually referring to the first line of @Query)
Incorrect syntax near the keyword 'TABLE'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'WEEKSExcluded'. (Line 4 of @Query)
Msg 1087, Level 15, State 2, Line 16
Must declare the table variable "@Results". (Ubiquitous)
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@Bucket". (Ubiquitous)
Msg 156, Level 15, State 1, Line 1

Now, I know my EXEC sp_executesql call is woefully mangled, and I have spent the last couple hours troubleshooting it myself from Microsoft's online help, but I just can't figure out how to get sp_executesql to see the table variables or other scalar variables I try passing to it. The source of the problem is I cant seem to figure out the proper syntax for passing variables to sp_executesql. This piece is actually just the very end of a rather long query (for me, anyways), which I can provide the rest of if it would be of further assistance. What am I doing wrong?

Furthermore, is what I am trying to do even possible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources