SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure Not Working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mspelly
Starting Member

2 Posts

Posted - 08/30/2012 :  15:14:49  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 08/30/2012 :  15:29:00  Show Profile  Visit chadmat's Homepage  Reply with Quote
Are you passing in 23% for Event_Ref_Number, and % for the other 2?

-Chad
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/30/2012 :  15:34:30  Show Profile  Reply with Quote
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.
Go to Top of Page

mspelly
Starting Member

2 Posts

Posted - 08/31/2012 :  15:03:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/31/2012 :  15:12:36  Show Profile  Reply with Quote
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/

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 08/31/2012 :  15:32:51  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000