Dynamically create a function or a stored procedure

8 May

Dynamically create a function or a stored procedure in “some-other“ database from within the stored procedure running in “another“ database.

Note: for the time being, we will forget about possible security issues caused by dynamic SQL.

First of all we all know that CREATE or ALTER procedure clauses do not allow specifying the database name as a prefix to the object name, for instance if you will try to run following statement you will get an error:

CREATE PROCEDURE A.dbo.sp_bar
AS
BEGIN
SELECT 'a'
END;

That brings us to the usual way of setting database and then executing CREATE statement in the script, like this:

USE A
GO
CREATE PROCEDURE dbo.sp_bar
AS
BEGIN
SELECT 'a'
END
GO

Don’t forget about “GO” either, because CREATE or ALTER procedure must be the first statement in a query batch. That in fact itself, brings a little challenge when trying to dynamically create a function or a stored procedure in “A database from within the stored procedure running in “B” database, since sp_executesql does not accept “GO”… if you will try following code:

DECLARE @sql NVARCHAR(100) = 'USE A GO'
EXEC sp_executesql @sql

or even this one

DECLARE @sql NVARCHAR(100)
SET @sql = 'USE A
GO'
EXEC sp_executesql @sql

both will return “Incorrect syntax near ‘GO’” error message

So, to get around of all those limitations, create a little stored procedure in the target database (assuming that you have control over database and that you have thought about all the security concerns, and you have limited all access to such stored procedure) that will look like this:

USE A
GO
CREATE PROCEDURE dbo.sp_executesql_inTHISDatabase
(
@sql NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_executesql @sql;
END
GO

All we need now is to do what we originally intended

USE B
GO
DECLARE @sql NVARCHAR(800)
SET @sql=
'CREATE PROCEDURE dbo.sp_bar
AS
BEGIN
SELECT ''a''
END;
';
EXEC A.dbo.sp_executesql_inTHISDatabase @sql;

Hurray! It works.

But now… please do remember all those security concerns we were trying to forget for a little while.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: