SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using MAXRECURSION hint with sp_executesql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mobrien118
Starting Member

USA
5 Posts

Posted - 07/17/2013 :  15:04:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 07/17/2013 :  15:57:49  Show Profile  Reply with Quote
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.

Edited by - James K on 07/17/2013 15:59:03
Go to Top of Page

mobrien118
Starting Member

USA
5 Posts

Posted - 07/17/2013 :  16:34:20  Show Profile  Reply with Quote
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
Go to Top of Page

mobrien118
Starting Member

USA
5 Posts

Posted - 07/17/2013 :  17:06:34  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 07/17/2013 :  18:03:12  Show Profile  Reply with Quote
OPTION clause can be used only with SELECT, DELETE and UPDATE as far as I know.

Editing: And MERGE in SQL 2008 and later.

Edited by - James K on 07/17/2013 18:03:28
Go to Top of Page

mobrien118
Starting Member

USA
5 Posts

Posted - 07/17/2013 :  19:15:55  Show Profile  Reply with Quote
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
Go to Top of Page

mobrien118
Starting Member

USA
5 Posts

Posted - 07/18/2013 :  06:20:30  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000