通过

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

Comments