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.
| Author |
Topic |
|
miamikk
Starting Member
19 Posts |
 Posted - 2007-06-01 : 21:30:18
|
| I would like to know if I can create new variables using existing variables in a stored procedure.To be clear, in the SP I use, I pass table name. But I need the results from 2 other tables as well.The tables are named 1996, 1997, 1998.......2007.If I pass 2005 to SP, I need results from 2005, 2004 & 2003. How do I assign or get 2 new table names (variables) for 2004 & 2003 ??Part of code:ALTER Procedure [dbo].[XX]( @TblName1 varchar(20), @Month varchar(3))when I pass 2005 to SP, I need@TblName1 = 2005@TblName2 = 2004@TblName3 = 2003How can assign 2004 & 2003 to variables TblName2 & Tblname3 ??I really appreciate any help. |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-06-02 : 00:38:50
|
| Hi,U need to use dynamic sql to get the data. U have to declare the variables also dynamically, if they are changing with the year u pass, If u want only 2 tables every time u can declare the variable directly.For help on dynamic SQL u can check thishttp://www.sommarskog.se/dynamic_sql.html Peter |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-02 : 02:29:05
|
| <<The tables are named 1996, 1997, 1998.......2007.>>At least follow some naming conventionshttp://vyaskn.tripod.com/object_naming.htm Read Sommarskog's link, if you have doubts post hereMadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-02 : 02:30:30
|
| <<@TblName1 varchar(20),>>When you named all tables like 1996, 1998, etc, why did you use varchar datatype?MadhivananFailing to plan is Planning to fail |
|
|
|
miamikk
Starting Member
19 Posts |
Posted - 2007-06-02 : 11:40:28
|
| The tables are really not named 1995...2007. They are named 1995imp, 1996imp, 1997imp....2007imp. Each table contains data from each year.I can use case-switch statements to assign the 2 previous years table names depending on the selected table name. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-04 : 06:16:23
|
| Also, make sure that you read sommarskog's article fullyMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
creating variables in a Stored procedure