December 26, 2007 at 12:30 pm
Ok, this is probably really simple, and maybe the holidays just has my brain all out of whack. So here is my question.
How do I search a numerical column, such as an Int based column, for any value in it. I know with string based columns you can just do
WHERE strColumn LIKE '%'
but what's the equivalent for an int Column? Since
WHERE intColumn LIKE '%' doesn't work
December 26, 2007 at 12:50 pm
SELECT *
FROM CAFEEMPLOYEE
WHERE CONVERT(CHAR(8),COLUMN) LIKE '1%'
December 26, 2007 at 12:52 pm
INT are searched as INTEGERS in SQL server. You will typically filter by range or exact value. Not a string comparison.
I am curious why you need to search an INT as a string.
December 26, 2007 at 12:55 pm
I don't really need to search it as a string...I just have a table that has a column "Status" and the status is an int based value. I want to let the user retrieve values based on "Status", and I want them to be able to select either a particular status... 0-10 or all statuses.
December 26, 2007 at 1:22 pm
I maybe understanding what you are doing incorrectly but it sounds like you passing the status value in as a parameter to a stored procedure?
DECLARE @TB TABLE(
[STATUS] INT,
[STATUS_DESCR] VARCHAR(25)
)
INSERT INTO @TB
SELECT 1, 'STATUS1' UNION ALL
SELECT 2, 'STATUS2' UNION ALL
SELECT 3, 'STATUS3' UNION ALL
SELECT 4, 'STATUS4' UNION ALL
SELECT 5, 'STATUS5' UNION ALL
SELECT 6, 'STATUS6' UNION ALL
SELECT 7, 'STATUS7' UNION ALL
SELECT 8, 'STATUS8' UNION ALL
SELECT 9, 'STATUS9' UNION ALL
SELECT 10,'STATUS10'
DECLARE @VAR CHAR(3)
SET @VAR = 'ALL'
IF @VAR <> 'ALL'
BEGIN
SELECT *
FROM @TB
WHERE [STATUS] = @VAR
END
ELSE
BEGIN
SELECT *
FROM @TB
END
December 26, 2007 at 1:48 pm
I'm sorry, yes that's exactly what I'm doing. I'm passing in a status value to a stored procedure as a parameter, and it's being compared to a column with the value type of Int.
December 26, 2007 at 2:23 pm
In this case you can use the code I provided. You will have to change some stuff around, but the concept will hold true. If you pass in a string variable type and set the comparison in the where clause, SQL Server 2005 will implicitly convert the datatype as show in my sample code.
All your stored procedure needs to do is determine what is Status levels are selected and then that will determine what data will be returned. This is where I used the IF/ELSE statement.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply