Solved

Need to add a insert into a stored procedure.

Posted on 2009-05-06
5
315 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
Comment Utility

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
Comment Utility
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
Comment Utility
I want to add this in the above stored proc.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
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
Comment Utility
awesome
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now