SQL组合查询的存储过程写法

最进一个项目 里面有个查询的功能,它是进行组合查询的, 而且用的是存储过程写。写这样的存储过程,需要注意单引号的使用,请看本人下面的例子,假如你以后写的话 记得注意写就行:

create proc selectbyname

(

@LinkmanName VARCHAR(50)=NULL,

@LinkmanBirthday VARCHAR(50)=NULL,

@LinkmanCatalog VARCHAR(50)=NULL,

@linkmanCompany VARCHAR(50)=NULL,

@LinkmanPhoneNumber VARCHAR(50)=NULL,

@LinkmanEmail VARCHAR(50)=NULL

)

as

declare @string VARCHAR(1000) //此处需要定义长一点那个string 因为你组合起来的话,整个语句加起来的字符就很多的了。

if ( LEN(@LinkmanName)>0)

begin

if @string IS NULL

set @string='select * from linkman where lm_name like '''+@LinkmanName+ '%'''

else

set @string=@string+' and lm_name like '''+@LinkmanName+ '%'''

end

if (LEN(@LinkmanBirthday)>0)

begin

if @string IS NULL

set @string=@string+' select * from linkman where lm_birthday='''+@LinkmanBirthday+''''

else

set @string=@string+' and lm_birthday='''+@LinkmanBirthday+''''

end

if ( LEN(@LinkmanCatalog)>0)

begin

if @string IS NULL

set @string=@string +' select * from linkman where lm_catalog like ''' + @LinkmanCatalog + '%'''

else

set @string=@string +' and lm_catalog like ''' + @LinkmanCatalog + '%'''

end

if (LEN(@linkmanCompany) >0 )

begin

if @string IS NULL

set @string=@string +' select * from linkman where lm_company like '''+ @linkmanCompany + '%'''

else

set @string=@string +' and lm_company like '''+ @linkmanCompany + '%'''

end

if ( LEN(@LinkmanPhoneNumber)>0)

begin

if @string IS NULL

set @string=@string +' select * from linkman where lm_mobile ='''+@LinkmanPhoneNumber+''''

else

set @string=@string +' and lm_mobile ='''+@LinkmanPhoneNumber+''''

end

if (LEN(@LinkmanEmail)>0)

begin

if @string IS NULL

set @string=@string +' select * from linkman where lm_email='''+@LinkmanEmail+''''

else

set @string=@string +' and lm_email='''+@LinkmanEmail+''''

end

if(@string is null)

begin

set @string='select * from linkman '

end

exec( @string)

?

程序界面:

SQL组合查询的存储过程写法 - 376735332 - 376735332的博客

?

确定按钮代码:

private void button1_Click(object sender, EventArgs e)

{

string strProName = "selectbyname";

string conn = DBUtility.SqlHelper.connString;

本文来源于网络,若有侵权,请告知,谢谢!!

上一篇:【leetcode❤python】141. Linked List Cycle


下一篇:SQL联合查询(内联、左联、右联、全联)的语法(转)