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.