How to compare String Variable with Integer

Question

I have this table structure and and some sample data. I want return data for multiple ids via parameter. I have declared a parameter string and now I want to compare it with the column but it ain't allowing because ID is integer. Can anybody give me any help here ?

CREATE TABLE EMPLOYEE
(
    ID INT,
    EMPLOYEE_NAME VARCHAR(50)
);

INSERT INTO EMPLOYEE VALUES (1, 'Isaac Frempong');
INSERT INTO EMPLOYEE VALUES (2, 'Eric Ortizz');

DECLARE @StrID VARCHAR(20) = '1, 2'
SELECT * FROM EMPLOYEE
WHERE ID = @StrID

LoztInSpace · Accepted Answer · 2019-06-12 06:06:29Z

1

SELECT * FROM EMPLOYEE
    WHERE @StrID+',' LIKE '%'+cast(ID as varchar(20))+'%,'

Pretty bad performance as it will need to do a table scan but safe enough.

Generally though, your list of IDs should be a table variable you can do a proper JOIN or IN with

answered Jun 12, 2019 at 6:06

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Turczyn · Accepted Answer · 2019-06-12 05:53:10Z

1

The easiest solution is to use dynamic SQL

DECLARE @sql VARCHAR(1000) = 'SELECT * FROM EMPLOYEE WHERE ID IN (' + @StrID + ')';
EXEC(@sql);

answered Jun 12, 2019 at 5:53

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

2 Comments

Matthias Meid Over a year ago

While it is simple indeed please bear in mind that this makes you vulnerable to SQL Injection attacks.

Michał Turczyn Over a year ago

@MatthiasMeid Maybe, if it were open to outside users, this is entirely written by OP, so unless he wants to attack himself, he's safe :)

MJoy · Accepted Answer · 2019-06-12 05:58:00Z

1

For SQL Server 2017+ you could use STRING_SPLIT a table-valued function that splits a string into rows of substrings

CREATE TABLE EMPLOYEE
(
ID INT,
EMPLOYEE_NAME VARCHAR(50)
);

INSERT INTO EMPLOYEE VALUES (1, 'Isaac Frempong');
INSERT INTO EMPLOYEE VALUES (2, 'Eric Ortizz');

DECLARE @StrID VARCHAR(20) = '1, 2'
SELECT * FROM EMPLOYEE
WHERE ID IN (SELECT value FROM STRING_SPLIT (@StrID,','))

Refer this working fiddle

answered Jun 12, 2019 at 5:58

MJoy

1,3692 gold badges10 silver badges24 bronze badges

Comments

Serg · Accepted Answer · 2019-06-12 06:56:35Z

1

Create a user defined table type and pass it as a parameter.

CREATE TYPE [UDT_INTIDS] AS TABLE(
    [ID] [int] NOT NULL
)
GO

-- create a table value
DECLARE @IDs [UDT_INTIDS];
INSERT @IDs VALUES (1),(2);

-- search using table value. 
SELECT e.* 
FROM EMPLOYEE e
WHERE e.ID IN (SELECT p.ID FROM @IDs p);
-- or
SELECT e.* 
FROM EMPLOYEE e
JOIN @IDs p ON e.ID = p.ID;

See https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017 for more details.

answered Jun 12, 2019 at 6:56

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Comments

Voodoo · Accepted Answer · 2019-06-12 05:59:20Z

0

You can use the Cast in SQL-Server to cast it to the appropriate datatype. Source Here

WHERE CAST(ID AS VARCHAR(20)) = @StrID

Alternatively: You can use CONVERT function.

WHERE CONVERT(VARCHAR(20), ID) = @StrID

edited Jun 12, 2019 at 5:59

answered Jun 12, 2019 at 5:52

Voodoo

1,6561 gold badge11 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to compare String Variable with Integer

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related