Create a SQL Server stored procedure that can run against multiple databases

Question

I have a SQL Server instance that contains many databases. These databases all have the same table structure. I'm creating a C# app to connect to this server and then run stored procedures.

I created a stored procedure in one of the databases and my C# code connects to and runs it just fine. My question is: can I create a stored procedure that I can pass the database name to and have it run?

My C# is fairly straightforward. I created the Helper class to build my connection string:

public List<ProcessRuns> GetRunId(string selectedDB, string runId)
{
    using (IDbConnection connection = new Microsoft.Data.SqlClient.SqlConnection(Helper.CnnVal(selectedDB)))
    {
        var output = connection.Query<ProcessRuns>("dbo.GetRun @RunId", new { RunId = runId }).ToList();
        return output;
    }
}

Currently I am passing in the database name selectedDB to connect to and the run id 'runId' to search for.

My stored procedure is straight forward:

USE [RP_Reserved_1]
GO
/****** Object:  StoredProcedure [dbo].[GetRun]    Script Date: 02/13/2025 14:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetRun] 
    @RunId char(36) 
AS
    SELECT
        RunId, RunDate, ActualDate, StopDate 
    FROM
        ProcessRuns 
    WHERE
        RunId = @RunId

I connected to a table in the master database and simply ran this query.

SELECT     
    RunId, RunDate, ActualDate, StopDate
FROM         
    RP_Reserved_1.dbo.ProcessRuns

It did go to the other database and return the data. That makes me think it is possible but I really have no clue how to make it work. I want to avoid going to every database and create the exact same stored procedure in it.

My intent is to create one stored procedure and then pass what database I want it to run against.

So something like this:

USE @SelectedDB
GO
/****** Object:  StoredProcedure [dbo].[GetRun]    Script Date: 02/13/2025 14:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetRun] 
    @RunId char(36) 
AS
    SELECT
        RunId, RunDate, ActualDate, StopDate 
    FROM
        ProcessRuns 
    WHERE 
        RunId = @RunId

Then I could have this C# code call it:

var output = connection.Query<ProcessRuns>("dbo.GetRun @SelectedDB, @RunId", new { SelectedDB = selectedDB, RunId = runId }).ToList();

Is this possible and if so how?

Answer 1

You could add the stored procedure to all databases, but that requires you to have change control in place that allows you to easily broadcast changes to all of the copies.

But if you need to loop to call against more than one, or any that aren't the current database context, you need some kind of dynamic control over where it gets called.

My preferred mechanism to do this is, well, I'm not sure what it's called, actually. But you can take advantage of the fact that EXEC takes a procedure name, and can accept a variable/parameter. So you can say up front "I want this to execute in the context of {database}," e.g.:

-- @RunId specified previously

DECLARE @database sysname = N'RP_Reserved_1';

IF DB_ID(@database) IS NOT NULL
BEGIN
  DECLARE @sql nvarchar(max) = N'
      SELECT RunId, RunDate, ActualDate, StopDate
        FROM dbo.ProcessRuns
         WHERE RunId = @RunId;',
          @exec nvarchar(512) = QUOTENAME(@database) + N'.sys.sp_executesql';

  EXEC @exec @sql, N'@RunId char(36)', @RunId;
--^^^^^^^^^^^^^^^
-- resolves to EXEC RP_Reserved_1.sys.sp_executesql @sql;
END

All the usual warnings about dynamic SQL always apply, of course.

Answer 2

The real answer for your situation - do not create a stored procedure. In situations like this, develop your system towards app-centric SQL processing. Multiple databases, multiple DB engine type etc will benefit from having your SQL code in repositories of your application.

You can replace stored procedures with prepared statements.

Call this from C#:

DECLARE @preparedId int;
EXEC sp_prepare @someInt OUTPUT,
    N'@prodId int',
    N'SELECT ProdKey, ProdName FROM Products WHERE prodId = @prodId;';
 
SELECT @preparedId;

And then use your prepared id

EXEC sp_execute 1, 10; -- where 1-prepared id and  10 is product id

-- destroy after use/app exiting

exec sp_unprepare 1;

Above is straight SQL but you can do it with C#. The only downside is that you prepare and you want to destroy. And your app and SQL Server are disconnected. If your app exits abruptly, there is no guarantee.

Or just execute SQL in code. You can have a special metadata DB or repository where these statements are stored and then only substitute connection.

Another thing, code like this example isn't even worth preparing, in the sense of performance gains. Only if this is some complex block that takes awhile to compile by the server. If you just have a select, just use parameterized query, which will be compiled and cached in the server buffer and every next use will be only value substitution.

Bottom line - don't do what you're doing.