Ok I am buildin on an issue I solved a few weeks back about dynamically generating the
table name varible and using it in the FROM clause of an SQL statement. I am now
trying to use this method but also set a varible from the select statement. I keep
getting an error telling me I have to declare the varible I am trying to set
(@MaxLoopCounter) but it is in my declaration statement. Can eanyone assist me with
this or give me another approach? Here is the stored proc. SQL2000.
CREATE PROCEDURE Validate_Member_Counts
@month1 varchar(10),
@month2 varchar(10)
AS
DECLARE
@pid1 int,
@pid2 int,
@expdate1 datetime,
@joindate1 datetime,
@expdate2 datetime,
@joindate2 datetime,
@status varchar(10),
@counttype varchar(15),
@accttype varchar(1),
@loopid int,
@MaxLoopCounter int,
@SQL varchar(8000),
@TABLE_NAME1 sysname,
@TABLE_NAME2 sysname
--Create a holding table for results
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[DAILY_MBR_COUNTS]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE [DAILY_MBR_COUNTS]
(
personid int,
accttype varchar(1),
status varchar(10),
joindate datetime,
expdate datetime,
counttype varchar(15)
)
ELSE
TRUNCATE TABLE DAILY_MBR_COUNTS;
SELECT @TABLE_NAME1 = @month1+'persondem';
SELECT @TABLE_NAME2 = @month2+'persondem'
SET @loopid = 1;
SELECT @SQL =' SELECT @MaxLoopCounter = Max(Loopid) FROM ' + @TABLE_NAME1
EXEC(@SQL)
WHILE @LoopId <= @MaxLoopCounter
BEGIN
-- Do your magic here
SELECT @SQL ='@pid1 = personid, @expdate1,@joindate1 FROM ' + @TABLE_NAME1
+'WHERE loopid = @loopid';
EXEC(@SQL)
SET @LoopId = @LoopID + 1
END
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=36
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183072
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
