*Newbie* Evaluation of SP Parameters ....

You know when you stare at something for long enough, you start to question
if it's right or not?
This afternoon I've completely tied myself up trying to work out the
sequence in which parameters to a stored proc are evaluated, and could
really use someone to point out the obvious.
Basically I need to quickly retrieve a record based on 2 integer values
(foreign keys) and a string value. If my thinking is correct (') then the
SP will evaluate the first integer value, then the second, before trying to
evaluate the string. If I'm wrong, then the SP will try to evaluate all 3
simultaneously, and I know how slow evaluating a string can be.
The table the SP is querying is going to get pretty large (1 million records
+) and I'm AND'ing the parameters together in the WHERE clause for the SP.
I guess the question fundamentally is, am I OK doing the work in a single
SP, or should I first narrow the scope of the query EXEC'ing another SP
which uses the quick integer values, with the calling SP evaluating the
string value, albeit for a much smaller number of records?
Thanks for your time.
Andy"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:%23CWa4D0oFHA.1996@.TK2MSFTNGP10.phx.gbl...
> You know when you stare at something for long enough, you start to
> question if it's right or not?
> This afternoon I've completely tied myself up trying to work out the
> sequence in which parameters to a stored proc are evaluated, and could
> really use someone to point out the obvious.
> Basically I need to quickly retrieve a record based on 2 integer values
> (foreign keys) and a string value. If my thinking is correct (') then the
> SP will evaluate the first integer value, then the second, before trying
> to evaluate the string. If I'm wrong, then the SP will try to evaluate all
> 3 simultaneously, and I know how slow evaluating a string can be.
> The table the SP is querying is going to get pretty large (1 million
> records +) and I'm AND'ing the parameters together in the WHERE clause for
> the SP.
> I guess the question fundamentally is, am I OK doing the work in a single
> SP, or should I first narrow the scope of the query EXEC'ing another SP
> which uses the quick integer values, with the calling SP evaluating the
> string value, albeit for a much smaller number of records?
> Thanks for your time.
> Andy
>
Some DDL and the sproc in question would be very helpful. I'm not entirely
sure what you are asking for...
A guess:
CREATE PROC dbo.Foo
@.fk1 int,
@.fk2 int,
@.val varchar(100)
AS
BEGIN
SET NOCOUNT ON
IF (SELECT COUNT(*) FROM SomeTable WHERE @.fk1 = SomeTable.FK1 AND @.fk2 =
SomeTable.FK2) > 0
BEGIN
-- Evaluate your string
END
ELSE
BEGIN
RETURN -- No match found.
END
END|||TSQL does not guarantee an evaluation order of conditionals like the C
languages do. But it is very smart - it will narrow the number of records it
must search as much as possible, so don't try to do its work for it.
"Andrew Kidd" wrote:

> You know when you stare at something for long enough, you start to questio
n
> if it's right or not?
> This afternoon I've completely tied myself up trying to work out the
> sequence in which parameters to a stored proc are evaluated, and could
> really use someone to point out the obvious.
> Basically I need to quickly retrieve a record based on 2 integer values
> (foreign keys) and a string value. If my thinking is correct (') then the
> SP will evaluate the first integer value, then the second, before trying t
o
> evaluate the string. If I'm wrong, then the SP will try to evaluate all 3
> simultaneously, and I know how slow evaluating a string can be.
> The table the SP is querying is going to get pretty large (1 million recor
ds
> +) and I'm AND'ing the parameters together in the WHERE clause for the SP.
> I guess the question fundamentally is, am I OK doing the work in a single
> SP, or should I first narrow the scope of the query EXEC'ing another SP
> which uses the quick integer values, with the calling SP evaluating the
> string value, albeit for a much smaller number of records?
> Thanks for your time.
> Andy
>
>|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:e8mSdK0oFHA.3916@.TK2MSFTNGP12.phx.gbl...
> "Andrew Kidd" <nospam@.nospam.com> wrote in message
> news:%23CWa4D0oFHA.1996@.TK2MSFTNGP10.phx.gbl...
> Some DDL and the sproc in question would be very helpful. I'm not
> entirely sure what you are asking for...
> A guess:
> CREATE PROC dbo.Foo
> @.fk1 int,
> @.fk2 int,
> @.val varchar(100)
> AS
> BEGIN
> SET NOCOUNT ON
> IF (SELECT COUNT(*) FROM SomeTable WHERE @.fk1 = SomeTable.FK1 AND @.fk2
> = SomeTable.FK2) > 0
> BEGIN
> -- Evaluate your string
> END
> ELSE
> BEGIN
> RETURN -- No match found.
> END
> END
>
Yeah, sorry about not sticking in the SP, but to modify your example:
CREATE PROC dbo.Foo
@.fk1 int,
@.fk2 int,
@.val varchar(100)
AS
BEGIN
SET NOCOUNT ON
SELECT
SomeTable.PK
FROM
SomeTable
WHERE
SomeTable.FK1 = @.fk1
AND SomeTable.FK2 = @.fk2
AND SomeTable.SomeString = @.val
RETURN @.@.Rowcount
Hope this is more illustrative.|||I was hoping that this was the case, just looking for confirmation :-)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:DE01A461-DFEA-4B8A-9EA7-85E72106A217@.microsoft.com...
> TSQL does not guarantee an evaluation order of conditionals like the C
> languages do. But it is very smart - it will narrow the number of records
> it
> must search as much as possible, so don't try to do its work for it.
>
> "Andrew Kidd" wrote:
>