Solved

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

Posted on 2014-07-31
5
168 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Troubleshooting Methodology - steps 3 36
SQL Exceptions 3 39
SQL Server Question 5 28
sql Total query 2 17
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach 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.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

16 Experts available now in Live!

Get 1:1 Help Now