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.

Creating SDF on desktop

I have read all posts in this forum, but the answers for the main questions are still not completely clear for me. My scenario is smilar than other described cases.

I would like to send a large amount of data to a Windows CE device which runs SQL Server Mobile Edition 2005. Replication is not possible, cause the desktop size of software sometimes uses Oracle or earlier versions of MSSQL Server (for e.g. MSSQL 7.0).

For syncrhonizing data we have two possibilities:

1. sending the data in some format (csv, xml, etc...) to the Windows CE device, parse this file and insert the data into a database, the speed of this is unacceptable, so if it is possible I wouldn't like to use this method

2. Create the SDF file on desktop side, it would be very fast, because it makes unnecessary the post processing on a slow Windows CE device. So please give me a short (YES / NO is completely enough) answer to this: is there any *LEGAL* way to create an SDF file for SQL Server Mobile Edition without buying VS2005 or MSSQL2005 licenses for the desktop computer?

Thx for any help in advance. If the question was answered clear earlier, sorry for wasting time. I think a lot of us struggling with this problem.

Your answer is YES: just download the SQL Compact Edition RC1 from Microsoft's website. You will get all the desktop bits that will allow you to create SDF files on the desktop although you will not be able to use it from within an IIS process.

Importing text files on the device is not a bad option per se, but using the Compact Framework to do it... My experience with OLE DB allows me to insert 150,000 rows in a fully indexed table in 7 minutes. This sometimes is the best approach because when updating a very large SDF file you have to factor in the tranfer times (desktop SDF generation time + time to transfer to device).

|||It is possible to use CE under IIS - see here in Steve Lasker's Blog

http://blogs.msdn.com/stevelasker/

I am surprised it takes as long as 7 minutes - this was the code we used to install 10,000 rows in 2 seconds

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=926404&SiteID=1|||The IIS stuff is good news. I will take a look at Steve's blog - thanks for the heads up. The 7 minutes involves converting data on the device from a zipped text file. The target table has 6 indexes, a PK and an FK and the target database resides on a CF card (on an iPAQ 2210).|||Hi, I think you are the same person who wrote me answers to the Primeworks Forum (http://www.primeworks-mobile.com/Forum/viewtopic.php?t=349) :-)) So thx once again your support, my problem is solved, and all I can say for everyone who would like to use SDF files on desktop size, use the Primeworks DesktopSqlCe component. It is worth to pay the price for this, you will gain a lot of time with avoid unnecessary research, investigation and finding/testing the good solution.