Need to fix the procedure to do an insert or updat

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to fix the procedure to do an insert or updat

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2009-05-31 : 17:30:34

I need to do an insert or an update depenped.

IF exist then do an update. else insert into table.

Any help would be awesome.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-31 : 18:52:10

Post your table structure some sample data and desired results. It's easy to do what you are requesting, but with the information provided there is not a lot to go off of.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2009-05-31 : 19:05:46

so if serviceId and ticketid exist then do an update otherwise do an insert.

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
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

Any help would be awesome.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-01 : 00:32:17

[code]
try like this
ALTER 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

IF EXISTS(SELECT 1 FROM [dbo].[tbl_index_ticket_services] WHERE [TicketID] = @ticketID and [ServiceID] =@serviceID )
BEGIN
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
END
ELSE
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()
END
[/code]

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2009-06-01 : 00:49:08

I tried the following but when I insert a record that is in the table it goes through the whole thing.

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 = 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
GO

Any help would be awesome.

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-01 : 11:58:05

The store procedure you have written actually works correctly.

I have executed it successfully inserting and updating records using the same stored procedure.

I need more clarity on what you are trying to achieve.

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2009-06-01 : 12:10:04

It is ok for now. I would like to not do an update if a service exist.

Any help would be awesome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources