Using MAXRECURSION hint with sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using MAXRECURSION hint with sp_executesql

Author

Topic

mobrien118
Starting Member

5 Posts

Posted - 2013-07-17 : 15:04:40

Hello,

I'm trying to do this (where the "SA_Gen_After_Hours" UDF contains a CTE):

DECLARE @CALCULATION nvarchar(MAX)
DECLARE @Result int
DECLARE @start_time datetime = '7/17/2012'
DECLARE @end_time datetime = '7/17/2013'
DECLARE @Expected int

set @CALCULATION = 'SET @Result = (select count(*) from SA_Gen_After_Hours(@start_time,@end_time))'

EXECUTE sp_executesql @CALCULATION,N'@Start_Time datetime,@End_Time datetime,@Result float OUTPUT',@start_time=@start_time,@end_time=@end_time,@Result=@Expected OUTPUT --OPTION (MAXRECURSION 0)

but, obviously, get "Msg 530, Level 16, State 1, Line 1; The statement terminated. The maximum recursion 100 has been exhausted before statement completion." whenever I run it for more than 100 recursions.

I have tried putting the " OPTION (MAXRECURSION 0)" after the last line, inside of the @CALCULATION as well as a number of other locations.

Is there any way to use hints with sp_executesql?

(In reality, my case is much more complicated. I'm using sp_executesql to run retrieved that sets a value for a scorecard.)

Thanks!

--mobrien118

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-17 : 15:57:49

You should be able to add the hint at the very end of the dynamic SQL. See this example below:

DECLARE @n INT = 200;
DECLARE @m INT = 0;
DECLARE @sql NVARCHAR(4000) = '
;WITH cte AS 
( SELECT 10 AS n UNION ALL SELECT n+1 FROM cte WHERE n < 200) 
SELECT @Count = count(*) FROM cte OPTION (MAXRECURSION 0);';

EXEC sp_executesql @sql, N'@Recursions INT, @Count INT OUTPUT',@Recursions=@n,@Count = @m OUTPUT;

SELECT @m;

However, if your SA_Gen_After_Hours function itself has dynamic SQL in there which is recursive, then you will need to open up that function and do a similar thing to that dynamic SQL.

mobrien118
Starting Member

5 Posts

Posted - 2013-07-17 : 16:34:20

Continuing with my example, both of the following result in a "Msg 156, Level 15, State 1, Line 1;Incorrect syntax near the keyword 'OPTION'."

set @CALCULATION = 'SET @Result = (select count(*) from SA_Gen_After_Hours(@start_time,@end_time))  OPTION (MAXRECURSION 0)'

and

set @CALCULATION = 'SET @Result = (select count(*) from SA_Gen_After_Hours(@start_time,@end_time)  OPTION (MAXRECURSION 0))'

I'm trying to figure out why your example works and mine doesn't.

--mobrien118

mobrien118
Starting Member

5 Posts

Posted - 2013-07-17 : 17:06:34

AHA!

I've figured out that the reason I can't use the hint ISN'T because of the sp_executesql, but because of the "SET" operation.

I WOULD opt to do something like this:

set @CALCULATION = 'select @Result=count(*) from SA_Gen_After_Hours(@start_time,@end_time) OPTION (MAXRECURSION 0)';

which actually works, but the program is not designed to work this way.

I guess I can retool it to work like this, but it's going to involve changing a lot of individual queries.

If nobody else has any ideas, I'm going to get started on this process.

Thanks,

--mobrien118

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-17 : 18:03:12

OPTION clause can be used only with SELECT, DELETE and UPDATE as far as I know.

Editing: And MERGE in SQL 2008 and later.

mobrien118
Starting Member

5 Posts

Posted - 2013-07-17 : 19:15:55

UGGHHH!

I got THAT to work, but the whole thing is inside of a dynamic SQL SPROC that is called by SSRS.

Since the EXEC statement isn't called directly, but inside of a SPROC, it returns "Msg 156, Level 15, State 1, Line 1; Incorrect syntax near the keyword 'OPTION'."

Why does this have to be so difficult?

I can't call "EXEC [MySPROC]" with the hint.

SO I'm still looking for suggestions.

Thanks in advance for any ideas.

--mobrien118

mobrien118
Starting Member

5 Posts

Posted - 2013-07-18 : 06:20:30

I absolutely HATE that I had to do this, but I just copied the code out of the SProc and into the SSRS report so that it works.

This may be a rather rare instance of a way to do things, but I think it is an oversight by Microsoft that it can't be done (call a UDF through dynamic SQL in a SProc and apply hints).

Maybe they will fix it in SQL 2015.

--mobrien118

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources