my problem

[mani14]

I Need to be able to use this judgment inside a concrete datebase. It is to say:
-If I put it this way
DECLARE @indexid nvarchar(30)
declare @dbName nvarchar(30)
declare @table nvarchar(30)
set @table='poliza'
use master
EXEC sp_executesql N'use BDPD01 exec sp_MShelpindexPru @table, @indexid output',
N'@table nvarchar(30),@indexid nvarchar(30) OUTPUT',
@table,@indexid OUTPUT

print ''+@indexid+''

It works correctly.

But of this another form:

DECLARE @indexid nvarchar(30)
declare @dbName nvarchar(30)
declare @table nvarchar(30)
set @table='poliza'
set @dbName='BDPD01'
use master
EXEC sp_executesql N'use @dbName exec sp_MShelpindexPru @table, @indexid output',
N'@dbName nvarchar(30),@table nvarchar(30),@indexid nvarchar(30) OUTPUT',
@dbName,@table,@indexid OUTPUT
print 'hola'
print ''+@indexid+''

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@dbName'.

Which is the mistake?


Thank you very much, Regards.

[rmiao]

I think 'use' command needs db name instead of variable.

[mani14]

yes, but I need to work with the variable

[skhanal]

Try this

DECLARE @indexid nvarchar(30)
declare @dbName nvarchar(30)
declare @table nvarchar(30)
declare @sqlstr nvarchar(4000)

set @table='poliza'
set @dbName='BDPD01'
use master
set @sqlstr = N'use ' + cast(@dbName as nvarchar) +N' exec sp_MShelpindexPru @table, @indexid output'

EXEC sp_executesql @sqlstr,
N'@dbName nvarchar(30),@table nvarchar(30),@indexid nvarchar(30) OUTPUT',
@dbName,@table,@indexid OUTPUT
print 'hola'
print ''+@indexid+''