2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Johannes Wentu
  • 931
  • 1
  • 14
  • 28
  • 1
    *Why* do you want to specify the MAXDOP like this? The number of cores on a machine isn't going to change from one query to another. It's not something that you should modify in code either, unless you are absolutely certain it's needed. Typically, it's the DBA that knows that, not the developer – Panagiotis Kanavos May 25 '17 at 07:54

2 Answers2

1

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;
S.Karras
  • 1,483
  • 15
  • 19
0

Build your query dynamically and then execute it.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
  • Thankx for your fast answer. Yes, it would definitely work but wouldn't I lose the benefits of having a stored procedure? I guess I can't have both, but I was hoping for a strange syntax or anything that allowed me to get the same result. – Johannes Wentu May 25 '17 at 07:50