no data return - 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 2005 Forums

Transact-SQL (2005)

no data return

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-11 : 21:25:47

Hi, code below is used to check whether a table is existed in a database, but when I print @TableValid, it returns nothing...it should be an integer returned..Please advise.


DECLARE @ActiveSvrTemp VARCHAR(100);
DECLARE @ActiveDBName VARCHAR(100);
DECLARE @TableName VARCHAR(100);
DECLARE @nsql NVARCHAR(4000);
DECLARE @TableValid INT;
SET @ActiveSvrTemp = 'SERVER1';
SET @ActiveDBName = 'DB';
SET @TableName = 'tableA;

SET @nsql = 'SELECT @TableValid = COUNT(*) FROM ' + @ActiveSvrTemp + 
'.' + quotename(@ActiveDBName) + '.[dbo].[SYSOBJECTS] WHERE XTYPE = ''u''' + ' AND NAME = ''' + @TableName + '_temp'''

EXEC sp_executesql @nsql, N'@TableValid INT', @TableValid = @TableValid;

PRINT @TabelValid;

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 21:35:08

[code]EXEC sp_executesql @nsql, N'@TableValid INT OUTPUT', @TableValid = @TableValid OUTPUT[/code]

KH
[spoiler]Time is always against us[/spoiler]