Solved

Need to add a insert into a stored procedure.

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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