Parameterized where clause

Hello,
I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data. So it looks like:
create procedure ..
..
set transaction isolation level serializable
begin transaction
declare @.ID int
select @.ID = rowid from tblBusinessInformation where Name = @.Name and Rules = @.Rules
if ( @.ID is NULL )
begin
insert into tblBusinessInformation (..) values (@.Name, @.Rules)
end
commit transaction
The problem is the values could be:
Name Rules
NULL 'Test'
'Test' NULL
'Test' 'Test'
When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @.Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...
Thanks.perhaps you can use the CASE stmt. So what xactly is your condition..you want to allow NULLs in both columns or select an ID if either Name or Rules is NULL ? If you can state what conditions you are trying to match we can help you with the SQL.|||

Hello,
Well, what I need to do is ensure uniqueness in the table, for the name/rules pairing, so for example, the proc searches for a @.name value of "bob" and a @.rules value of "must be a bob", this must be unique in the table. It isn't the primary key; I use an identity value for the key, so I have three fields, the ID, name, and rules. Now the @.names can be null or the @.rules can be null, but not both.
The problem is, with this query:
declare @.ID int
select @.ID = rowid from tblBusinessInformation where Name = @.Name and Rules = @.Rules
if @.Name is null and @.Rules has a value, it doesn't find the ID; however, for Name, when I change "Name = @.Name" to "Name is @.Name", then I find that it works; however, I can't use is because whenever @.Name isn't null, then that causes a problem. I thought this wasn't supposed to be the case.
I can use the CLR, but I was trying to avoid it because of all the code necessary for a simpler statement. Any ideas how to avoid this? As an alternative, I can try putting in a blank space for when it is null and then use the ISNULL function, but I would like to use the NULL value.
Thanks for looking at this.

|||

I dont think I completely understood but I will take a guess. You could do something like :

SELECT
@.ID = rowid
FROM
tblBusinessInformation
WHERE
ISNULL(Name,'') = ISNULL(@.Name,'') AND ISNULL(Rules,'') = ISNULL(@.Rules,'')