Dynamic SQL From DB Table with Parameters

Introduction

Have you ever needed to execute a query to retrieve two fields from a database but did not want to store the query in a Procedure and definitely didn't want to store it in your code. Here is a solution which will give you the ability to do that and update it without having to re-compile or update stored procedures.

You can store the query in a database table and execute it via a stored procedure with Parameters.

The goal was to define a stored procedure which could execute Dynamic SQL from a database table with parameters. And have the ability to change the SQL but not the stored procedure.

Typically, you would either build your T-SQL in code or on the database as follows:

declare @sql varchar(max) =  'select * from users where userid = @userid'
declare @params  varchar(max) = '@userid int'    
sp_executesql @sql, params, @userid = 5

This executes the same query as:

select UserID, Username, DisplayName from users where userid = 5  or  displname = 'User1' 

Using the Code

Data Transfer Objects (DTO) are great for passing small objects around code. This example is based on the premise of using DTO objects to pass data around.

The database sample consists of three tables and three stored procedures:

1. DTOScripts - Stores the scripts and some ancillary information
2. DTOScriptsHistory - Stores historical changes to the tables
3. Users - Sample table and data for queries

Stored Procedures

• DtoScriptExec - Executes the stored procedure with parameters
• DtoScripts_Get - Retrieves a script by id or unique identifier
• DtoScripts InsUpd - Inserts or updates a given script and creates history record

DtoScriptExec Procedure Explained

This procedure executes the script and passes the parameters provided by the users. Parameters are defined in two groups. Standard or items which rarely change are used consistently or variable type parameters (change regularly).

Standard Parameters

• @DTOScriptId int - script id from the script table
• @DTOUniqueID uniqueidentifier - used in code and ensures a more concrete selection
• @UserID BigInt - userid requesting the script (optional)
• @isdeleted bit - used for soft deletes (optional)
• @isactive bit - used to disable a script selection (optional)

Variable Parameters

• @Var1 BigInt = null,
• @Var2 BigInt = null,
• @Var3 BigInt = null,
• @Var4 nvarchar(255) = null,
• @Var5 nvarchar(255) = null)

Variables are exactly that, variables. You can add or remove as needed. If you need more than 5 or 6 for a query, chances are you should not be creating a DTO script for it.

Database Objects

CREATE TABLE [dbo].[DTOScripts](
    [DTOScriptId] [int] IDENTITY(1,1) NOT NULL,
    [DTOUniqueID] [uniqueidentifier] NULL,
    [UserID] [bigint] NOT NULL,
    [DTOName] [varchar](100) NULL,
    [DTOSQL] [varchar](4000) NULL,
    [CreateDt] [nchar](10) NULL,
    [IsDeleted] [bit] NULL,
    [IsDeletedDt] [datetime] NULL,
    [LastModifiedDt] [datetime] NULL,
    [IsActive] [bit] NULL,
    [IsActiveDT] [datetime] NULL,
 CONSTRAINT [PK_DTOScripts] PRIMARY KEY CLUSTERED 
(
    [DTOScriptId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[DTOScripts] ADD  CONSTRAINT _
[DF_DTOScripts_DTOUniqueID]  DEFAULT (newid()) FOR [DTOUniqueID]
GO
 
ALTER TABLE [dbo].[DTOScripts] ADD  CONSTRAINT _
[DF_DTOScripts_CreateDt]  DEFAULT (getdate()) FOR [CreateDt]
GO
 
ALTER TABLE [dbo].[DTOScripts] ADD  CONSTRAINT _
[DF_DTOScripts_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO
 
ALTER TABLE [dbo].[DTOScripts] ADD  CONSTRAINT _
[DF__DTOScript__IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

CREATE TABLE [dbo].[DTOScriptsHistory](
    [DTOScriptsHistoryId] [bigint] IDENTITY(1,1) NOT NULL,
    [DTOScriptId] [int] NOT NULL,
    [UserID] [bigint] NOT NULL,
    [OldValue] [xml] NULL,
    [UpdatedDt] [datetime] NOT NULL,
    [IsDeletedDt] [datetime] NULL,
    [LastModifiedDt] [datetime] NULL,
    [IsActive] [bit] NULL,
    [IsActiveDT] [datetime] NULL,
 CONSTRAINT [PK_DTO_Scripts_History] PRIMARY KEY CLUSTERED 
(
    [DTOScriptsHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[DTOScriptsHistory]  WITH CHECK ADD  _
CONSTRAINT [FK_DTO_Scripts_History_DTO_Scripts] FOREIGN KEY([DTOScriptId])
REFERENCES [dbo].[DTOScripts] ([DTOScriptId]) 

CREATE TABLE [dbo].[Users](
    [UserID] [bigint] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](100) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Email] [nvarchar](256) NULL,
    [DisplayName] [nvarchar](128) NULL,
    [CreatedDt] [datetime] NULL,
    [IsDeleted] [bit] NULL,
    [IsDeletedDt] [datetime] NULL,
    [IsDeletedUserId] [bigint] NULL,
    [LastModifiedDt] [datetime] NULL,
    [LastmodifiedById] [bigint] NULL,
    [IsActive] [bit] NULL,
    [IsActiveDT] [datetime] NULL,
    [IsActiveUserId] [bigint] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_DisplayName]  DEFAULT ('') FOR [DisplayName]
GO
 
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_CreatedDt]  DEFAULT (getdate()) FOR [CreatedDt]
GO 

--Populate  the user table with test data
DECLARE
    @i          int,
    @Content    int;
SET @i = 5001;
 
WHILE @i > 5000 AND @i < 10000
BEGIN
    INSERT INTO Users ([Username], [FirstName], [LastName], _
    [Email], [DisplayName], [CreatedDt],[LastModifiedDt], _
    [IsDeleted],[IsDeletedDt], [IsActive], [IsActiveDT])
    VALUES('User_'+ CAST(@i AS varchar(10))
            , 'UserFN', 'UserLn_'+ CAST(@i AS varchar(10))
            , 'UserLn_'+ CAST(@i AS varchar(10)) + '@some.com'
            , 'UserFN' + 'UserLn_'+ CAST(@i AS varchar(10))
            , DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
            , DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
            , 0
            ,DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
            , 1
            , DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0))
    SET @i = @i + 5;
END
GO  

Create a few test scripts.

--Insert 3 scripts into the script table
INSERT INTO [dbo].[DTOScripts]([DTOUniqueID],[UserID],[DTOName],_
[DTOSQL],[CreateDt],[IsDeleted],[IsDeletedDt],[LastModifiedDt],[IsActive],[IsActiveDT])
VALUES(NEWID(),1
    ,'Sample Users 1'
    ,'select UserID, Username DisplayName from users where userid = @userId'
    ,GETDATE()
    ,null
    ,null
    ,null
    ,null
    ,null)
GO

INSERT INTO [dbo].[DTOScripts]([DTOUniqueID],[UserID],[DTOName],_
[DTOSQL],[CreateDt],[IsDeleted],[IsDeletedDt],[LastModifiedDt],[IsActive],[IsActiveDT])
VALUES(NEWID()
    ,1
    ,'Sample Users between id'
    ,'select UserID, Username DisplayName from users where userid between @var1 and @Var2'
    ,GETDATE()
    ,null
    ,null
    ,null
    ,null
    ,null)
GO

INSERT INTO [dbo].[DTOScripts]([DTOUniqueID],[UserID],[DTOName],[DTOSQL],_
[CreateDt],[IsDeleted],[IsDeletedDt],[LastModifiedDt],[IsActive],[IsActiveDT])
VALUES (NEWID()
    ,1
    ,'Sample Users created between dates'
    ,'select UserID, Username, DisplayName from users where CreatedDt between @var3 and @Var4'
    ,GETDATE()
    ,null
    ,null
    ,null
    ,null
    ,null)
GO 

Executing Scripts...

The DTO script would allow you to execute the same parameterized statement from a table. This would execute the second script above which gets users within a range of IDs.

EXEC [DTOScripts_Exec]
    @DTOScriptId = 2,
    @DTOUniqueID = NULL,
    @UserID = 5,
    @isdeleted = NULL,
    @isactive = NULL,
    @Var1 = 50,
    @Var2 = 100,
    @Var3 = NULL,
    @Var4 = NULL,
    @Var5 = NULL 

ALTERNATE 
exec [DTOScripts_Exec] 1, null, 5, null, null, 50, 100, null, null 

Storing and Retrieving Script Change History

The stored procedure which handles the insert and updates also handles the history records. Files are attached. The history is stored and retrieved in XML format. This method is fast, efficient saves a ton of time.

You can quickly capture and store a single record via XML as follows:

INSERT INTO [dbo].[DTOScriptsHistory]([DTOScriptId], [UserID], [OldValue], [UpdatedDt])
VALUES(@DTOScriptId
      ,@UserID
      ,(select * from DTOScripts where DTOScriptId = @DTOScriptId FOR XML AUTO, ELEMENTS)
      ,GETDATE())

Sample XML

<DTOScripts>
  <DTOScriptId>1</DTOScriptId>
  <DTOUniqueID>4D7A7F6B-4C74-47F4-8C43-20C7D1781215</DTOUniqueID>
  <UserID>1</UserID>
  <DTOName>Sample Users 1</DTOName>
  <DTOSQL>select UserID, Username, DisplayName from users where userid = @userId</DTOSQL>
  <CreateDt>Oct 19 201</CreateDt>
  <IsDeleted>0</IsDeleted>
  <IsDeletedDt>2013-01-19T00:00:00</IsDeletedDt>
  <LastModifiedDt>2013-10-19T04:53:48.657</LastModifiedDt>
  <IsActive>1</IsActive>
  <IsActiveDT>2013-10-19T04:53:48.657</IsActiveDT>
</DTOScripts>  

The following sample query of the XML data is fast and allows you to quickly see the changes to the scripts queries in the table:

SELECT 
    OldValue.value('(/DTOScripts/DTOScriptId)[1]', 'int') AS 'ID',
    OldValue.value('(/DTOScripts/DTOUniqueID)[1]', 'varchar(50)') AS 'Unique',
    OldValue.value('(/DTOScripts/DTOName)[1]', 'varchar(500)') AS 'Name',
    OldValue.value('(/DTOScripts/DTOSQL)[1]', 'varchar(max)') AS 'SQL',        
    OldValue.value('(/DTOScripts/IsDeleted)[1]', 'bit') AS 'IsDeleted',
    OldValue.value('(/DTOScripts/IsDeletedDt)[1]', 'datetime') AS 'IsDeletedDt',
    OldValue.value('(/DTOScripts/IsActive)[1]', 'bit') AS 'IsActive',
    OldValue.value('(/DTOScripts/IsActiveDT)[1]', 'datetime') AS 'IsActiveDT',
    OldValue.value('(/DTOScripts/LastModifiedDt)[1]', 'datetime') AS 'LastModifiedDt'
from [dbo].[DTOScriptsHistory]
where  OldValue.value('(/DTOScripts/DTOScriptId)[1]', 'int') = 1 

Points of Interest

Executing dynamic SQL with parameters:

• http://technet.microsoft.com/en-us/library/ms188001.aspx
• http://stackoverflow.com/questions/1036745/t-sql-how-to-use-parameters-in-dynamic-sql

Storing the history in XML is fast and easy.

• http://technet.microsoft.com/en-us/library/ms178107.aspx

History

I am working on an example web application which uses the above script methods and a UI for managing the scripts.