CODEWITHSUNDEEP
sqlparameters
John
John

Reputation: 13

SQL Stored Procedure to handle several null parameters?

On SQL Server I am trying to be able to select * records from a table with four parameters, in some cases the incoming parameter will be a certain string value 'Select', in that case I would like to select * from the specified field.

Does anyone know of a good way to handle this?

(
 @mParameter varchar(50) = Null,
 @tParameter varchar(50) = Null,
 @gParameter int = Null,
 @nParameter varchar(255) = Null
)

as



Select
  *

From
  TableA

Where

If @mParameter = 'Select' then
  M = @mParameter


If @tParameter = 'Select' then
AND
  T = @tParameter

If @gParameter = 'Select'then
AND
  G = @gParameter

If @nParameter = 'Select' then
AND
  N = @nParameter

Upvotes: 0

Views: 2053

Answers (2)

Kashif
Kashif

Reputation: 14440

SELECT *
FROM YourTable
WHERE (@mParameter = 'M' OR @mParameter IS NULL)
AND (@tParameter = 'T' OR @tParameter IS NULL)
AND (@gParameter = 123 OR @gParameter IS NULL)
AND (@nParameter = 'N' OR @nParameter IS NULL)

Upvotes: 2

Denis Valeev
Denis Valeev

Reputation: 6015

Update 2

Try this:

Select *
From TableA
Where
M = isnull(@mParameter, M)
and T = isnull(@tParameter, T)
and G = isnull(@gParameter, G)
and N = isnull(@nParameter, N)

Upvotes: 3

Related Questions