Solved

Need to add a insert into a stored procedure.

Posted on 2009-05-06
5
318 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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