在SqlServer中的where，Orderby，Like中使用变量 [英] use variables in where,Orderby,Like in SqlServer

问题描述

如何在where，Orderby Classes&中使用变量像SqlServer 2005中的行为一样

我在以下几行中提到了我的代码..但MyCode不返回任何值

How To use variables in where,Orderby Classes & Like Conduction in SqlServer 2005

i mention my code in below lines.. but MyCode Not Returning any Values

@target varchar(30),
@prefixText varchar(100)

As
Begin


SELECT Top 10 ad.Doctor_Code, ad.DoctorName, Admin_Doctor_Timing.AvailabilityStatus, Admin_Department.Department_Name,Admin_Company_Master.CompanyOrBranchName AS Branch FROM Admin_Department INNER JOIN Admin_Doctor AS ad 
	ON Admin_Department.Department_Code = ad.Department INNER JOIN 
	Admin_Company_Master ON ad.Branch = Admin_Company_Master.Branch_Code LEFT OUTER JOIN 
	Admin_Doctor_Timing ON ad.Doctor_Code = Admin_Doctor_Timing.Doctor_Code 
	where @target Like @prefixText +''%'' ORDER BY DoctorName Asc


End 

End 

推荐答案

在where条件下，使用列名.您不能使用变量来改变列.所以这个:

In the where condition, use column names. You cannot vary a column using a variable. So this:

where @target Like @prefixText +'%' ORDER BY DoctorName Asc

应该像

should be like

where columnname Like @prefixText ORDER BY DoctorName Asc

另外，根据变量prefixtext 的位置，将％添加到变量的末尾或让用户添加它，不要直接将其放在语句中.因此，在选择之前，您可能需要输入以下内容:

Also depending where the variable prefixtext is coming, add the % to the end of the variable or let the user add it, don''t put it in the statement directly. So before the select you could have something like:

set @prefixtext = @prefixtext + '%'

如果要使用变量来改变语句，则应将SQL文本放入字符串变量中，然后使用目标变量添加相关列.喜欢:

If you want to vary the statement using variables you should put the SQL text into a string variable and then use the target variable to add relevant column. Like:

SET @sqlText = 'SELECT TOP...WKERE ' @target + ' LIKE...'

然后使用执行 [

And then use EXECUTE[^] to run the statement.

现在我从Mika那里得到了解决方案温德留斯&我现在应用了他的建议，效果很好

我的代码:

Now i got solution from Mika Wendelius & i applyed his suggestion now it''s working fine

my code :

 (
@target varchar(30),
@prefixText varchar(100)
)

as
begin
	declare @sqry as varchar(1000)
set @sqry=''SELECT Top 10 ad.Doctor_Code, ad.DoctorName, Admin_Doctor_Timing.AvailabilityStatus, Admin_Department.Department_Name,Admin_Company_Master.CompanyOrBranchName AS Branch FROM Admin_Department INNER JOIN Admin_Doctor AS ad 
	ON Admin_Department.Department_Code = ad.Department INNER JOIN 
	Admin_Company_Master ON ad.Branch = Admin_Company_Master.Branch_Code LEFT OUTER JOIN 
	Admin_Doctor_Timing ON ad.Doctor_Code = Admin_Doctor_Timing.Doctor_Code 
	where ''  + @target+ '' Like '''''' +@prefixText +''%'''' ORDER BY '' + @target+ '' Asc''
--print @Sqry
Exec(@sqry) 
end

这篇关于在SqlServer中的where，Orderby，Like中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！