How to use value of a variable in defining data type

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_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

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_DATATYPE = (SELECT Primarykey_DATATYPE 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_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER

--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.

Please suggest

Thanks in Advance

Quote:

Originally Posted by 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_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

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_DATATYPE = (SELECT Primarykey_DATATYPE 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_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER


--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.


Please suggest

Thanks in Advance

Examine this line more closely...

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE

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

Jim :)