Dynamic strings & variables

[chenthorn]

Why can I not pass the results of a dynamic string query straight into a variable?
I have this query to find the max uid for a givern job run. I paramaterized the server name so that the table can be moved between servers for disaster recovery:
declare @destsvr varchar(30),
@file varchar(30),
@cmd varchar(300),
@marker Int

select @destsvr = '[PRD-005].Operations.DBO'
select @file = 'test1.txt'

select @cmd = ('select MAX(UID) FROM '+@destsvr+'.ProcessStatus Where SubApp = @File')
exec @cmd

This query qill return a UID that I want to assign to @Marker, thus allowing me to do updates later on in the process.
Other than inserting the results to a table, then assigning that value to my marker var, does anyone see any other way to get this uid out of the dynamic string and into a variable?

 

[MapMan]

A few small syntax changes will do the trick...

declare @destsvr    varchar(30),
    @file        varchar(30),
    @cmd        varchar(300),
    @marker        Int

select @destsvr = '[PRD-005].[Operations].[DBO]'
select @file = 'test1.txt'

select @cmd = 'select MAX(UID) FROM '+@destsvr+'.ProcessStatus Where SubApp = @File'
exec (@cmd)

HTH, MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[MapMan]

Oops,

Let's try that again...

declare @destsvr    varchar(30),
    @file        varchar(30),
    @cmd        varchar(300),
    @marker        Int

select @destsvr = '[PRD-005].[Operations].[DBO]'
select @file = 'test1.txt'

select @cmd = 'select MAX(UID) FROM '+@destsvr+'.ProcessStatus Where SubApp =

' +

@File
exec (@cmd)

HTH,

MapMan MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[MapMan]

Honest, this time it's right...

The four apostrophes in a row put the variable (@File) in the correct context in order to pass it to the string that's called by the execute (Exec) command.

I apologise for posting incorrect versions twice.

declare @destsvr    varchar(30),
        @file       varchar(30),
        @cmd        varchar(300),
        @marker     Int

select  @destsvr =  '[PRD-005].[Operations].[DBO]'
select  @file    =  'test1.txt'

select  @cmd     =  'select MAX(UID) FROM ' + @destsvr 
select  @cmd     =  @cmd + '.ProcessStatus Where SubApp = '
select  @cmd     =  @cmd + '''' + @FILE + ''''
exec (@cmd)

HTH, MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.