?
Solved

Need to add a insert into a stored procedure.

Posted on 2009-05-06
5
Medium Priority
?
324 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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 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