sql server programming:
How's dbo.Documents related to (SELECT *
FROM dbo.CommaListToIntArray(@DocTypes) AS lst_type
CROSS JOIN dbo.CommaListToIntArray(@DocGroups) AS
lst_group
WHERE lst_type.KeyValue IN (0, PD_DocType)
AND lst_group.KeyValue IN (0, PD_DocGroup))
Without specifying the filter/relationship, the Exists(...) will always
return true.
e.g.
select * from pubs..titles
where exists(select * from northwind..orders)
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net [quoted text, click to view] "Brandon Lilly" <brandon.lilly@nospam_medevolve.com> wrote in message
news:einfEazeDHA.2332@TK2MSFTNGP12.phx.gbl...
> I have the following query that returns the value 6:
>
> DECLARE @RangeStart int
> DECLARE @RangeEnd int
> DECLARE @DocTypes varchar(4000)
> DECLARE @DocGroups varchar(4000)
>
> SET @RangeStart = NULL
> SET @RangeEnd = NULL
> SET @DocTypes = ISNULL('2', '0')
> SET @DocGroups = ISNULL('0', '0')
>
> SELECT COUNT(*) AS Total
> FROM dbo.Documents
> WHERE (ISNULL(@RangeStart, @RangeEnd) IS NULL
> OR PD_Key BETWEEN @RangeStart AND @RangeEnd)
> AND EXISTS(SELECT *
> FROM dbo.CommaListToIntArray(@DocTypes) AS lst_type
> CROSS JOIN dbo.CommaListToIntArray(@DocGroups) AS
> lst_group
> WHERE lst_type.KeyValue IN (0, PD_DocType)
> AND lst_group.KeyValue IN (0, PD_DocGroup))
>
>
> Forgetting schemas and all, the CommaListToIntArray simply takes a
> comma delimited list of numbers and returns a table with the numbers.
> The number zero (0) is used to denote all document types or groups.
> Essentially, in this example, lst_type has a single row with KeyValue
> = 2, and lst_group has a single row with KeyValue = 0
>
> I know for a fact that there are exactly six rows in dbo.Documents
> that meet these criteria.
>
> I use TADODataSet components in Delphi. I don't know if that makes a
> difference, or if it is ADO in general, but it often wraps my raw
> queries in sp_executesql as follows:
>
> exec sp_executesql N'DECLARE @RangeStart int
> DECLARE @RangeEnd int
> DECLARE @DocTypes varchar(4000)
> DECLARE @DocGroups varchar(4000)
>
> SET @RangeStart = @P1
> SET @RangeEnd = @P2
> SET @DocTypes = ISNULL(@P3, ''0'')
> SET @DocGroups = ISNULL(@P4, ''0'')
>
> SELECT COUNT(*) AS Total
> FROM dbo.Documents
> WHERE (ISNULL(@RangeStart, @RangeEnd) IS NULL
> OR PD_Key BETWEEN @RangeStart AND @RangeEnd)
> AND EXISTS(SELECT *
> FROM dbo.CommaListToIntArray(@DocTypes) AS lst_type
> CROSS JOIN dbo.CommaListToIntArray(@DocGroups) AS
> lst_group
> WHERE lst_type.KeyValue IN (0, PD_DocType)
> AND lst_group.KeyValue IN (0, PD_DocGroup))', N'@P1
> int,@P2 int,@P3 varchar(4000),@P4 varchar(4000)', NULL, NULL, '2,',
> '0'
>
>
> Note that the parameters are the same (if they aren't I'll eat my
> shoe), but instead of getting the correct value of "6", I get over
> 10000 rows, which happens to be the number of rows in dbo.Documents.
> I cannot figure out what is happening here that is taking my working
> query and nerfing it all up so that my results are wrong....
>
> Ideas offhand without me having to supply all of my bare naked
> schemas?
>
> Thanks,
>
> Brandon
> --
> "In the beginning the universe was created. This has made a lot of
> people very angry, and has been widely regarded as a bad move." -
> Douglas Noel Adams (1952-2001)
>
>
