CODEWITHSUNDEEP
sqlsql-serverdynamic-sql
Victor Ibasco
Victor Ibasco

Reputation: 11

Is there a way to pass in a variable into a stored procedure when using dynamic SQL?

DECLARE @DBNAME nvarchar(200) = 'MY_DB_NAME',
        @STOREDPROC nvarchar(200) = 'dbo.Friends_SelectById',
        @Id int = 2

EXEC [dbo].[Test_WithID_Proc]
                        @DBNAME,
                        @STOREDPROC,
                        @Id

DECLARE @SQL_QUERY NVARCHAR(4000)

SET @SQL_QUERY =  N'EXEC ' + @DBNAME + N'.' + @STOREDPROC + @Id

EXECUTE sp_executesql @SQL_QUERY, N'@Id int', @Id

This won't run of course but it's what I had in mind of dynamically passing in the variable that the stored procedure requires.

Context: I'm dynamically calling another DB with a particular stored procedure name. I've plans to call 100+ other DBs for testing purposes. Procedures that don't require any input I call the same way

Upvotes: 1

Views: 754

Answers (2)

Charlieface
Charlieface

Reputation: 71579

You don't actually need dynamic SQL at all here.

SQL Server supports using a variable for the procedure name in an EXEC. So you can do this:

CREATE OR ALTER PROCEDURE Test_WithID_Proc
  @DBNAME sysname,
  @SCHEMANAME sysname,
  @STOREDPROC sysname,
  @Id int
AS

DECLARE @ProcedureName nvarchar(776) = QUOTENAME(@DBName) + N'.' + QUOTENAME(@SCHEMANAME) + N'.' + QUOTENAME(@STOREDPROC);

EXEC @ProcedureName
  @Id = @Id;

GO

DECLARE @DBNAME sysname = N'MY_DB_NAME'
        ,@SCHEMANAME sysname = N'dbo'
        ,@STOREDPROC sysname = N'Friends_SelectById'
        ,@Id int = 2;


EXEC [dbo].[Test_WithID_Proc]
                        @DBNAME,
                        @STOREDPROC,
                        @SCHEMANAME,
                        @Id;

Upvotes: 2

Thom A
Thom A

Reputation: 95561

You're concatenating your variable's value, but then passing it as a parameter. Also the syntax for objects is {Database Name}.{Schema Name}.{Object Name}, you have omitted the schema's name.

I suspect you want something like this:

DECLARE @DBName sysname = N'YourDatabase',
        @ProcedureName sysname = N'YourProcedure',
        @Id int = 1;

DECLARE @SQL nvarchar(MAX) = N'EXEC ' + QUOTENAME(@DBName) + N'.dbo.' + QUOTENAME(@ProcedureName) + N' @Id;';

EXEC sys.sp_executesql @SQL, N'@Id int', @Id;

Upvotes: 0

Related Questions