variable for table name - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

variable for table name

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2011-06-21 : 11:19:49

I need to set up a variable for a table name(F100). There is a table(SDE_layers) containing a list of all business tables and its ID(layer_ID). There are many tables named like 'F1, F2, ... F100, F101' which number after 'F' is same as number in Layer_ID column in SDE_Layers table.

When I run below code, @FTABLE has 'F100' but it error out at count statement like 'Incorrect syntax near '@FTABLE''

Please help.

DECLARE @F VARCHAR(1)
SET @F = 'F'

DECLARE @LAYERID VARCHAR(5)
SET @LAYERID = (SELECT LAYER_ID
FROM SDEWASHCOIN.SDE.SDE_layers
WHERE table_name = 'TAX_SHAPEF_VIEW')

DECLARE @FTABLE VARCHAR(5)
SET @FTABLE = @F + @LAYERID

PRINT @FTABLE

SELECT COUNT(*)
FROM COBGISSDE.sdeWashcoIN.GIS.@FTABLE

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 11:25:02

declare @sql varchar(1000)
select @sql = 'SELECT COUNT(*) FROM COBGISSDE.sdeWashcoIN.GIS.' + @FTABLE
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mikehjun
Starting Member

24 Posts

Posted - 2011-06-21 : 12:24:57

Thanks, it works like a charm.

I need to condition statement with the result of 'EXEC(@sql)'. How can I set variable for the result(integer)? I got an error at

SELECT @RESULT = EXEC(@COMP) Incorrect syntax near the keyword 'EXEC'.

######################
GO

DECLARE @F VARCHAR(1)
SET @F = 'F'

DECLARE @LAYERID1 VARCHAR(5)
SET @LAYERID1 = (SELECT LAYER_ID
FROM SDEWASHCOIN.SDE.SDE_layers
WHERE table_name = 'TAX_SHAPEF_VIEW')

DECLARE @LAYERID2 VARCHAR(5)
SET @LAYERID2 = (SELECT LAYER_ID
FROM SDEWASHCO.SDE.SDE_layers
WHERE table_name = 'TAX_SHAPEF_VIEW')

DECLARE @FTABLE1 VARCHAR(5)
SET @FTABLE1 = @F + @LAYERID1

DECLARE @FTABLE2 VARCHAR(5)
SET @FTABLE2 = @F + @LAYERID2

DECLARE @COMP VARCHAR(1000)

SELECT @COMP = 'SELECT COUNT(*)
FROM COBGISSDE.sdeWashco.GIS.' + @FTABLE2 + ' a INNER JOIN COBGISSDE.sdeWashcoIN.GIS.' + @FTABLE1 + ' b
ON a.fid = b.fid
WHERE a.area <> b.area'

DECLARE @RESULT INT
SELECT @RESULT = EXEC(@COMP)

IF @RESULT = 0
PRINT 'There is no changes'
ELSE
PRINT 'There are some changes'

Mikehjun
Starting Member

24 Posts

Posted - 2011-06-21 : 14:46:30

I figured out. here is the answer.

DECLARE @cmd NVARCHAR(500)
DECLARE @parm NVARCHAR(100)
DECLARE @number INT
DECLARE @number_out INT

SELECT @cmd = N'SELECT @number_out = COUNT(*)
FROM COBGISSDE.sdeWashco.GIS.' + @FTABLE2 + ' a INNER JOIN COBGISSDE.sdeWashcoIN.GIS.' + @FTABLE1 + ' b
ON a.fid = b.fid
WHERE a.area <> b.area'

SELECT @parm = N'@number_out INT OUTPUT'

EXECUTE sp_executesql @cmd,@parm,@number_out = @number OUTPUT

SELECT @number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources