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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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  
 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