Sergey,

Thanks a lot for your help. Your suggestion to use case statements worked really well.

My project manager asked me to also try a series of nested if statements, thinking that the server would cache the selects and we would get better performance. We found that the performance of the two was comparable. The nested if statements resulted in a shorter, sharper burst of CPU usage, while the case statement used about a fourth less processor time, but for about a third longer.

Do you know of any set of basic rules for optimizing stored procedure performance on Microsoft SQL 2000 aside from experiments and experience? We are starting to use a lot of stored procedures for our current project and I would like to write the most resource-efficient stored procedures that I can. Even knowing the ways in which SQL Server 2000 optimized queries would be useful.

I am reading "SQL Server 2000 Stored Procedure & XML Programming, Second Edition" by Dejan Sunderic, which I like as a resource. If you could share recommendations for other print or web resources, I would appreciate it.

Thanks again.

>Hi David,
>
>T-SQL doesn't have logical data type. You'll have to use numeric or character.

DECLARE @found int
>SET @found = CASE
>	WHEN EXISTS  (SELECT * FROM table1  WHERE field1 = myValue) THEN 1
>	WHEN EXISTS  (SELECT * FROM table2  WHERE field2 = myValue) THEN 1
>	...
>	ELSE 0 END
>

Take a look also at Re: Can stored procedures return values? Message #876903
>
>>I am not experienced with SQL, but I have a basic understanding.
>>
>>I want to determine if a value exists in any of a set of tables. I want to stop after I find the first instance. The pseudocode for a simplified case would be,
>>
>

IF EXISTS
>>  (SELECT TOP 1 field1
>>   FROM table1
>>   WHERE field1 = myValue)
>>THEN
>>   RETURN true
>>ELSE IF EXISTS
>>  (SELECT TOP 1 field2
>>   FROM table2
>>   WHERE field2 = myValue)
>>THEN
>>  RETURN true
>>ELSE
>>  RETURN false
>>END IF

I eventually need this to be in a stored procedure. I have to believe that only querying the minimum necessary tables is more efficient than unioning a couple the separate queries.