Rick Caminiti dotCOM

I was trying to figure out how to use Native Dynamic SQL in SQL Server (using late binding) for performance reasons. With late binding, you are able to reuse cached execution plans and therefore can get better performance. This also keeps you from having from having to create the same query multiple times in a stored procedure just to allow for different filter criteria. You can build the SQL on the fly, bind the parameters and execute the SQL on the fly.

Here’s what I ended up with.




USE [database]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[proc]

@Parm1 INT,

@Parm2 INT,

@Parm3 DATETIME,

AS

SET NOCOUNT ON

DECLARE

@SELECT NVARCHAR(1000),

@FROM NVARCHAR(1000),

@WHERE NVARCHAR(1000),

@ORDERBY NVARCHAR(1000),

@SQL NVARCHAR(4000), –MAX SIZE for dynamic SQL is 4000 characters

@DYNSQLPARMLIST NVARCHAR(250),

@vParm3 CHAR(10)

 

– -1 implies ALL. Set -1s to NULL for COALESCE functions in SQL

IF @Parm1 = -1 SET @Parm1 = NULL

IF @Parm2 = -1 SET @Parm2 = NULL

 

SET @vParm3 = CONVERT(char, @Parm3, 101) /*Just a placeholder for easier dyn sql conversion*/

 

/*For some reason, dynamic sql can’t handle s or linefeeds in this section, so they’re all on one line with spaces separating them*/

/*Note, the DATETIME parameter is not in CHAR format. Again, this is just because I’m building a string for the SQL*/

SET @DYNSQLPARMLIST = N’@dynParm1 INT, @dynParm2 INT, @dynParm3 CHAR(10)’

 

/*Build the SQL. Note the parameters match the parameters in the @DYNSQLPARMLIST variable. @dynParm1, not @Parm1*/

SET @SELECT = ‘SELECT *’

SET @FROM = ‘ FROM table1 with (nolock)’

SET @WHERE = ‘ WHERE column1 = COALESCE(@dynParkID, column1) –if null, set column = to itself (no filter)

AND column2 = COALESCE(@dynInformationTypeID, column2)

AND column3 > cast(@dynParm3 as datetime)’

SET @ORDER = ‘ ORDER BY 1?

 

SET @SQL =

@SELECT + ‘ ‘ +

@FROM + ‘ ‘ +

@WHERE + ‘ ‘ +

@ORDERBY

 

EXECUTE sp_executesql

@SQL,

@DYNSQLPARMLIST,

@dynParm1 = @Parm1,

@dynParm2 = @Parm2,

@dynParm3 = @vParm3

 

SET NOCOUNT OFF

For more information, check out this page: http://ping.fm/mOmXI