SQL LIKE%整数 [英] SQL LIKE % FOR INTEGERS
问题描述
在T-SQL,我怎么写的查询来选择一列??
In T-SQL, how do I write the query to select rows for any integer value for a column??
例如,该数据是这样的
NAME,AGE
A,10
B,20
C,10
D,20
和有一个< ASP:DropDownList的>
有两个选项, 10,20
,使用户可以选择 10
或 20
。如果用户选择 10
或 20
,该数据被正确地拉出来,但我怎么说 *
状态? - 喜欢选择的所有数据在年龄的任何值
列??
and There's a <asp:dropdownlist>
that has two options, 10,20
, so that a user can select either 10
or 20
. If the user selects 10
or 20
, The data is being pulled correctly, but how do I say a *
condition?? - like select ALL data for ANY value in the age
column??
我的code是如下,
选择...在哪里(年龄= @AGE)
&LT; SelectParameters&GT;
&LT; ASP:ControlParameter控件ID =DropDownList1NAME =年龄属性名=的SelectedValue
的DbType =的Int32默认值=ANY
此外,后续查询工作完全在SSMS,但如何实现asp.net SqlDataSource的这种行为?
Also, the follow query works perfectly in the SSMS, but how to implement this behavior in asp.net SqlDataSource??
SELECT * FROM [TABLE]其中年龄不是空
如果列年龄为VARCHAR类型的,我能够使用'%',但它是一个数字字段
If the column AGE was of varchar type, I am able to use the '%', but it's an numeric field
谢谢,
推荐答案
要提供ALL / ANY选项,您需要指定一个哨兵值 - 一个值,将永远在你的数据存在 - 这样你就可以检查为了提交给存储过程变量知道什么时候忽略变量并使用正确的WHERE子句。
To provide an ALL/ANY option, you need to specify a sentinel value -- a value that will never exist in your dataset -- so you can check the variable submitted to the stored procedure in order to know when to ignore the variable and use the correct WHERE clause.
IE:如果下拉列表中具有的全部显示文本的元素,和值-1,下面的动态SQL将是适当的:
IE: If the drop down list has an element with the display text of "All", and a value of -1, the following dynamic SQL would be appropriate:
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT *
FROM [YOUR_TABLE]
WHERE 1 = 1 '
SET @SQL = @SQL + CASE
WHEN @age > 0 THEN
' AND age = @age '
ELSE
' AND age IS NOT NULL '
END
BEGIN
EXEC sp_executesql @SQL N'age INT', @age
END
请参阅此链接,获取有关TSQL / SQL服务器动态SQL的更多详细信息。
See this link for more details about dynamic SQL in TSQL/SQL Server.
但你不必使用动态SQL - 这相当于:
But you don't have to use dynamic SQL - this is equivalent:
IF @age > 0
BEGIN
SELECT *
FROM [YOUR_TABLE]
WHERE age IS NOT NULL
END
ELSE
BEGIN
SELECT *
FROM [YOUR_TABLE]
WHERE age = @age
END
......只是你可以想像这个笨拙得到,如果你有一个相互独立的多个参数。
...just that you can imagine how unwieldy this gets if you have multiple parameters that are independent of one another.
这篇关于SQL LIKE%整数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!