1

When i write a dynamic sql like this?

set @dynamicSQL='select '+@logName+'= name from '+@databaseName+'.sys.sysfiles where groupid=0'

It can't work,but when i write like this:

select @logName=name from sys.sysfiles where groupid=0;

It can work properly,and like this:

set @dynamicSQL='select name from '+@databaseName+'.sys.sysfiles where groupid=0'

It can work normal.But why? i will approciate If someone tell me.

Dolphin
  • 29,069
  • 61
  • 260
  • 539
  • 1
    what is the value that u pass into @logName – Low Chee Mun Oct 08 '13 at 03:33
  • when a statement executes, it executes in it's own shell, which is separate to the shell which your script runs in; No communication between these shells is possible, unless it involves data stored in a table (ie one shell writes data into a table, which is then retrieved in another shell): Your first query is trying to retrieve information back from a sub-shell but this isnt possible because the output of that sub-shell cannot be retrieved back. Does that help? – John Bingham Oct 08 '13 at 03:41
  • For simply retrieving variables from dynamic queries, this thread may help: http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable –  Oct 08 '13 at 03:49

1 Answers1

2

The solution is like this:

set @dynamicSQL=N'select @logName= name from '+@databaseName+'.sys.database_files where type_desc=''LOG'''

exec sp_executesql @dynamicSQL,N'@logName nvarchar(100) output',@logName output

Then we can get name from dynamic statement.

Dolphin
  • 29,069
  • 61
  • 260
  • 539