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]
GOEdward (teddysnips@.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/array...-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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||teddysnips@.hotmail.com (Edward) wrote in message news:<25080b60.0312020231.449732c@.posting.google.com>...
> 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
