cursor insert with like %

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 DEPTSyurps (yurps@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh gosh !! a couple hours wasted on something that I didn't need
to...

thanks!