QA returns different records from SQL Server EM

[Edward]

SQL Server 7.0

If I run the following in Query Analyzer I get no records returned:

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'

If, however, I run either :

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'

or

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'

I get a single record returned in each case (which is what I would
expect).

The SQL for GetLeadsOutcome_Dealer is:

CREATE PROCEDURE GetLeadsOutcome_Dealer
@FromDate smalldatetime,
@ToDate smalldatetime,
@OutcomeTypeID integer,
@DealerCode varchar(8000)
AS
DECLARE @TotalLeads integer

BEGIN
SELECT @TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE
(tblLeads.fldDealerID IS NOT NULL)
SELECT DISTINCT (dbo.tblDealers.fldDealerName),
COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@TotalLeads AS TotalLeads
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (((dbo.tblLeads.fldEntered) BETWEEN @FromDate And @ToDate)) AND
tblOutcome.fldOutcomeID=@OutcomeTypeID AND
(dbo.tblDealers.fldDealerCode in (@DealerCode))
GROUP BY dbo.tblDealers.fldDealerName
ORDER BY dbo.tblDealers.fldDealerName

END
GO

However, if I open EM, open a table in query view, and paste this into
the SQL window,

SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) AS
LeadCount
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND
(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,
183))
GROUP BY tblDealers.fldDealerName
ORDER BY tblDealers.fldDealerName

I get two records returned.

What is happening?

TIA

Edward

TABLE DEFS:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealers
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINT
FK_tblSystemUsers_tblDealers
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINT
FK_tblLeadNotes_tblLeads
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegion
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblDealers]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblLeads]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOutcome]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblRegion]
GO

CREATE TABLE [dbo].[tblDealers] (
[fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDealerCode] [varchar] (50) NOT NULL ,
[fldDealerName] [varchar] (50) NULL ,
[fldDealerTel] [varchar] (20) NULL ,
[fldDealerEmail] [varchar] (100) NULL ,
[fldDealerContact] [varchar] (50) NULL ,
[fldRegionID] [int] NULL ,
[fldDealerActive] [smallint] NOT NULL ,
[fldHeadOffice] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLeads] (
[fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,
[fldAccountNo] [varchar] (50) NULL ,
[fldDealerID] [int] NULL ,
[fldStatusID] [int] NOT NULL ,
[fldOutcomeID] [int] NOT NULL ,
[fldContacted] [smallint] NOT NULL ,
[fldDateContacted] [smalldatetime] NULL ,
[fldAppointment] [smallint] NULL ,
[fldShowRoom] [smallint] NOT NULL ,
[fldTestDrive] [smallint] NOT NULL ,
[fldSalesPersonID] [int] NULL ,
[fldCustomerName] [varchar] (50) NULL ,
[fldCHouseNo] [varchar] (50) NULL ,
[fldCStreet] [varchar] (50) NULL ,
[fldCDistrict] [varchar] (50) NULL ,
[fldCTown] [varchar] (40) NULL ,
[fldCCounty] [varchar] (50) NULL ,
[fldCPostcode] [varchar] (50) NULL ,
[fldPhoneInd] [varchar] (20) NULL ,
[fldCTel] [varchar] (50) NULL ,
[fldNewBusinessDate] [smalldatetime] NULL ,
[fldAgreementType] [varchar] (50) NULL ,
[fldCashPrice] [smallmoney] NULL ,
[fldBalanceFin] [smallmoney] NULL ,
[fldCustRate] [varchar] (10) NULL ,
[fldOrigTerm] [int] NULL ,
[fldPPPType] [varchar] (10) NULL ,
[fldBalloonValue] [smallmoney] NULL ,
[fldMonthlyInstal] [smallmoney] NULL ,
[fldRegNo] [varchar] (10) NULL ,
[fldRegDate] [smalldatetime] NULL ,
[fldModel] [varchar] (50) NULL ,
[fldDescription] [varchar] (50) NULL ,
[fldTheoPIFDate] [smalldatetime] NULL ,
[fldMonthsToGo] [int] NULL ,
[fldBalanceOS] [smallmoney] NULL ,
[fldLeadPrinted] [smallint] NULL ,
[fldMergeFile] [varchar] (255) NULL ,
[fldTPS] [varchar] (10) NULL ,
[fldMPS] [varchar] (10) NULL ,
[fldUpdated] [smalldatetime] NULL ,
[fldUpdatedBy] [int] NULL ,
[fldEntered] [smalldatetime] NULL ,
[fldReasonLeadNotProgressed] [varchar] (5000) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblOutcome] (
[fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,
[fldOutcomeCode] [int] NULL ,
[fldOutcome] [varchar] (100) NULL ,
[fldConvertedSales] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRegion] (
[fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,
[fldRegionCode] [varchar] (10) NULL ,
[fldRegion] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED
(
[fldDealerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED
(
[fldLeadID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED
(
[fldOutcomeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED
(
[fldRegionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR
[fldDealerActive],
CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR
[fldHeadOffice],
CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED
(
[fldDealerCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR
[fldOutcomeID],
CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],
CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR
[fldAppointment],
CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],
CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],
CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR
[fldLeadPrinted],
CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],
CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]
GO

ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR
[fldConvertedSales]
GO

ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED
(
[fldRegionCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] ADD
CONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY
(
[fldRegionID]
) REFERENCES [dbo].[tblRegion] (
[fldRegionID]
)
GO

ALTER TABLE [dbo].[tblLeads] ADD
CONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY
(
[fldDealerID]
) REFERENCES [dbo].[tblDealers] (
[fldDealerID]
)
GO

alter table [dbo].[tblLeads] nocheck constraint
[FK_tblLeads_tblDealers]
GO

[Erland Sommarskog]

Edward (te********@hotmail.com) writes:

If I run the following in Query Analyzer I get no records returned:

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'
...
The SQL for GetLeadsOutcome_Dealer is:
...
(dbo.tblDealers.fldDealerCode in (@DealerCode))
Apparently, you dont have any dealer with the code '176, 183'.
However, if I open EM, open a table in query view, and paste this into
the SQL window,
...
(tblDealers.fldDealerCode IN (176, 183))

Here you have two different values. In the procedure you have only one.

Looks like you need a procedure that unpacks a list of values into
a table. Look at
http://www.algonet.se/~sommar/arrays...of-string-proc.
(This brings you to a procedure which is for SQL7. But you have have
to read some other parts of the long text to make sense of it.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

te********@hotmail.com (Edward) wrote in message news:<25*************************@posting.google.c om>...

SQL Server 7.0

If I run the following in Query Analyzer I get no records returned:

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'

If, however, I run either :

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'

or

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'

I get a single record returned in each case (which is what I would
expect).

<snip>

Your first SQL statement essentially comes down to this:

select * from table where column in ('183, 176')

In other words, return the columns with a value of '183, 176'. Since
there aren't any, you get no rows. What you are trying to achieve is
this - return columns with a value of '183' or '176':

select * from table where column in ('183', '176')

When you use a single value for the variable, you get a valid query,
and hence a single result:

select * from table where column in ('183')

You're getting results in EM because you've correctly typed in the
values manually.
See here for a fuller discussion:

http://www.algonet.se/~sommar/dynamic_sql.html#List

Simon