Using a variable as a table name when opening a cursor
-
Hi guys,
I have trawled through a bunch of posts and I cant seem to find what I am after.
Simply put, I have a stored proc that has a varchar parameter passed which is the name of a table. I then want to use this table name variable to open a cursor to that table (basically, I have a few tables with the exact same structure but different names and I dont want to have multiple stored procs cause they all do the same thing just on different tables). Below is a snippet:
DECLARE c_PromoTransJoin CURSOR FOR
SELECT @StageTable.LEGACY_PATRON_ID FROM @StageTable
I have looked at exec and sp_ExecuteSql but I dont think these will help me? Can I exec a dynamic SQL string and have it return me a cursor I can use? I just cant see a way to do this.
-
Why do you need a cursor for this? If you converted the cursor to set based code, the dynamic SQL would work just fine...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ahh, sorry, that was just a cut-down snippet of the select statement. Theres about 20 columns in the table and I need to do row based transformations based on business rules. The reason for multiple tables is that there are 3 staging tables that hold imported data (beyond my scope) from flat files. I could just duplicate my stored proc 3 times and manually change the table name in each - but I hate that type of code-non-reuse, at the stored proc is about 700 lines of code. Surely there has to be a way of passing a table name param?!
-
In order to use table names or columns as a parameter in a query, you have to build a string and execute as dynamic sql. E.g.:
Declare @SQLStatement Varchar(100)
Declare @tablename varchar(20)
set @tablename='table1'
set @SQLStatement= 'select * from' + @tablename
execsql @SQLStatement
-
Did you think about having computed columns in your table with UDF as a formula?
This UDF may perform all calculations from your SP. And you supply values from your columns as parameters of this UDF.
No cursors necessary. Values are being recalculated immediately after the tables are updated.
Same code of UDF is used in all tables.
_____________
Code for TallyGenerator -
Heh... I sure do appreciate what that's all about... been there and done that... just not with a cursor so I have no idea if a cursor can be created using dynamic SQL. Serqiy has a pretty good alternative...
I also appreciate the fact that you might not have the time to convert a cursor in a 700 line proc to set based... but, if you do, take it from me and a bunch of other folks on this forum, you should. You will be VERY pleased with the results.
We'll be happy to help you with any hard spots you run into.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i do agree with the other guys here - set based is always best but sometimes you don't have a choice
the only way i can see you doing this is something like
create proc1 @tabname as
declare @strsql nvarchar(1000)
create table ##temptab (mycolumn int)
set @strsql='insert into ##temptab select mycolumn from '+@tabname'
exec @strsql
declare cursor curs1 for select * from ##temptab
......
MVDBA
-
Hey all,
Thanks for all your replies - much appreciated. As it turns out you can use dynamic SQL to exec a cursor declare statement and it works. Who woulda thunk.
The UDF is not a bad approach but for this job it just wasnt the best - I have to do quite complex on the table as a whole and apply business rules accross the DB... stored proc was determined the best approach so thats what I have been told to do.
I would be interested to learn more about the "set based" method you guys mentioned. Where can I read more about that?
Thanks again for all your help - much appreciated.
Oh - and just a little thing I found out, you need to use alias for temp var column names when joining on them. ie this works:
DECLARE c_PromoTransAgg CURSOR FOR
SELECT
a.LEGACY_PATRON_ID,
a.LEGACY_PROMOTION_TYPE,
a.LEGACY_PROMOTION_ID
b.LEGACY_PROMOTION_ID,
b.LEGACY_PROMOTION_TYPE,
FROM
@S_PROMOTION_JOIN a INNER JOIN @S_PROMOTION_AGG b ON
a.LEGACY_PROMOTION_ID = b.LEGACY_PROMOTION_ID
AND a.LEGACY_PROMOTION_TYPE = b.LEGACY_PROMOTION_TYPE
And this doesnt:
DECLARE c_PromoTransAgg CURSOR FOR
SELECT
@S_PROMOTION_JOIN.LEGACY_PATRON_ID,
@S_PROMOTION_JOIN.LEGACY_PROMOTION_TYPE,
@S_PROMOTION_JOIN.LEGACY_PROMOTION_ID,
@S_PROMOTION_AGG.LEGACY_PROMOTION_ID,
@S_PROMOTION_AGG.LEGACY_PROMOTION_TYPE,
FROM
@S_PROMOTION_JOIN INNER JOIN @S_PROMOTION_AGG ON
@S_PROMOTION_JOIN.LEGACY_PROMOTION_ID = @S_PROMOTION_AGG.LEGACY_PROMOTION_ID
AND @S_PROMOTION_JOIN.LEGACY_PROMOTION_TYPE = @S_PROMOTION_AGG.LEGACY_PROMOTION_TYPE
Strange huh. Couldnt find mention of this anywhere either. Just in case someone else had come accross this.
Cheers
Jordan
-
Declare a cursor variable. Pass the cursor variable as OUTPUT into sp_ExecuteSQL. The dynamic SQL should assign the cursor variable via SET and OPEN it before the batch completes.
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply