Solved

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

Posted on 2014-07-31
5
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Separate 2 comma delimited columns into separate rows 2 46
sql 2008 how to table join 2 31
Parse this column 6 35
how to make geography query faster?  SQL 7 45
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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