how to validate the input parameters before using in the static query?? SQL server 2005

Question

consider table1 with 2 columns..

table1: 
 column1 int,
 column2 char

create procedure SP1(@col1,@col2) as
begin
    select * from table1 where _______ 
end

Question: User may enter valid input for either (col1 or col2) or (both col1 and col2).so i need to validate the user input and use those correct column(s) in the satic query.

eg: if both inputs are correct then, the query will be:

select * from table1 where column1=@col1 and column2 =@col2

if only col2 is valid and col1 is not a valida one, then this:

select * from table1 where column2=@col2 

how to validate the input parameters before using in the static query?? in sql server 2005

Answer 1

You mean something like:

Create Procedure Sp1( @Col1..., @Col2... )
As

-- if @Col1 is not valid, then set it to Null
If @Col1 <> <valid number or string or date> 
    Set @Col1 = Null

-- if @Col2 is not valid, then set it to Null
If @Col2 <> <valid number or string or date>
    Set @Col2 = Null

Select ...
From Table1
Where ( @Col1 Is Not Null Or @Col2 Is Not Null )
    And ( Col1 = @Col1 Or @Col1 Is Null )
    And ( Col2 = @Col2 Or @Col2 Is Null )

Answer 2

try this:

Create Procedure Sp1( @Col1..., @Col2... )
As

If @Col1 {is valid} AND @Col2 {is valid} 
BEGIN
    select * from dbo.table1 where column1=@col1 and column2 =@col2
END
ELSE @Col2 {is valid} 
BEGIN
    select * from dbo.table1 where column2=@col2 
END

RETURN 0
GO

be careful using the (@col1 IS NULL or @Col1=Col1) trick, an index will not be used. Read Dynamic Search Conditions in T-SQL by Erland Sommarskog to see all the PROs and CONs of each dynamic search method. I chose the If method because the OP only lists 2 conditions to search on, so it would seem feasible to do it this way.

Answer 3

In such case it looks like dynamic SQL will be the best option - you will generate the WHERE clause depending on the validity of arguments and then execute the whole query with sp_executesql