local variable dissapears

[LeonAtRC]

When I run this code in Management Studio:

DECLARE @tbl TABLE 
	(
	PID int,
	Chip varchar(7)
	)
INSERT INTO @tbl SELECT Pid,Chip FROM Runners WHERE LiveUpdated = 0

DECLARE @LocalSvr varchar(25)
SET @LocalSvr = 'SQLSERVER.LocalLive.dbo.LiveRunners'

DELETE FROM @LocalSvr WHERE PID IN (SELECT PID FROM @tbl)

I get the message:
"Must declare the variable '@LocalSvr'." on the last line
(SQLSERVER is registered as a linked server.)

 

[onpnt]

umm...how can you delete from a varchar(25) variable?

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[gmmastros]

You'll need to use dynamic sql, which also means you'll need to change the table variable to a temp table.

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #tbl
    (
    PID [COLOR=blue]int[/color],
    Chip [COLOR=blue]varchar[/color](7)
    )
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #tbl [COLOR=blue]SELECT[/color] Pid,Chip [COLOR=blue]FROM[/color] Runners [COLOR=blue]WHERE[/color] LiveUpdated = 0

[COLOR=blue]DECLARE[/color] @LocalSvr [COLOR=blue]varchar[/color](500)
[COLOR=blue]Declare[/color] @SQL [COLOR=blue]varchar[/color](8000)

[COLOR=blue]SET[/color] @LocalSvr = [COLOR=red]'SQLSERVER.LocalLive.dbo.LiveRunners'[/color]
[COLOR=blue]Set[/color] @SQL = [COLOR=red]'DELETE FROM '[/color] + @LocalSvr + [COLOR=red]' WHERE PID IN (SELECT PID FROM #tbl)'[/color]

[COLOR=blue]Exec[/color] (@SQL)

-George

"the screen with the little boxes in the window." - Moron

 

[LeonAtRC]

The same way I'm gathering data from @tbl.

They are bgoth pointers to tables

 

[AlexCuse]

me thinks you need something like this for your delete:

DECLARE @LocalSvr varchar(25)
SET @LocalSvr = 'SQLSERVER.LocalLive.dbo.LiveRunners'
DECLARE @mySQL varchar(666)
set @mySQL = 'DELETE FROM ' + @LocalSvr + ' WHERE PID IN (SELECT PID FROM @tbl)'
EXECUTE(@mySQL)

(not tested)

I think that you will run into a scope issue with your table variable though. Why not just type it out?

DELETE FROM SQLSERVER.LocalLive.dbo.LiveRunners WHERE PID IN (SELECT PID FROM @tbl)

???????

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

You can't plop variables in the middle of SQL code and have the interpreter convert them to instructional statements. Variables are only allowed in positions where literals are allowed (except in the special case of table variables).

You can't do

DELETE FROM @LocalSvr WHERE PID IN (SELECT PID FROM @tbl)

any more than you can do

DELETE FROM 'SQLSERVER.LocalLive.dbo.LiveRunners' WHERE PID IN (SELECT PID FROM @tbl)

Here's an example that might help clarify the issue:

DECLARE @df varchar(100), @wpi varchar(100), @s varchar(100), @pf varchar(100)
SET @df = 'DELETE FROM'
SET @wpi = 'WHERE PID IN'
SET @s = 'SELECT'
SET @pf = 'PID FROM'

@df @LocalSvr @wpi (@s @pf @tbl)

That's just not going to work.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[LeonAtRC]

Thanks all for the feedback. I know it can be done but it will take just a bit more syntax research.

Although no one did answer my first question:

DECLARE @LocalSvr varchar(25)
SET @LocalSvr = 'SQLSERVER.LocalLive.dbo.LiveRunners'

DELETE FROM @LocalSvr WHERE PID IN (SELECT PID FROM @tbl)

Why, after declaring and using a declared variable does SQL tell me it doesn't exist?
As in: "Must declare the variable '@LocalSvr'."

 

[gmmastros]

Did you try my suggestion in the post dated (28 Jun 07 14:52)?

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

I think the error message you are getting is a bit misleading.

Obviously, you have an @LocalSvr variable. In this case, it is a scalar variable (Varchar 25). The syntax you show would be perfectly accepatible IF @LocalSvr was a table variable. The error message should probably be... "Deleting from tables can only be done with tables".

Since you are storing a string in the @LocalSvr variable, you must use dynamic SQL to do this.

Does this make sense now? If it doesn't I will explain more.

-George

"the screen with the little boxes in the window." - Moron

 

[gmmastros]

OK... Let me show you an example.

[COLOR=blue]DECLARE[/color] @LocalSvr [COLOR=blue]Table[/color](PID [COLOR=blue]int[/color])
[COLOR=blue]Declare[/color] @tbl [COLOR=blue]Table[/color](PID [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @LocalSvr [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @LocalSvr [COLOR=blue]Values[/color](2)

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @tbl [COLOR=blue]Values[/color](1)

[COLOR=blue]Select[/color] [COLOR=red]'Before'[/color], * [COLOR=blue]From[/color] @LocalSvr

[!]DELETE FROM @LocalSvr WHERE PID IN (SELECT PID FROM @tbl)[/!]

[COLOR=blue]Select[/color] [COLOR=red]'After'[/color], * [COLOR=blue]From[/color] @LocalSvr

See how (in this example) @LocalSvr is declared as a table? With this syntax, your original line works perfectly fine. As a varchar variable, it doesn't.

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

I'll translate the error message into something more clear:

"Must declare the variable '@LocalSvr'."

-->

"Cannot perform data operations against scalar variables. Must declare the variable '@LocalSvr' as a TABLE variable."



[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[LeonAtRC]

Got it folks!
Thanks George...you code Does work just fine.

By the way...this SP has to work with numerous table so that is what necessitates the use of variables.

Thanks again to all.

 

[dwilliams459]

Similar situation, I am getting the same error: Must declare the scalar variable "@Tag" on the last line. I think I understand why the following fails, but how do I work around it? "select @Tags.tag" for example would not evaluate. In my case, I want to join to a temp table. How do I do this? (The "Blog" table could be any table that contains a varchar(50) column.)

declare @Tags Table(Tag Varchar(50))
Insert into @Tags Values('tag1')
Insert into @Tags Values('tag2')
select Tag from @Tags left join Blog on
@Tags.Tag = Blog.Tag_vc

 

[dwilliams459]

After posting I see that adding a brackets around the temp table resolves the syntax issue. For example the following will return results (assuming the Temp table "@Tags" exists as declared previously):

select [@Tags].Tag From @Tags

 

[bborissov]

dwilliams459,
When you JOIN Table variables you ALWAYS must ALIAS them:

declare @Tags Table(Tag Varchar(50))
Insert into @Tags Values('tag1')
Insert into @Tags Values('tag2')

select Tag
        from @Tags [COLOR=red][b]Tags [/b][/color]
left join Blog on Tags.Tag = Blog.Tag_vc

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Actually bborissov, dwilliams459 taught us something new, that square brackets are also sufficient to the task, not just aliases (although aliases are better imo).

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]