Solved

Need to add a insert into a stored procedure.

Posted on 2009-05-06
5
322 Views
Last Modified: 2012-05-06
I have the following:
INSERT INTO [dbo].[tbl_index_ticket_pocs] ( [TicketID], [POCID], [DateAdded], [AgentAdded], [DateCreated] ) VALUES ( @intTicketID, '2858', '5/6/2009 10:59:08 AM', 'Mcupryk', '5/6/2009 10:59:08 AM' )
=================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_index_ticket_companies](
      [IndexTicketCompanyID] [int] IDENTITY(1,1) NOT NULL,
      [TicketID] [int] NULL,
      [LocationID] [int] NULL,
      [DateAdded] [datetime] NULL,
      [AgentAdded] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DateCreated] [datetime] NULL,
 CONSTRAINT [PK_tbl_index_ticket_companies] PRIMARY KEY NONCLUSTERED
(
      [IndexTicketCompanyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Right after the following insert snippet.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[sprInsertIntoTickets]
(
@ticketNumber AS nvarchar(50) = null,
@ticketType AS nvarchar(50) = null,
@ticketStatus AS nvarchar(50) = null,
@ticketSeverity AS nvarchar(50) = null,
@shortDescription AS nvarchar(1000) = null,
@externalTicket AS nvarchar(1000) = null,
@reasonMenu AS nvarchar(50) = null,
@problemNote AS ntext = null,
@solutionNote AS ntext = null,
@actionNote AS ntext = null,
@followupNote AS ntext = null,
@companiesFlag AS bit = null,
@agentCreated AS nvarchar(50) = null,
@dateCreated AS datetime = null,
@dateLastModified AS datetime = null,
@agentClosed AS nvarchar(50) = null,
@dateClosed AS datetime = null,
@agentFollowup AS nvarchar(50) = null,
@followupBy AS datetime = null,
@deletedFlag AS bit = null,
@ticketTypeOld AS nvarchar(50) = null,
@assignedTo AS varchar(50) = null,
@requestType AS varchar(50) = null,
@serviceImpact AS varchar(50) = null,
@dateTimeAssigned AS datetime = null,
@priority AS varchar(50) = null,
@dateAssigned AS datetime = null,
@trackIt AS varchar(50) = null,
@contactMethod AS varchar(50) = null,
@locationSIMs AS varchar(50) = null,
@userID AS nvarchar(50) = null,
@ticketApplicationType AS nvarchar(50) = null,
@accountID AS int = null,
@ticketID INT OUTPUT,
@resultCode INT OUTPUT,
@resultMessage NVARCHAR(200) OUTPUT
) 
AS
 
BEGIN
      BEGIN TRANSACTION
      BEGIN TRY
 
 
 
 
INSERT INTO
  [dbo].[tbl_tickets]
(
  [TicketNumber],
  [TicketType],
  [TicketStatus],
  [TicketSeverity],
  [ShortDescription],
  [ExternalTicket],
  [ReasonMenu],
  [ProblemNote],
  [SolutionNote],
  [ActionNote],
  [FollowupNote],
  [CompaniesFlag],
  [AgentCreated],
  [DateCreated],
  [DateLastModified],
  [AgentClosed],
  [DateClosed],
  [AgentFollowup],
  [FollowupBy],
  [DeletedFlag],
  [TicketTypeOld],
  [AssignedTo],
  [RequestType],
  [ServiceImpact],
  [DateTimeAssigned],
  [Priority],
  [DateAssigned],
  [TrackIt],
  [ContactMethod],
  [LocationSIMs],
  [UserID],
  [TicketApplicationType],
  [AccountID]
)
VALUES
(
  @ticketNumber,
  @ticketType,
  @ticketStatus,
  @ticketSeverity,
  @shortDescription,
  @externalTicket,
  @reasonMenu,
  @problemNote,
  @solutionNote,
  @actionNote,
  @followupNote,
  @companiesFlag,
  @agentCreated,
  @dateCreated,
  @dateLastModified,
  @agentClosed,
  @dateClosed,
  @agentFollowup,
  @followupBy,
  @deletedFlag,
  @ticketTypeOld,
  @assignedTo,
  @requestType,
  @serviceImpact,
  @dateTimeAssigned,
  @priority,
  @dateAssigned,
  @trackIt,
  @contactMethod,
  @locationSIMs,
  @userID,
  @ticketApplicationType,
  @accountID
)
 
SET @ticketID=SCOPE_IDENTITY()
UPDATE tbl_tickets SET TicketNumber=CAST((@ticketID + 10) AS VARCHAR)
WHERE TicketID = @ticketID
 
            SET @resultCode = 0
            SET @resultMessage = 'ticketID ' + CAST(@ticketID  AS NVARCHAR(200)) + ': ' + @ticketNumber  + ' was created.'
            
 
      END TRY
 
      BEGIN CATCH
	--	EXEC RethrowError;
            IF @@TRANCOUNT > 1 ROLLBACK
            
            SET @ticketID = 0
            SET @resultCode = 1
            SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200)) + ': ' + @ticketNumber + ' created error.  ' + char(13) + char(10)
                        + ERROR_NUMBER() + '. '  + char(13) + char(10)
                        + ERROR_MESSAGE() + '. ' + char(13) + char(10)
                        + ERROR_LINE() + '. ' + char(13) + char(10)
                        + ERROR_PROCEDURE() + '. ' + char(13) + char(10)
                        + ERROR_STATE() + '. ' + char(13) + char(10)
 
      END CATCH
 
  COMMIT TRAN
END
 
I have to put this in the above code. if one insert is done the next should be done.

Open in new window

0
Comment
Question by:mathieu_cupryk
[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 75

Expert Comment

by:Aneesh Retnakaran
ID: 24317017

SET @ticketID=SCOPE_IDENTITY()

INSERT INTO [dbo].[tbl_index_ticket_pocs] ( [TicketID], [POCID], [DateAdded], [AgentAdded], [DateCreated] ) VALUES ( @intTicketID, '2858', GETDATE(), 'Mcupryk',GETDATE() )

UPDATE tbl_tickets SET TicketNumber=CAST((@ticketID + 10) AS VARCHAR)
WHERE TicketID = @ticketID
 
            SET @resultCode = 0
            SET @resultMessage = 'ticketID ' + CAST(@ticketID  AS NVARCHAR(200)) + ': ' + @ticketNumber  + ' was created.'
0
 

Author Comment

by:mathieu_cupryk
ID: 24317831
I will make this a separte stored procedure can you help me build this,

INSERT INTO [tbl_index_ticket_pocs]
(
    [TicketPOCIndexID],
    [TicketID],
    [POCID],
    [DateAdded],
    [AgentAdded],
    [DateCreated]
)
VALUES
(
    @TicketPOCIndexID,
    @TicketID,
    @POCID,
    @DateAdded,
    @AgentAdded,
    @DateCreated
)
How can I add this in.
 
0
 

Author Comment

by:mathieu_cupryk
ID: 24317993
I want to add this in the above stored proc.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24318273
mathieu_cupryk,

here it is

and call like this




SET @ticketID=SCOPE_IDENTITY()
declare @now datetime
set @now  = getdate()

Execute dbo.Insert_tbl_index_ticket_pocs @intTicketID, '2858', @Now , 'Mcupryk', @Now

UPDATE tbl_tickets SET TicketNumber=CAST((@ticketID + 10) AS VARCHAR)
WHERE TicketID = @ticketID
 
            SET @resultCode = 0
            SET @resultMessage = 'ticketID ' + CAST(@ticketID  AS NVARCHAR(200)) + ': ' + @ticketNumber  + ' was created.'

Aneesh
CREATE PROCEDURE dbo.Insert_tbl_index_ticket_pocs
AS 
    @TicketPOCIndexID  INT ,
    @TicketID INT ,
    @POCID INT , 
    @DateAdded DATETIME ,
    @AgentAdded DATETIME ,
    @DateCreated DATETIME
AS  
BEGIN  
	SET NOCOUNT ON
	INSERT INTO [tbl_index_ticket_pocs]
	(
		[TicketPOCIndexID],
		[TicketID],
		[POCID],
		[DateAdded],
		[AgentAdded],
		[DateCreated]
	)
	VALUES
	(
		@TicketPOCIndexID,
		@TicketID,
		@POCID,
		@DateAdded,
		@AgentAdded,
		@DateCreated
	)
END	
GO

Open in new window

0
 

Author Closing Comment

by:mathieu_cupryk
ID: 31578563
awesome
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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