Stored Procedure Not Working - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Not Working

Author

Topic

mspelly
Starting Member

2 Posts

Posted - 2012-08-30 : 15:14:49

Hi

I have the following two tables

/**********************************************************************************************/
/****** Object: Table [dbo].[FA_EventType] Script Date: 01/07/2012 22:01:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[FA_EventType](
[EventType_ID] [smallint] IDENTITY(1,1) NOT NULL,
[Event_Type] [nvarchar](30) NOT NULL UNIQUE,
CONSTRAINT [PK_FA_EventType] PRIMARY KEY CLUSTERED
(
[EventType_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/**********************************************************************************************/
/****** Object: Table [dbo].[FA_EventDetails] Script Date: 01/08/2012 12:29:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[FA_EventDetails](
[EventDetail_ID] [bigint] IDENTITY(1,1) NOT NULL,
[Event_Ref_Number] [nvarchar](100) NULL,
[Event_Description] [nvarchar](3000) NOT NULL,
[Event_DateTime] [datetime] NOT NULL,
[User_ID] [nvarchar](50) NULL,
[Sys_ID] [smallint] NULL,
[ItemsUnderControl_ID] [int] NULL,
[EventType_ID] [smallint] NOT NULL,
CONSTRAINT [PK_FA_EvetDetails] PRIMARY KEY CLUSTERED
(
[EventDetail_ID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[FA_EventDetails] WITH CHECK ADD CONSTRAINT [FK_FA_EventDetails_FA_EventType] FOREIGN KEY([EventType_ID])
REFERENCES [dbo].[FA_EventType] ([EventType_ID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[FA_EventDetails] CHECK CONSTRAINT [FK_FA_EventDetails_FA_EventType]
GO

ALTER TABLE [dbo].[FA_EventDetails] WITH CHECK ADD CONSTRAINT [FK_FA_EventDetails_FA_ItemsUnderControl] FOREIGN KEY([ItemsUnderControl_ID])
REFERENCES [dbo].[FA_ItemsUnderControl] ([ItemUnderControl_ID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[FA_EventDetails] CHECK CONSTRAINT [FK_FA_EventDetails_FA_ItemsUnderControl]
GO

ALTER TABLE [dbo].[FA_EventDetails] WITH CHECK ADD CONSTRAINT [FK_FA_EventDetails_FA_Systems] FOREIGN KEY([Sys_ID])
REFERENCES [dbo].[FA_Systems] ([Sys_ID])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[FA_EventDetails] CHECK CONSTRAINT [FK_FA_EventDetails_FA_Systems]
GO

ALTER TABLE [dbo].[FA_EventDetails] ADD CONSTRAINT [DF_FA_EventDetails_Event_Ref_Number] DEFAULT ('N/A') FOR [Event_Ref_Number]
GO

ALTER TABLE [dbo].[FA_EventDetails] ADD CONSTRAINT [DF_FA_EventDetails_Event_Description] DEFAULT ('N/A') FOR [Event_Description]
GO

I have following stored procedure
--Check if SPROC exists and if it does drop it
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_GetAllEventDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_GetAllEventDetails]
GO
CREATE PROCEDURE [dbo].[usp_GetAllEventDetails]
@DateStart DATETIME,
@DateEnd DATETIME,
@UserID NVARCHAR(50),
@EventType NVARCHAR(30),
@EventRefNum NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT
dbo.FA_EventDetails.Event_Ref_Number,
dbo.FA_EventDetails.Event_Description,
dbo.FA_EventDetails.Event_DateTime,
dbo.FA_EventType.Event_Type,
dbo.FA_EventDetails.User_ID
FROM
dbo.FA_EventDetails
INNER JOIN
dbo.FA_EventType ON dbo.FA_EventDetails.EventType_ID = dbo.FA_EventType.EventType_ID
WHERE
(dbo.FA_EventType.Event_Type LIKE @EventType) AND
(dbo.FA_EventDetails.User_ID LIKE @UserID) AND
(dbo.FA_EventDetails.User_ID LIKE @EventRefNum) AND
(dbo.FA_EventDetails.Event_DateTime
BETWEEN CONVERT(DATETIME, @DateStart, 102)
AND CONVERT(DATETIME, @DateEnd, 102))
ORDER BY
dbo.FA_EventDetails.Event_DateTime DESC
END

When I pass in wildcard '%' for the 3 variables @EventType, @UserID, @EventRefNum
I get the following returned

Event_Ref_Number Event_Description Event_DateTime Event_Type User_ID
--------------------------------------------------------------------------
2345-999 NS-02-99 WEST SWITCH 21/08/2012 20:18 SYSTEM ADD SPELLM
123457 NS-02-99 EAST SWITCH 21/08/2012 21:18 SYSTEM ADD SPELLM
PC0010 SYSTEm 21/08/2012 21:19 SYSTEM ADD SPELLM

I want to serach using wild cards for the Event_Ref_Number so when I pass in
23% for Event_Ref_Number I get zero records returned? I can't figure out why as in
my stored procedure I am using LIKE.

I recreated the stored procedure as a view in SSMS and using the filter passed in the
above wild card and I got the following returned which is what I would expect

Event_Ref_Number Event_Description Event_DateTime Event_Type User_ID
--------------------------------------------------------------------------
2345-999 NS-02-99 WEST SWITCH 21/08/2012 20:18 SYSTEM ADD SPELLM

I am stumped as I am a newbie to SQL so help appreciated

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-30 : 15:29:00

Are you passing in 23% for Event_Ref_Number, and % for the other 2?

-Chad

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 15:34:30

You code itself looks fine from visual inspection. So you have to debug. I would start by just running these queries to see if they give expected results.

SELECT TOP 10 Event_Type FROM dbo.FA_EventType
WHERE Event_Type LIKE '2345-999'

SELECT TOP 10 Event_Type FROM dbo.FA_EventType
WHERE Event_Type LIKE '2345%'

If they work as expected, add the remaining where conditions, one at a time until you find what the issue is. I am suspecting some prefixed characters or something not quite right in the parameters you pass in.

mspelly
Starting Member

2 Posts

Posted - 2012-08-31 : 15:03:54

Silly me Stored Procedure rerferencing the wrong column for the variable @EventRefNum
Was
dbo.FA_EventDetails.User_ID LIKE @EventRefNum
Should be
dbo.FA_EventDetails.Event_Ref_Number LIKE @EventRefNum

thx for all replies

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 15:12:36

quote:
Originally posted by mspelly

Silly me Stored Procedure rerferencing the wrong column for the variable @EventRefNum
Was
dbo.FA_EventDetails.User_ID LIKE @EventRefNum
Should be
dbo.FA_EventDetails.Event_Ref_Number LIKE @EventRefNum

thx for all replies

Glad that you sorted it out yourself

No way anyone of use could find it out as we dont know about your system

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-31 : 15:32:51

quote:
Originally posted by visakh16

quote:
Originally posted by mspelly

Silly me Stored Procedure rerferencing the wrong column for the variable @EventRefNum
Was
dbo.FA_EventDetails.User_ID LIKE @EventRefNum
Should be
dbo.FA_EventDetails.Event_Ref_Number LIKE @EventRefNum

thx for all replies

Glad that you sorted it out yourself

No way anyone of use could find it out as we dont know about your system

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That's funny, I actually noticed it, and almost mentioned it, but thought "He must know what those columns are better than I do"

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources