current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
    
Did you mean @schedule_name = @ScheduleName,? –  Aaron Bertrand♦ 22 hours ago
    
yes, fixed, thanks –  Dan Roberts 21 hours ago
1  
Relevant connect item connect.microsoft.com/SQLServer/feedback/details/352110/… –  Martin Smith 20 hours ago

2 Answers 2

up vote 5 down vote

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).

share|improve this answer
    
Though in the first case you can do RAISERROR('String %s',1,0,@variable); –  Martin Smith 19 hours ago
    
@Martin sure, but not RAISERROR('String %s', 1, 0, @var1 + @var2); :-[) –  Aaron Bertrand♦ 19 hours ago
up vote 1 down vote

If you are just doing some maintenance scripts (not stored procedures) then you can do this type of thing using SQLCMD Mode ( Query > SQLCMD Mode ), eg

:setvar databaseName yourDbName

EXEC msdb.dbo.sp_help_schedule @schedule_name = 'FullBackup_$(databaseName)'

Variables have a batch scope and can even pass through to other scripts called with the :r command.

share|improve this answer
    
doing both scripts and stored procedures but will take a look, thanks –  Dan Roberts 1 hour ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.