June 1, 2009 at 10:49 am
------------------------------------------------------------------------------------------------------------
-- Author:Mathieu Cupryk
-- Create date: 05/06/2009
-- Description:Get the fields from the table using ticketID
-- ===========================================================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sprGetTicketByTicketID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sprGetTicketByTicketID]
GO
CREATE PROCEDURE [dbo].[sprGetTicketByTicketID]
(
@TicketID int
)
AS
SET NOCOUNT ON
declare @returnCode int
select @returnCode = 0
SELECT
[TicketID],
[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]
FROM [tbl_tickets]
WHERE
[TicketID] = @TicketID AND [DeletedFlag] = 0
if (@@ERROR <> 0)
BEGIN
select @returnCode = @@ERROR
END
OnExit:
SET NOCOUNT OFF
RETURN @returnCode;
GO
-----------------------------------------------------------------------------------
I am tying to run this with other stored procedure creations.
June 1, 2009 at 10:55 am
Care to provide the error message(s) you are getting when you attempt this?
June 1, 2009 at 11:00 am
Msg 2714, Level 16, State 3, Procedure sprGetTicketByTicketID, Line 60
There is already an object named 'sprGetTicketByTicketID' in the database.
But there is not. Something weird.
June 1, 2009 at 11:31 am
Maybe there is S.P with the same name under different schema? Hard to believe that SQL is lying.
What does this gives?
select * from sys.sysobjects where xtype = 'P' and name = 'sprGetTicketByTicketID';
June 1, 2009 at 12:25 pm
1sprGetTicketByTicketID330484256P 1000002009-06-01 12:02:36.843000P 0402009-06-01 12:02:36.8430000000
June 1, 2009 at 12:27 pm
looks ok to me.
June 1, 2009 at 12:32 pm
Works fine on my system. Did you execute the complete script (including the IF EXISTS -> DROP)?
I copied the procedure and changed the SELECT to an existing table in my environment. Works fine.
June 1, 2009 at 1:09 pm
seems like S.P is there. So if u run your IF EXISTS DROP statement, without the CREATE S.P. (your below statemnt), and then run the SELECT * FROM sysobjects, do u still see the s.p in ur query result window?(You should not be seeing ur S.P), if u see it then maybe ur there is something wrong in your DROP statement(Although looks o.k to me)
June 1, 2009 at 4:03 pm
i will take alook at this. I apprecaite your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply