Solved

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

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
space used inside SQL data file- PerfMon counter.. 2 27
SQL Server Insert where not exists 24 46
MS SQL Server select from Sub Table 14 24
SQL Recursion 6 18
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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