cursor insert with like '%'

[yurps]

Hi I have a weird problem I want to cursor thru the values in a
temporary table and use the values to do a select statement to insert
into another temporary table...This select statement uses a like clause

something like where....when I take off the insert still nothing comes
back from the select...when I hardcode values it works...I get
results...is there something wrong with appending a +'%' to a value
read from a cursor???

DECLARE @DEPT VARCHAR(65)
SET @DEPT = "00201,00203"

DECLARE @TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))
DECLARE @TB_DEPT TABLE ( V_DEPARTMENT_CODE VARCHAR(128) )

INSERT INTO @TB_DEPT (V_DEPARTMENT_CODE)
SELECT V_DEPT FROM [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @DEPT)

DECLARE DEPTS CURSOR FAST_FORWARD FOR
SELECT V_DEPARTMENT_CODE+'%' FROM @TB_DEPT

OPEN DEPTS
FETCH NEXT FROM DEPTS INTO @DEPT_CODE

WHILE @@FETCH_STATUS = 0
BEGIN

--INSERT INTO @TB_ABSENCES TABLE
SELECT Code from TB_EMPLOYEE_DEPARTMENT T2
WHERE T2.V_HIERARCHY_CODE LIKE @DEPT_CODE + '%'

FETCH NEXT FROM DEPTS INTO @DEPT_CODE

END

CLOSE DEPTS
DEALLOCATE DEPTS

[Erland Sommarskog]

yurps (yu***@yahoo.co.uk) writes:

Hi I have a weird problem I want to cursor thru the values in a
temporary table and use the values to do a select statement to insert
into another temporary table...This select statement uses a like clause

something like where....when I take off the insert still nothing comes
back from the select...when I hardcode values it works...I get
results...is there something wrong with appending a +'%' to a value
read from a cursor???

I would guess that there are trailing spaces. Rewrite as:

DECLARE @DEPT VARCHAR(65)
SET @DEPT = "00201,00203"

DECLARE @TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))

INSERT INTO @TB_ABSENCES TABLE
SELECT Code
from TB_EMPLOYEE_DEPARTMENT T2
JOIN [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @DEPT) D
ON T2.V_HIERARCHY_CODE LIKE rtrim(V_DEPT) + '%'

Yeah, that's right. No cursor. There is no need for it, and it could
be costly in terms of performance.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[yurps]

Oh gosh !! a couple hours wasted on something that I didn't need
to....

thanks!