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:
DTOScripts - Stores the scripts and some ancillary information DTOScriptsHistory - Stores historical changes to the tables 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
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 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:
Storing the history in XML is fast and easy.
History
I am working on an example web application which uses the above script methods and a UI for managing the scripts.
