I think the problem is with the context of the executed dynamic sql string.
If you try something like this...
DECLARE
@max INT,
@tableName VARCHAR(20),
@sql VARCHAR(100)
SET @tableName = 'tblQuestions'
SET @sql = 'DECLARE @max INT SELECT @max = MAX(QuestionID) FROM ' +
@tableName
EXEC(@sql)
... you'll see there's no error about @max not being declared because it is
declared within the context of the SELECT @max statement. I think!
This doesn't give you the result you're after and I can't think of a way
around this, there might be a way to get the result from one context to the
current on but I'm not sure how.
Let us know if you figure out a way.
Ade
-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: 01 November 2004 22:38
To: CF-Talk
Subject: Stored Proc Help Needed Please
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183404
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
