Database Programming: SET Statements and Dynamic SQL


What’s this, you say?  Useful T-SQL making a return to this blog?


Yep.  The first T-SQL I’ve posted since we were in the midst of the prime number exercise, over a month ago.  The first commercially viable T-SQL I’ve posted since the end of August.


It would apparently be fair to state that my new position has changed my perspective a bit, at least as it’s reflected here.


At any rate, Yulin posed the following question:



I’m running the following script..


declare @mysql nvarchar(max)


select @mysql = N



SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


IF  (OBJECT_ID(N”dbo.mySproc”) IS NOT NULL)


BEGIN


      DROP PROCEDURE dbo.mySproc


END


GO


CREATE PROCEDURE dbo.mySproc


AS


SET NOCOUNT ON


select 1 from sys.objects



.. and getting these errors:


Msg 102, Level 15, State 1, Line 4


Incorrect syntax near ‘GO’.


Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘GO’. 


Any suggestions? 


After a couple of false starts, I was able to provide Yulin with the following:



Here’s a little batch which demonstrates that child sessions will in fact inherit SET options from parent sessions.  Based on this research, you should be able to use your SET options in the caller with confidence that your EXEC will inherit them.  This will allow you to make the CREATE/ALTER the first process in the batch.


 


declare @mysql nvarchar(max)


 


set ansi_nulls on


 


set @mysql = ‘SELECT SESSIONPROPERTY(”ANSI_NULLS”)’


exec (@mysql)


 


set ansi_nulls off


 


set @mysql = ‘SELECT SESSIONPROPERTY(”ANSI_NULLS”)’


exec (@mysql) 


I felt pretty good about this solution, until Peter trumped it:



But if you still want to do it entirely in a dynamic batch just start a new EXEC() inside the first:


select @mysql = N


SET ANSI_NULLS ON;


SET QUOTED_IDENTIFIER ON;


IF  (OBJECT_ID(N”dbo.mySproc”) IS NOT NULL)


BEGIN


      DROP PROCEDURE dbo.mySproc


END;


exec (”CREATE PROCEDURE dbo.mySproc


AS


SET NOCOUNT ON


select 1 from sys.objects”)



exec (@mysql)


Nested EXECs!  Brilliant, Peter, and much preferable to my inheritance-based example, because we’re not changing the settings of the parent session.


Hopefully, it won’t be another two and a half months before another coding post..


     -wp