Using LIKE function with VARCHAR to INT datatypes...

[ranta]

Hi,

I have the following coded in an SP which I can't seem to get to work. Basically I am passing a varchar field to the SP which will contain a number of logon ID's in comma seperated format.. ie. '32,76,43,22'.

I then need to check for these values on the Logon file which stores the information in an INT field.

I have tried the following LIKE statement but can't seem to get it to work, does anyone have any ideas? On this example of the LIKE statement I am only trying to match the first logon ID ie '32'

CREATE PROCEDURE sp_ReportMonthlyMetric
(

@startDate datetime,
@profileCatID int,
@getTotals int,
@reportType int,
@profileType int,
@selProfileID varchar(50)


)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

-- Retrieve Profile description...
DECLARE @parentID as int
DECLARE @logonID as int
DECLARE @CurVar Cursor
DECLARE @profileDesc as varchar(255)
SET @profileDesc = (SELECT name FROM CA_GLOBAL_LOGON.dbo.logonProfilesCat WHERE CA_GLOBAL_LOGON.dbo.logonProfilesCat.id = @profileCatID)

-- Retrieve users in selected Profile Cat...
CREATE TABLE #TempIDs
(
PID int,
LogonID int,
parent int,
profileCatID int,
manager int,
managerFName varchar(255),
managerSName varchar(255),
jobTitle varchar(255)


)
if @profileType = 0
INSERT into #TempIDs(logonID, profileCatID)
SELECT TOP 100 PERCENT CA_GLOBAL_LOGON.dbo.Logon.ID, CA_GLOBAL_LOGON.dbo.Logon.profileCatID
FROM CA_GLOBAL_LOGON.dbo.Logon
WHERE CA_GLOBAL_LOGON.dbo.Logon.profileCatID = @profileCatID
else begin
INSERT into #TempIDs(logonID,parent, profileCatID)
SELECT TOP 100 PERCENT CA_GLOBAL_LOGON.dbo.Logon.ID, CA_GLOBAL_LOGON.dbo.Logon.parent, CA_GLOBAL_LOGON.dbo.Logon.profileCatID
FROM CA_GLOBAL_LOGON.dbo.Logon
WHERE CA_GLOBAL_LOGON.dbo.logon.parent IN(SELECT CA_GLOBAL_LOGON.dbo.logon.id FROM CA_GLOBAL_LOGON.dbo.logon WHERE cast(CA_GLOBAL_LOGON.dbo.logon.profilecatid as char)+ ',%' like @selProfileID)


select * from #TempIDs

 

[ClaireHCM]

can you try this
INSERT into #TempIDs(logonID,parent, profileCatID)
exec('SELECT TOP 100 PERCENT CA_GLOBAL_LOGON.dbo.Logon.ID, CA_GLOBAL_LOGON.dbo.Logon.parent, CA_GLOBAL_LOGON.dbo.Logon.profileCatID
FROM CA_GLOBAL_LOGON.dbo.Logon
WHERE CA_GLOBAL_LOGON.dbo.logon.parent IN(SELECT CA_GLOBAL_LOGON.dbo.logon.id FROM CA_GLOBAL_LOGON.dbo.logon WHERE CA_GLOBAL_LOGON.dbo.logon.profilecatid in ('+@selProfileID+')')

 

[ranta]

Thanks for that Claire, you have just made my afternoon a lot easier!