Sql-server – How to use expressions as arguments to EXEC

sql servert-sql

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?

Best Answer

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

Related Solutions

Sql-server – SQL Server 2008 and constant expressions

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant Functions to get a grasp what is 'constant' in SQL to start with.

See http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/ for an example when SQL Server does not do boolean short-circuit.

Sql-server – Safe alternative to exec(sql)

For certain types of parameterization, it's not going to matter if you use EXEC() or sp_executesql, because some things can't be parameterized anyway. For example, you expressed in the comments (please update your question to be more specific about your requirements!) that you are parameterizing table names, but these can't be parameterized because they need to expressed literally to SQL Server (it can't tokenize that and swap at runtime).

To protect yourself from table name vulnerabilities, you can easily protect yourself like this:

DECLARE @tablename sysname; -- pretend this is a parameter
SET @tablename = N'this_is_not_a_table';

-- in procedure body:
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @tablename)
BEGIN
  RAISERROR(N'Table does not exist.', 1, 11);
  RETURN;
END
SET @sql = N'SELECT ... FROM dbo.' + QUOTENAME(@tablename) + ...;
-- EXEC(@sql) or EXEC sys.sp_executesql @sql

Now, I prefer to use sp_executesql always, partly because it promotes using strongly-typed parameters (avoiding SQL injection issues as well as double-single-quote issues), but also because in some cases you will be passing parameters for both parameterizable and unparameterizable values (yes I made those words up). More info:

Now the only thing you have to worry about is if someone is able to create tables and can create a table named sys.objects; DROP TABLE foo; -- - but if you have someone you don't trust but has the ability to create tables in your database...

Best Answer

Related Solutions

Sql-server – SQL Server 2008 and constant expressions

Sql-server – Safe alternative to exec(sql)

Related Question