sql server 2005进行搜索条件 [英] sql server 2005 making search condition

问题描述

嗨
我需要使用四个参数名称，序列号，全部，出生日期进行搜索条件.

我正在这样使用:

if(@searchtype==''Name'')
begin

select * from tbluser where Name=@name
end

if(@searchtype==''serialnumber'')
begin
select * from tbluser where serialnumber=@serialnumber
end
.....

我已经两次使用了tbluser中的select *，所以我需要使用一次此查询，并在需要使用条件的地方使用上述参数..

解决方案

可以使用存储过程，将Search Type和Search Value作为参数传递给该存储过程，如下所示

 创建 过程 dbo.SearchData( NVARCHAR ( @SearchValue   NVARCHAR ( 100 ))
 AS 
开始

 DECLARE   @ FilterExp   NVARCHAR ( 250 )
 SET   @ FilterExp  =  Case   @ SearchType 
    何时 ' 名称'  THEN  '  WHERE Name =''' +  @ SearchValue  + ' '''
    何时 '  SerialNumber'  THEN  '  WHERE SerialNumber =''' +  @ SearchValue  + ' '''
    何时 ' 全部'  THEN  ' '
     END 
执行('  SELECT * FROM tbluser' +  @ FilterExp )
 END  

然后打电话给

  EXEC  dbo.SearchData ' 名称'，'  UserName' 

用作

 开始
 
选择 * 来自 tbluser 其中名称= @名称或 serialnumber = @序列号
结束 

hi
i need to make search condition with four parameter Name, serial number, all,date of birth.

i am using like this :

if(@searchtype==''Name'')
begin

select * from tbluser where Name=@name
end

if(@searchtype==''serialnumber'')
begin
select * from tbluser where serialnumber=@serialnumber
end
.....

i have use select * from tbluser two time, so i need use this query one time, and use above parameter in where condition how can i do this..

解决方案

A stored procedure can be used to which the Search Type and Search Value are passed as arguments as shown below

CREATE PROCEDURE dbo.SearchData (@SearchType NVARCHAR(100), @SearchValue NVARCHAR(100))
AS
BEGIN

DECLARE @FilterExp NVARCHAR(250)
SET @FilterExp = CASE @SearchType
    WHEN 'Name' THEN 'WHERE Name = ''' + @SearchValue + ''''
    WHEN 'SerialNumber' THEN 'WHERE SerialNumber = '''+ @SearchValue + ''''
    WHEN 'All' THEN ' '
    END
Exec( 'SELECT * FROM tbluser ' + @FilterExp )
END

Then to call

EXEC dbo.SearchData 'Name','UserName'

Use as

begin
 
select * from tbluser where Name=@name or serialnumber=@serialnumber
end

这篇关于sql server 2005进行搜索条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！