How to use value of a variable in defining data ty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use value of a variable in defining data ty

Author

Topic

Avinash
Starting Member

5 Posts

Posted - 2007-12-13 : 21:43:47

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 03:54:41

You would need to do this in dynamic SQL as you can not set this at runtime. Alternitively, you may be able to set it as one datatype and CAST it to the required datatype. Not tried it, but would think it is possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources