Hello all,

i am writing a SP and want to do the next thing:

1. check whether a row exists by key field.
2. if not - raiserror
3. if does exist - return it.

the thing is that i dont want to scan the table twice with 2 select statements, and dont know how to do it....

any sugestions ??

thanks a lot, Ran.

sorry, did a mistake:
in step 3, i might want to return only one field of the row (in some cases).

thanks again, Ran.

SELECT col1, col2
FROM table1
WHERE col1 = '1'

IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('Exercise in futility.',16,1)
RETURN 0
END

declare @field_key your_data_type (..)
set @field_key = (select field_key from your_table
where col1 = '1' --don't know what this is
if @field_key is null raiserror ('your_error, 16, 1)
return @field_key

If you do it that way, then:

<i>in step 3, i might want to return only one field of the row (in some cases).</I>

you will only be able to return one row.