creating variables in a Stored procedure

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 = 2003

How can assign 2004 & 2003 to variables TblName2 & Tblname3 ?

I really appreciate any help.

look up either the SELECT statement or the SET statement in books online. But you seem to have an inconsistency in the way you are using the data. Your code has:

Code Snippet

ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

when I pass 2005 to SP, I need
@.TblName1 = 2005
@.TblName2 = 2004
@.TblName3 = 2003

But you seem to be subtracting 1 and 2 from parameter passed to the proedure. But if that is what you want, you need something like:

Code Snippet

ALTER Procedure [dbo].[XX](
@.TblName1 varchar(20),
@.Month varchar(3)
)

as

declare @.TblName2 varchar(20)

declare @.TblName3 varchar(20)

set @.TblName2 = convert(integer, @.TblName1) - 1

set @.TblName3 = convert(integer, @.TblName1) - 2

But a big problem now is that if you pass a string in for you table name such as 'aTable'

you will get execution errors.

You can avoid this by use of IF statements such as:

Code Snippet

IF isNumeric(@.TblName1) = 1

select @.TblName2 = convert(integer, @.TblName1) - 1,

@.TblName3 = convert(integer, @.TblName2)

Now if that isn't enough, there are times (actually, many times) in which the "IsNumeric" function will not correctly assess whether or not a string is numeric. There is an article that talks about these particular problems here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Look for the "IsReallyNumeric" funtion at this site. That should be enough to keep you going for at least a little while.

Kent

|||

Miamik,

This has all the markings of a very unwieldy design. I would not consider having identical tables named as this seems to indicate JUST as a method to partition data.

You would be better served to explore table partitioning in Books Online. Then your code would be so-o-o-o much easier, and you wouldn't have to resort to using dynamic SQL (with all of its' issues and security problems.)

|||I think Arnie is definitely telling it like it is.|||Thanks for all your replies.