Solved

How to store executed insert query in the table sql server?

Posted on 2014-07-31
5
165 Views
Last Modified: 2014-10-01
Hi ,

I am having the following stored procedure to insert.. I want to add 'sqlquery' column in the below table to store the executed insert query. How this will be done?
ALTER PROCEDURE [dbo].[usp_InsertItemHold]
        @Description varchar(500),
       @FromDate smalldatetime,
                 @ToDate smalldatetime,
                @ItemHoldStartDate smalldatetime,
            @CreatedOn smalldatetime,
        @ReturnID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ItemHold(Description, FromDate,
                      ToDate, ItemHoldStartDate,  CreatedOn)
values
(@Description, @FromDate,
                      @ToDate, @ItemHoldStartDate
                      , @CreatedOn)
SELECT @ReturnID  = @@IDENTITY
0
Comment
Question by:Saroj13
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:Chris Watson
ID: 40231998
Why do you want to do that? You're already capturing the inserted data, neatly separated into strongly typed, searchable fields.
0
 

Author Comment

by:Saroj13
ID: 40232002
I want that for future if I need to execute query due to any reasons, I can execute the stored insert query
0
 
LVL 4

Accepted Solution

by:
Chris Watson earned 500 total points
ID: 40233940
I guess this might work.

ALTER TABLE ItemHold
ADD SqlQuery varchar(max)

Open in new window


ALTER PROCEDURE [dbo].[usp_InsertItemHold]
    @Description varchar(500),
    @FromDate smalldatetime,
    @ToDate smalldatetime,
    @ItemHoldStartDate smalldatetime,
    @CreatedOn smalldatetime,
    @ReturnID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SqlQuery varchar(max)
SET @SqlQuery = 
	'INSERT INTO ItemHold'
  + '(Description'
  + ', FromDate'
  + ', ToDate'
  + ', ItemHoldStartDate'
  + ', CreatedOn)'
  + ' VALUES'
  + '(''' + REPLACE(@Description, '''', '''''') + ''')'
  + ', ''' + CONVERT(varchar(30), @FromDate, 126) + ''''
  + ', ''' + CONVERT(varchar(30), @ToDate, 126) + ''''
  + ', ''' + CONVERT(varchar(30), @ItemHoldStartDate, 126) + ''''
  + ', ''' + CONVERT(varchar(30), @CreatedOn, 126) + ''')'

INSERT INTO ItemHold(Description, FromDate,
                     ToDate, ItemHoldStartDate,
                     CreatedOn, SqlQuery)
VALUES
(@Description, @FromDate,
 @ToDate, @ItemHoldStartDate,
 @CreatedOn, @SqlQuery)
SELECT @ReturnID  = @@IDENTITY 

Open in new window


You could consider creating an audit log (maybe using a trigger) if you're worried about the data getting modified after it's been added.
0
 

Author Comment

by:Saroj13
ID: 40242939
Hi Chris,

Thanks . Its working great. I need little help.

If Description or ItemHoldStartDate or CreatedOn is Null, then insert query is not inserted in the SqlQuery column. Its having null value in sqlquery column


How to fix this?
0
 
LVL 4

Expert Comment

by:Chris Watson
ID: 40243393
You'd have to COALESCE those parameters to account for NULL values.

ALTER PROCEDURE [dbo].[usp_InsertItemHold]
    @Description varchar(500),
    @FromDate smalldatetime,
    @ToDate smalldatetime,
    @ItemHoldStartDate smalldatetime,
    @CreatedOn smalldatetime,
    @ReturnID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SqlQuery varchar(max)
SET @SqlQuery = 
	'INSERT INTO ItemHold'
  + '(Description'
  + ', FromDate'
  + ', ToDate'
  + ', ItemHoldStartDate'
  + ', CreatedOn)'
  + ' VALUES'
  + '(' + COALESCE('''' + REPLACE(@Description, '''', '''''') + '''', 'NULL')
  + ', ' + COALESCE('''' + CONVERT(varchar(30), @FromDate, 126) + '''', 'NULL')
  + ', ' + COALESCE('''' + CONVERT(varchar(30), @ToDate, 126) + '''', 'NULL')
  + ', ' + COALESCE('''' + CONVERT(varchar(30), @ItemHoldStartDate, 126) + '''', 'NULL')
  + ', ' + COALESCE('''' + CONVERT(varchar(30), @CreatedOn, 126) + '''', 'NULL') + ')'

INSERT INTO ItemHold(Description, FromDate,
                     ToDate, ItemHoldStartDate,
                     CreatedOn, SqlQuery)
VALUES
(@Description, @FromDate,
 @ToDate, @ItemHoldStartDate,
 @CreatedOn, @SqlQuery)
SELECT @ReturnID  = @@IDENTITY 

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
query help 18 50
Contained Database Collations 6 19
SQL Server Reporting Services Service Start Timeout 4 15
Sql query 34 19
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now