How can I use expressions as arguments to EXEC?

I am building some scripts and found an annoyance to be that I can't provide expressions as arguments to EXEC.

Here is an example I have run into. I want to setup various items using a consistent format that includes the database name. I can't simply concatenate the strings when passing an argument

EXEC msdb.dbo.sp_help_schedule
    @schedule_name = 'FullBackup_'+@DatabaseName,
    ...

instead I have to declare a variable just for the final string and pass that...

DECLARE @ScheduleName varchar(100)
SET @ScheduleName = 'FullBackup_'+@DatabaseName
EXEC msdb.dbo.sp_help_schedule
    @schedule_name = @ScheduleName,
    ...

I have built up a command string dynamically when necessary which could achieve this but would rather not to do that every time. Is this a limitation in t-sql or is there a way around it?

Yes, this is a limitation in T-SQL. There are several scenarios where you can't build up a string dynamically, but rather have to do so beforehand.

For example:

RAISERROR('String' + @variable,1,0);
EXEC dbo.procedure @param = 'String' + @variable;

There are other cases where it is valid, but usually just for assignment, not for passing or doing anything constructive with the result, e.g.

DECLARE @var VARCHAR(32) = 'String' + @variable;

T-SQL is just a quirky language with a lot of parsing peculiarities and odd rules. I think it's just the nature of a long-evolving language with different folks in charge of implementation at different times (made more obvious by their changing stance on adhering to things like the ANSI standard).

StackExchange Database Administrators Q#80652, answer score: 6