September 4, 2006 at 6:56 am
DECLARE @sid Varchar(10)
SET @sid = '00260AMIT'
DECLARE @STR VARCHAR(1000)
SET @STR = ''
SET @STR = 'CREATE TABLE RD_'+ @sid +' (RESPID INT PRIMARY KEY, FLAG BIT)'
EXEC(@STR)
DECLARE @FIELDCODE INT, @FIELDNAME VARCHAR(20)
SET @STR = 'DECLARE CURTABLECREATION CURSOR LOCAL STATIC FORWARD_ONLY
FOR
SELECT DISTINCT CODE, DESCRIPT FROM UI_'+ @sid +' ORDER BY CODE
OPEN CURTABLECREATION
FETCH NEXT FROM CURTABLECREATION INTO @FIELDCODE, @FIELDNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = ''ALTER TABLE RD_'+ @SurveyID +' ADD ' + @FIELDNAME + ' TINYINT''
EXEC(@STR)
FETCH NEXT FROM CURTABLECREATION INTO @FIELDCODE, @FIELDNAME
END
CLOSE CURTABLECREATION
DEALLOCATE CURTABLECREATION'
I want to run this cursor. This is based on the table UI_00260AMIT and rd_00260AMIT. These tables are created just before this step. In fact you can see the code of RD_00260AMIT creation. Now my questions is how do I run a cursor that is based on the table that created dynamically.
please suggest.
September 4, 2006 at 7:01 am
You have to put all the code from the cursor declaration all the way to its deallocation in the dynamic sql, then run that in a single run. That's because the cursor will exists only in the scope of the exec statement.
Also may I ask why all this dynamic table creations?
September 4, 2006 at 7:18 am
Actually, I am enagged in the project in which I have to do this and this is the only way as far as I think. By this I can create a table(RD_00260AMIT) whose columns are depend on the record of other table (UI_00260AMIT).
Could you please explain me in brief.
thanks.
September 4, 2006 at 7:31 am
I'll happily show you how to do this, but I'd like to hear what you have to do exactly... maybe there's a better way to do this stuff than creating a bunch of stuff at run time...
September 4, 2006 at 7:36 am
i want to do this on runtime. coz i don't know the table name in advance. this will created at runtime only.
September 4, 2006 at 7:44 am
Exactly my point... Why do you think you need to do this? It is considered a worst practice (almost) to have to do something like this (there are rare exceptions). Can you explain me the whole process of this operation so that I understand what you have to do?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply