Posts From May, 2013

Dynamically create a function or a stored procedure 

 

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.
 
Thx.
Alex Podlesny
Wednesday, May 8, 2013 3:38:00 PM
  • RSS
  • Add To My MSN
  • Add To Windows Live
  • Add To My Yahoo
  • Add To Google

Statistics

  • Entries (13)
  • Comments (0)