1

I'd like to do the following in T-SQL:

EXEC('... ' + (SELECT ...))

A simple concrete example would be

EXEC('DROP TABLE ' + (SELECT name FROM sys.tables WHERE ...))

(Obviously, the WHERE clause is chosen such that the subquery always returns exactly one value.) When I try this, I get an error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

I know that I can work around this by using variables, e.g.:

DECLARE @sql varchar(MAX)
SELECT @sql = 'DROP TABLE ' + name FROM sys.tables WHERE ...
EXEC(@sql)

but for various reasons I'd like to have only one statement. Is that possible?

PS: In case it's relevant, that's the dynamic SQL code I'm trying to squeeze into one statement:

Community
  • 1
  • 1
Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • 1
    What are the "various reasons"? If you explain the limits you have to work within you may get alternative solutions. – Pondlife Oct 24 '12 at 14:32
  • @Pondlife: I'm not looking for an alternative solution, I'm asking whether one particular solution is possible or not. "No, it's not possible" is a perfectly valid answer. :-) That being said, in my concrete situation it's more of a preference ("I'd like to") than a need ("I need to"). I output a large list of SQL statements and I'd like to keep them "independent" of each other, for easier handling and manipulation. Declaring and filling variables adds dependencies which I'd like to avoid *if easily possible*. – Heinzi Oct 24 '12 at 14:39
  • I understand, but "squeezing" SQL code into a one-liner or single statement is almost never useful or desirable so I think it's a reasonable question to ask why you need to do it. My usual answer is put it in a stored procedure and call the stored procedure, which is always a one-liner but without knowing why or how you need to do this, it may not help. – Pondlife Oct 24 '12 at 14:42
  • @Pondlife: I understand, and I've extended my comment to provide a more detailed explanation. I'm comparing database structures and (programmatically) create one-time throwaway SQL scripts to "unify" them. Using variables and other things in them means that I have to be more careful (don't declare the same variable twice in the same script, etc.) – Heinzi Oct 24 '12 at 14:46

1 Answers1

1

You can't do treatment on the value that you pass to the EXEC function.

Thus, you can't concatenate or otherwise transform the value, it as to be done prior to the call.

Thus the answer is, no you can't.

But like Pondlife said, if you give the reason why you want to do it this way, it will be possible to find a more satisfying answer.

Yan Brunet
  • 4,727
  • 2
  • 25
  • 35