How to use value of a variable in defining data type

[aviansh]

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :


DECLARE @rowcount_maste rtable FLOAT
SET @rowcount_maste rtable = (select count(*) from master_table)

DECLARE @TABLE_ID float,
@TABLE_NAME varchar (100),
@primary_key varchar (100),
@Primarykey_DAT ATYPE varchar (50),

DECLARE @COUNTER FLOAT
SET @COUNTER = 1

WHILE (@Counter <= @rowcount_maste rtable)

Begin

SET @TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @COUNTER)
SET @primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @COUNTER)
SET @Primarykey_DAT ATYPE = (SELECT Primarykey_DATA TYPE FROM MASTER_TABL WHERE TABLE_ID = @COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @MAX_primary_ke y to fetch max of primary key from table name where table id is 1

DECLARE @MAX_primary_ke y @Primarykey_DAT ATYPE
SELECT @MAX_primary_ke y = MAX(@primary_ke y) FROM @TABLE_NAME
WHERE TABLE_ID = @COUNTER


---But by running it i am getting error that "Incorrect syntax near '@Primarykey_DA TATYPE'. and "Must declare the variable '@MAX_primary_k ey'.


Please suggest

Thanks in Advance

[Jim Doherty]

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :


DECLARE @rowcount_maste rtable FLOAT
SET @rowcount_maste rtable = (select count(*) from master_table)

DECLARE @TABLE_ID float,
@TABLE_NAME varchar (100),
@primary_key varchar (100),
@Primarykey_DAT ATYPE varchar (50),

DECLARE @COUNTER FLOAT
SET @COUNTER = 1

WHILE (@Counter <= @rowcount_maste rtable)

Begin

SET @TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @COUNTER)
SET @primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @COUNTER)
SET @Primarykey_DAT ATYPE = (SELECT Primarykey_DATA TYPE FROM MASTER_TABL WHERE TABLE_ID = @COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @MAX_primary_ke y to fetch max of primary key from table name where table id is 1

DECLARE @MAX_primary_ke y @Primarykey_DAT ATYPE
SELECT @MAX_primary_ke y = MAX(@primary_ke y) FROM @TABLE_NAME
WHERE TABLE_ID = @COUNTER


---But by running it i am getting error that "Incorrect syntax near '@Primarykey_DA TATYPE'. and "Must declare the variable '@MAX_primary_k ey'.


Please suggest

Thanks in Advance

Examine this line more closely...

DECLARE @MAX_primary_ke y @Primarykey_DAT ATYPE

at what point are you declaring either of these variable datatypes?

Jim :)