MSSQL Variable Where clause

[Ianvn]

I have a address table in SQL with 2 possible fields that I want to query it on using a stored procedure.

address_id AND link_id

SELECT * FROM address WHERE address_id = @ADDRESS_ID AND link_id = @LINK_ID

My problem comes in that @ADDRESS_ID OR @LINK_ID can be passed as -1,
and then should not form part of the query.

I know I can do this by building up the query string and then executing it,
but there should be a way of doing this using either inline if statements of case statements?

 

[Mr.Jax]

Easy peasy.

where ( @ADDRESS_ID = -1 OR address_id = @ADDRESS_ID) AND (@LINK_ID = -1 OR link_id = @LINK_ID)

 

[Nerfherder]

address_id = CASE WHEN @ADDRESS_ID > 0 THEN address_id ELSE @ADDRESS_ID END


That should work for the one case


EDIT: Swapped around

 

[xrapidx]

Easy peasy.

where ( @ADDRESS_ID = -1 OR address_id = @ADDRESS_ID) AND (@LINK_ID = -1 OR link_id = @LINK_ID)

this.

 

[Ianvn]

Easy peasy.

where ( @ADDRESS_ID = -1 OR address_id = @ADDRESS_ID) AND (@LINK_ID = -1 OR link_id = @LINK_ID)

Awesome, thanks for the help.
Works perfectly.

 

[_kabal_]

I question the fact that there is a field called "LINK_ID" in an address table

 

[semaphore]

I question the fact that there is a field called "LINK_ID" in an address table

Yeah who the hell uses _ in field naming conventions.

 

[xrapidx]

 

[Compton_effect]

Do a If statement at the start of the stored procedure where you check if @ADDRESS = -1. If so set it to NULL.
And then in your logic 'WHERE Address_Id = ISNULL(@ADDRESS,Address_Id'.

Also gives you the option to handle NULLS neatly. The only time it has issues is if the Address_Id field can be nullable.

 

[shauntir]

Do a If statement at the start of the stored procedure where you check if @ADDRESS = -1. If so set it to NULL.
And then in your logic 'WHERE Address_Id = ISNULL(@ADDRESS,Address_Id'.

Also gives you the option to handle NULLS neatly. The only time it has issues is if the Address_Id field can be nullable.

This is the solution I also use.