how to validate the input parameters before using in the static query?? SQL server 2005
consider table开发者_高级运维1 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
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 )
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.
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
精彩评论