SQL LIKE % FOR INTEGERS

Question

In T-SQL, how do I write the query to select rows for any integer value for a column??

For example, the data is like this

NAME,AGE
A,10
B,20
C,10
D,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??

My code is as follows,

select ...where (AGE = @AGE)

<SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="AGE" PropertyName="SelectedValue" DbType="Int32" DefaultValue="ANY"

Also, the follow query works perfectly in the SSMS, but how to implement this behavior in asp.net SqlDataSource??

SELECT * FROM [TABLE] where AGE is not null

If the column AGE was of varchar type, I am able to use the '%', but it's an numeric field

Thanks,

Answer 1

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: 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

See this link for more details about dynamic SQL in TSQL/SQL Server.

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.