Trouble with where clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble with where clause

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-11 : 23:06:45

I have five db's in this example and need to return the unique siteid etc from each DB. The following code is returning all four siteid when I select any of the DB in @DBName. The first three siteid shown below are in all DB's and the fourth is only in the first db.

USE [VC]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Sites_with_no_Events_AD]
@DBName = N'vca1'

SELECT 'Return Value' = @return_value

1760 Alix FastVU
3213 James FastTX
1764 Jazz FastVU
3238 VC No Events FastTrace

Because in this case I have selected VCA1 as the @DBName I should only get the first three from DB 2. If I select VC as the DBName I should get all four. The AND does not seem to be working in the where clause, where is my syntax incorrect please?

Declare @sql nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType
FROM SiteDetails CROSS JOIN
EventDetails
WHERE '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','','')) AND
(NOT (SiteDetails.SiteID IN (SELECT SiteID FROM EventDetails)))
' from sys.databases where name='VC' or name like 'VCA%'

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-11 : 23:54:10

There seems to be a lot of issues.

1. Probably not a good spot to use Dynamic Sql
2. Query does not appear to be written properly from a optimization standpoint
3. Looks like the wrong methodolgy.
4. Query is returning the wrong results

Let's start with a clean slate, and please post some sample data and desired results (Please also include some sample data for your table function fn_MVParam as well. This way we can fully test.

Thanks

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

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-12 : 00:12:44

I have five DB called VC, VCA1, VCA2, VCA3, VCA4 in this example. (this could be 1 to x).
The function is a parser for the multivalued parameter from reporting services.

I have two tables called SiteDetails and EventDetails (the same in each DB). SiteDetails has SiteID as key and EventDetails has SiteID as a field. I am tring to return the SiteDetails.SiteId where there are no matching EventDetails.SiteId. Ie the site(s) has no events.

The data in the databases where the sitedetails.siteid has no matching eventdetails.siteid is as follows, typically for most sitedetails.siteid it will have one or more linked eventdetails.siteid:
VC
SiteID SiteName TransmitterType
1760 Alix FastVU
3213 James FastTX
1764 Jazz FastVU
3238 VC No Events FastTrace

VCA1 to VCA4
SiteID SiteName TransmitterType
1760 Alix FastVU
3213 James FastTX
1764 Jazz FastVU

The only input parameter is the database name as @DBName, at the moment I am running the store proc from management studio to test but will use the stored proc finally from reporting services.

The desired result is:
@DBName = VC
SiteID SiteName TransmitterType
1760 Alix FastVU
3213 James FastTX
1764 Jazz FastVU
3238 VC No Events FastTrace

@DBName = VCA1
SiteID SiteName TransmitterType
1760 Alix FastVU
3213 James FastTX
1764 Jazz FastVU

@DBName = VC, VCA1
SiteID SiteName TransmitterType
1760 Alix FastVU
3213 James FastTX
1764 Jazz FastVU
3238 VC No Events FastTrace

USE [VC]
GO
/****** Object: Table [dbo].[SiteDetails] Script Date: 09/12/2008 15:10:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SiteDetails](
[SiteID] [int] IDENTITY(1,1) NOT NULL,
[DeviceID] [nvarchar](50) NOT NULL,
[SiteName] [nvarchar](50) NOT NULL,
[TransmitterType] [nvarchar](50) NOT NULL,
[SaveLiveToDB] [bit] NOT NULL,
[StayOnlinePriority] [int] NOT NULL,
[DownloadImages] [bit] NOT NULL,
[ImageDwnldStrategy] [nvarchar](20) NULL,
[SitePulseEnable] [bit] NOT NULL,
[NotifyIfSiteNotHeardFrom] [int] NULL,
[AllOKPassword] [nvarchar](50) NOT NULL,
[NotificationUserField] [nvarchar](max) NULL,
[ContactUserField] [nvarchar](max) NULL,
[LocationUserField] [nvarchar](max) NULL,
[ContactCompany] [nvarchar](255) NULL,
[ContactName] [nvarchar](255) NULL,
[ContactPhone] [nvarchar](255) NULL,
[ContactFax] [nvarchar](255) NULL,
[ContactMobile] [nvarchar](255) NULL,
[ContactEmail] [nvarchar](max) NULL,
[LocationAddress] [nvarchar](max) NULL,
[LocationCity] [nvarchar](max) NULL,
[LocationState] [nvarchar](max) NULL,
[LocationZip] [nvarchar](max) NULL,
[LocationMapReference] [nvarchar](max) NULL,
[LocationDescription] [nvarchar](max) NULL,
[LocationTimeZone] [nvarchar](20) NULL,
[InteractiveDiagramFileName] [nvarchar](max) NULL,
[Isdeleted] [bit] NOT NULL

GO
/****** Object: Table [dbo].[EventDetails] Script Date: 09/12/2008 15:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventDetails](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[CameraNumber] [int] NULL,
[EventType] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NULL,
[Priority] [int] NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Cause] [nvarchar](max) NULL,
[SourceID] [int] NULL,
[SourceType] [nvarchar](255) NULL,
[ACKFlag] [tinyint] NOT NULL CONSTRAINT [DF_EventDetails_ACKFlag] DEFAULT ((0)),
[FullyACKed] [bit] NOT NULL,
[IsInCurrentDB] [bit] NOT NULL CONSTRAINT [DF_EventDetails_IsInCurrentDB] DEFAULT ((1)),
[EventStartTime] [datetime] NOT NULL,
[EventEndTime] [datetime] NULL,
[SiteID] [int] NOT NULL,
[StateType] [nvarchar](50) NULL,
[StateData] [varbinary](max) NULL

Here is a print of the current query execution, I cannot see why the where clause is not working for the parameter?

SELECT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType
FROM SiteDetails CROSS JOIN
EventDetails
WHERE 'VC' IN ('vca1',',') AND
(NOT (SiteDetails.SiteID IN (SELECT SiteID FROM EventDetails)))
SELECT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType
FROM SiteDetails CROSS JOIN
EventDetails
WHERE 'VCA1' IN ('vca1',',') AND
(NOT (SiteDetails.SiteID IN (SELECT SiteID FROM EventDetails)))
SELECT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType
FROM SiteDetails CROSS JOIN
EventDetails
WHERE 'VCA2' IN ('vca1',',') AND
(NOT (SiteDetails.SiteID IN (SELECT SiteID FROM EventDetails)))
SELECT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType
FROM SiteDetails CROSS JOIN
EventDetails
WHERE 'VCA3' IN ('vca1',',') AND
(NOT (SiteDetails.SiteID IN (SELECT SiteID FROM EventDetails)))
SELECT SiteDetails.SiteID, SiteDetails.SiteName, SiteDetails.TransmitterType
FROM SiteDetails CROSS JOIN
EventDetails
WHERE 'VCA4' IN ('vca1',',') AND
(NOT (SiteDetails.SiteID IN (SELECT SiteID FROM EventDetails)))

(1 row(s) affected)

Hopefully that is everything?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources