May 31, 2009 at 3:21 pm
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sprInsertServiceTypeToTicket]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sprInsertServiceTypeToTicket]
GO
CREATE procedure [dbo].[sprInsertServiceTypeToTicket]
@ticketID int
,@dateAdded datetime
,@agentCreated nvarchar(50)
,@flagRemoved bit
,@agentRemoved nvarchar(50)
,@dateRemoved datetime
,@serviceID int
,@IndexTicketServiceID int output
,@resultCode int OUTPUT
,@resultMessage varchar(1000) output
as
begin
IF EXISTS(select null from [dbo].[tbl_index_ticket_services] WHERE [TicketID] = @ticketID and [ServiceID] =@serviceID )
begin
SET @resultCode = 0
SET @resultMessage = 'IndexTicketServiceID ' + CAST(@IndexTicketServiceID AS NVARCHAR(200)) + ': ' + ' was created.'
return
end
begin try
INSERT INTO [dbo].[tbl_index_ticket_services]([TicketID],[DateAdded],[AgentAdded],[DateCreated]
,[FlagRemoved],[AgentRemoved],[DateRemoved],[ServiceTypeID] , [ServiceId])
VALUES( @ticketID,@dateAdded,@agentCreated,GETDATE()
,@flagRemoved,@agentRemoved,@dateRemoved,null,@serviceID)
SET @IndexTicketServiceID =SCOPE_IDENTITY()
END TRY
BEGIN CATCH
SET @IndexTicketServiceID = 0
SET @resultCode = 1
SET @resultMessage = 'IndexTicketServiceID ' + CAST(@IndexTicketServiceID AS NVARCHAR(200))
+ 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
END
GO
May 31, 2009 at 3:40 pm
Hello,
What is it that you want to know?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 31, 2009 at 3:47 pm
I would like to fix the stored procedure what needs to be fixed?
so if exist then do an update otherwise do an insert.
May 31, 2009 at 4:17 pm
Hello again,
As we don’t have your table definitions, we can’t test it, but at a guess all you seem to be missing is an Update statement before the first “Return” e.g.
Update
[dbo].[tbl_index_ticket_services]
Set
[DateAdded] = @DateAdded,
Etc …
Where
[TicketID] = @ticketID and [ServiceID] =@serviceID
You may also want to add Exception Handling for the Update as per the Insert statement?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 31, 2009 at 4:29 pm
I would love it if you guys could give me an hand.
CREATE PROCEDURE [dbo].[sprUpdateServiceTypeToTicket]
@indexTicketServiceID int = null,
@ticketID int = null,
@dateAdded datetime = null,
@agentAdded nvarchar(50) = null,
@dateCreated datetime = null,
@flagRemoved bit = null,
@agentRemoved nvarchar(50) = null,
@dateRemoved datetime = null,
@serviceTypeID int = null,
@serviceID int = null,
@resultCode int OUTPUT,
@resultMessage varchar(1000) output
AS
BEGIN
BEGIN TRY
UPDATE [dbo].[tbl_index_ticket_services]
SET
[TicketID] = @ticketID,
[DateAdded] = @dateAdded,
[FlagRemoved] = @flagRemoved,
[AgentRemoved] = @agentRemoved,
[DateRemoved] = @dateRemoved,
[ServiceID] = @serviceID
WHERE [TicketID] = @ticketID and [ServiceID] =@serviceID
-----------------------------------------------------------------------
and here is the table definiton
CREATE TABLE [dbo].[tbl_index_ticket_services](
[IndexTicketServiceID] [int] IDENTITY(1,1) NOT NULL,
[TicketID] [int] NULL,
[DateAdded] [datetime] NULL,
[AgentAdded] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCreated] [datetime] NULL,
[FlagRemoved] [bit] NULL DEFAULT ((0)),
[AgentRemoved] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateRemoved] [datetime] NULL,
[ServiceTypeID] [int] NULL,
[ServiceID] [int] NULL,
CONSTRAINT [PK_tbl_index_ticket_services] PRIMARY KEY NONCLUSTERED
(
[IndexTicketServiceID] 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]
GO
June 1, 2009 at 2:18 am
Hope this works,
CREATE procedure [dbo].[sprInsertServiceTypeToTicket]
@ticketID int
,@dateAdded datetime
,@agentCreated nvarchar(50)
,@flagRemoved bit
,@agentRemoved nvarchar(50)
,@dateRemoved datetime
,@serviceID int
,@IndexTicketServiceID int output
,@resultCode int OUTPUT
,@resultMessage varchar(1000) output
as
BEGIN
BEGIN TRY
IF EXISTS(select null from [dbo].[tbl_index_ticket_services] WHERE [TicketID] = @ticketID and [ServiceID] =@serviceID )
BEGIN
SET @resultCode = 0
SET @resultMessage = 'IndexTicketServiceID ' + CAST(@IndexTicketServiceID AS NVARCHAR(200)) + ': ' + ' was created.'
-- DO UPDATE HERE.
END ELSE BEGIN
-- SET @resultCode AND @resultMessage HERE.
-- INSERT HERE.
INSERT INTO [dbo].[tbl_index_ticket_services]([TicketID],[DateAdded],[AgentAdded],[DateCreated]
,[FlagRemoved],[AgentRemoved],[DateRemoved],[ServiceTypeID] , [ServiceId])
VALUES( @ticketID,@dateAdded,@agentCreated,GETDATE()
,@flagRemoved,@agentRemoved,@dateRemoved,null,@serviceID)
SET @IndexTicketServiceID =SCOPE_IDENTITY()
END TRY
BEGIN CATCH
SET @IndexTicketServiceID = 0
SET @resultCode = 1
SET @resultMessage = 'IndexTicketServiceID ' + CAST(@IndexTicketServiceID AS NVARCHAR(200))
+ 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
END
GO
"Don't limit your challenges, challenge your limits"
June 1, 2009 at 7:17 am
Hi there, I have a solution but if the entry is existing it just does an update. Canyou please help me out,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[sprInsertServiceTypeToTicket]
(
@ticketID int = null,
@dateAdded datetime = null,
@agentCreated nvarchar(50) = null,
@flagRemoved bit = null,
@agentRemoved nvarchar(50) = null,
@dateRemoved datetime = null,
@serviceID int = null,
@serviceTypeID int = null,
@IndexTicketServiceID int output,
@resultCode int OUTPUT,
@resultMessage varchar(1000) output
)
as
BEGIN
Declare @RowCount int;
set @RowCount=0;
SELECT @RowCount= Count(*)from [dbo].[tbl_index_ticket_services]
WHERE [TicketID] = @ticketID and [ServiceID] =@serviceID
BEGIN TRANSACTION
BEGIN TRY
if (@RowCount = 0)
BEGIN
INSERT INTO [dbo].[tbl_index_ticket_services]
([TicketID],
[DateAdded],
[AgentAdded],
[DateCreated],
[FlagRemoved],
[AgentRemoved],
[DateRemoved],
[ServiceTypeID],
[ServiceId])
VALUES
( @ticketID,
@dateAdded,
@agentCreated,
GETDATE() ,
@flagRemoved,
@agentRemoved,
@dateRemoved,
null,
@serviceID
)
SET @IndexTicketServiceID =SCOPE_IDENTITY()
SET @resultCode = 0
SET @resultMessage = 'IndexTicketServiceID ' + CAST(@IndexTicketServiceID AS NVARCHAR(200)) + ': ' + ' was created.'
END
else
BEGIN
UPDATE [dbo].[tbl_index_ticket_services]
SET
[TicketID] = @ticketID,
[DateAdded] = @dateAdded,
[AgentAdded] = @agentCreated,
[DateCreated] = GETDATE(),
[FlagRemoved] = @flagRemoved,
[AgentRemoved] = @agentRemoved,
[DateRemoved] = @dateRemoved,
[ServiceTypeID] = @serviceTypeID,
[ServiceID] = @serviceID
WHERE [TicketID] = @ticketID and [ServiceID] =@serviceID
SET @IndexTicketServiceID =0
SET @resultCode = 0
SET @resultMessage = 'IndexTicketServiceID ' + CAST(@IndexTicketServiceID AS NVARCHAR(200)) + ': ' + ' was created.'
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 1 ROLLBACK
SET @IndexTicketServiceID = 0
SET @resultCode = 1
SET @resultMessage = 'ticketID ' + CAST(@ticketID AS NVARCHAR(200)) + ': ' + ' error was created. ' + 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
June 1, 2009 at 10:50 pm
Are you getting any error message?
If you can give some example data for insert and update, then it will be very helpful to understand the problem.
"Don't limit your challenges, challenge your limits"
June 1, 2009 at 11:01 pm
Hello again,
if the entry is existing it just does an update
Is that not what you want?
Normally I would have expected that if the Primary Key(s) already exist in the table, then the logic should be to do an Update, otherwise an Insert. What logic do you require?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 2, 2009 at 7:39 am
Excellent job guys. I appreciate your work.
June 2, 2009 at 10:58 pm
Did you get your solution?
Please update us with your result.
"Don't limit your challenges, challenge your limits"
June 2, 2009 at 11:54 pm
SOLVED
June 2, 2009 at 11:55 pm
Grasshopper
Has posted the correct solution. Thanks again.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply