Dynamic table name in a cursor?
-
I have a stored procedure in which I need to use dynamic column names and a dynamic table name in a cursor. Is there a way to do this? And, before you ask, yes I do have to walk the table rather than use a set based operation. And, yes I know it'll be slow, but I have no choice.
The reason is this:
The stored procedure will dynamically create a table which I then need to add one or many columns to (depending on user defined meta-data) and then I need to populate these columns with a calculation that is:
(ABS(ColumnA) - ABS(ColumnB)) * ColumnC.
The only problem I am having is determining how to walk the data while having a dynamic table name and dynamic column names.
Any help will be much appreciated.
A.J.
DBA with an attitude -
Have you thought about rotating the columns so they become rows?
e.g
Have a table UserTables(userid, tablename, columnname, value)
Then you wouldn't need to use dynamic SQL.
Otherwise, from what I remember (I hardly ever use dyamic SQL
) any tables you create inside a dynamic sql piece of code will not be available when the execute command returns, so you would have to construct the entire sql string, including the cursor walk through etc - you wont be able to walk through it in regular sql.Have a think about rotating the columns and see if you can come up with another, easier to write, easier to maintain, faster solution.
When you're performing your sums in the rotated version you can do a self-join to perform the calculations.
Dave Hilditch.
-
why not 'always create' the extra fields and use the likes of a CASE statement to conditionally fill them with the required data...based on the user profiles....if it turns out some of the columns end up empty....don't worry...it's only a small overhead...compared to the cost of the cursor operation.
-
As far as the dynamic table name, consider using a local temporary table unless you need the table to be available beyond the current connection scope. It gives you a unique access point with a constant name.
As far as table scope within dynamic SQL, physical tables are available from any batch within or after the create. In SQL Server, local temporary tables are available for the life of a connection from any batch within or after its creation. In SQL Server 2000, declared tables (local variables of type "table") are merely variables and only visible within current scope be it a single dynamic SQL batch or a single stored procedure excluding any dynamic SQL it executes.
The two solutions posed may work fine for you. I'm personally preferential to the "always create" approach. It removes the dynamic SQL and the cursor and due to what appears to be a large number of calculated columns avoids the massive join nightmare you would get from the columns as rows approach. If for some odd reason you can't tollerate having every possible column returned you could put the results with all possible columns into a temporary result set and then use as simpler dynamic SQL batch that selects out only certain columns from the temporary result set.
-
Thanks for all the suggestions, I think Grasshoper's suggestion will be a possibility.
1. Local Temp table will not work because of concurrency issues.
2. Always creating the "extra fields". This would be ideal if this stored procedure didn't have to run against anywhere from 1 - 50000 tables, each having any number of columns with any unique name possible
Thanks,
A.J.
DBA with an attitude -
Presumably there is a primary key that ties all these optional columns together?
Could you use something like this:
declare @cmd varchar(8000)
set @cmd = 'SELECT * INTO ' + <new table> + ' FROM
(SELECT pk FROM MainTable) a'
if <option1>
set @cmd = @cmd + ' LEFT JOIN (select pk, (abs(a) - abs(b)) * c AS Result1
FROM Table1) b ON a.pk = b.pk'
if <option2>
set @cmd = @cmd + ' LEFT JOIN (select pk, (abs(a) - abs(b)) * c AS Result2
FROM Table2) c ON a.pk = c.pk'
...
exec (@cmd)
Or maybe the solution is simpler and you're just adding optional fields to a query that uses a standard FROM clause.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply