Mechanism in SQL Server that allows you to store a stored procedure in a table and call it after retrieving it from a select statement?

Question

Basically if I had some database tables set up like so:

Object State

Id | Description | State
---+-------------+------
1  | blah        | A
2  | blah blah   | A
3  | blurgh      | B

StateAction

Id | State | Action
---+-------+---------
1  |  A    | ActionA
2  |  B    | ActionB

Is there a native way (in T-SQL, SQL Server 2017+) to do something like

@proc ProcedureType --No idea if this is a thing

SELECT @proc = SA.Action
FROM ObjectState OS
JOIN StateAction SA ON OS.State = SA.State
WHERE OS.Id = 3

EXEC @proc

Basically, are there function pointer equivalents in SQL or function/procedure types? Are there any patterns for handling this sort of thing within a relational database context?

There is [dynamic SQL](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15)? Only recommended for use if you absolutely need to though. More of an anti-pattern than a pattern. — Dale K, Dec 10 '19 at 23:51
This is called *dynamic SQL*. It can be done, although it is often not the best approach to solving a problem (but sometimes is!). — Gordon Linoff, Dec 10 '19 at 23:51
I think the real problem here is that you're trying to make SQL work like a procedural language. It's not that. It's a declarative language. Have a look at [What is the difference between declarative and procedural programming paradigms?](https://stackoverflow.com/questions/1619834/what-is-the-difference-between-declarative-and-procedural-programming-paradigms). — Eric Brandt, Dec 11 '19 at 04:04

David Browne - Microsoft · Answer 1 · 2019-12-11T00:14:07.957

You can call a procedure whose name is stored in a TSQL variable, eg:

use tempdb

go
create procedure foo
as
begin
  select 1 a;
end
go

declare @procName sysname = 'foo';
exec @procName;

Which is not technically dynamic SQL, and is the closest thing to a function pointer SQL Server has, as the value of the variable has to be the name of an existing procedure.

Or you can use dynamic SQL, and execute an arbitrary string retrieved from wherever you want (notice the slightly-different syntax for exec).

declare @sql nvarchar(max) = 'select 1 a';
exec ( @sql );

And just to confuse matters, the dynamic SQL batch could, of course, invoke a stored procedure:

declare @sql nvarchar(max) = 'exec foo;';
exec ( @sql );

And it's even legal to omit exec if the stored procedure invocation is the only statement in the batch:

declare @sql nvarchar(max) = 'foo';
exec ( @sql );

But note that exec ( @sql ); is vulnerable to SQL Injection attacks, while exec @procName; is not.

Mechanism in SQL Server that allows you to store a stored procedure in a table and call it after retrieving it from a select statement?

1 Answers1