Solved

Need to add a insert into a stored procedure.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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