declare the table variable - 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)

declare the table variable

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-01-28 : 05:07:18

Hi, I want to check either a table name exist in a linked server or not, I do in this way and it works well,

SELECT @TableValid = COUNT(*) FROM [SERVER1\DBTEST].[DBTEST].[dbo].[SYSOBJECTS] WHERE xtype = 'u' AND name = 'tableABC';

But the problem now is I do not want to hardcord the database name which is [SERVER1\DBTEST].[DBTEST].[dbo].[SYSOBJECTS], so I use this way,

SELECT @TableValid = COUNT(*) FROM @DBString WHERE xtype = 'u' AND name = 'tableABC';

the @DBString is set through passing parameter, but I get the error:

Msg 1087, Level 15, State 2, Procedure StorProc1, Line 34
Must declare the table variable "@DBString".

Please help....

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 05:22:07

you will have to have the entire sql called and run dynamically ..
have a look at this ..it should help

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-28 : 05:22:39

Have you tried declaring it locally. i.e withhin the stored procedure.
In terms , of what you are tring to get, the way to do it , is to use dynamic sql .

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-28 : 05:23:50

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 05:24:32

quote:
Originally posted by juicyapple

Hi, I want to check either a table name exist in a linked server or not, I do in this way and it works well,

SELECT @TableValid = COUNT(*) FROM [SERVER1\DBTEST].[DBTEST].[dbo].[SYSOBJECTS] WHERE xtype = 'u' AND name = 'tableABC';

But the problem now is I do not want to hardcord the database name which is [SERVER1\DBTEST].[DBTEST].[dbo].[SYSOBJECTS], so I use this way,

SELECT @TableValid = COUNT(*) FROM @DBString WHERE xtype = 'u' AND name = 'tableABC';

the @DBString is set through passing parameter, but I get the error:

Msg 1087, Level 15, State 2, Procedure StorProc1, Line 34
Must declare the table variable "@DBString".

Please help....

LOL... how did you manage to "HARDCORD" it by the way ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources