How to parametrize the MAXDOP hint with a variable in SQL Server

Question

I would like to call a stored procedure with an integer variable @Mdop so that the query in the stored procedure reads something like:

SELECT someField 
FROM SomeTable 
OPTION (MAXDOP @Mdop)

Is there a way to do this? The only trivial way I found is having a giant IF THEN ELSE and, for each Maxdop value I plan to pass to the stored procedure, repeat the same query with different maxdop values. I find this approach original but quite horrifying.

Other ideas?

Answer 1

Build your query dynamically and then execute it.

Answer 2

You can use dynamic SQL like this:

DECLARE @MDOP1 INT = 1;
DECLARE @MDOP2 INT = 4;

DECLARE @SQLSTM1 NVARCHAR(4000) = 'SELECT someField  FROM SomeTable  OPTION (MAXDOP ' +  CAST(@Mdop1 AS NVARCHAR) + ')';
DECLARE @SQLSTM2 NVARCHAR(4000) = 'SELECT someField  FROM SomeTable  OPTION (MAXDOP ' +  CAST(@Mdop2 AS NVARCHAR) + ')';

EXEC sp_executesql @SQLSTM1;
EXEC sp_executesql @SQLSTM2;