Hi, try:

create table #UserFriends(FriendId numeric,UserId numeric, [name] nvarchar(100))

insert into #UserFriends
select 1,21,'a' union all
select 2,21,'b' union all
select 3,21,'c' union all
select 4,22,'d' union all
select 5,22,'e' union all
select 6,23,'f' union all
select 7,23,'g'

create table #Users(UserId numeric, UserName nvarchar(100))
insert into #Users
select 1,'a' union all
select 4,'d' union all
select 6,'f' union all
select 7,'g'

select * from #UserFriends
select * from #Users


DECLARE @ID numeric
DECLARE @username NVARCHAR(50)
DECLARE @getID CURSOR
SET @getID = CURSOR FOR SELECT FriendId from #UserFriends where UserId=21
OPEN @getID FETCH NEXT FROM @getID INTO @ID
WHILE @@FETCH_STATUS = 0
	BEGIN
		select @username = UserName from #Users where UserId=@id
		PRINT @username
		FETCH NEXT 
		FROM @getID INTO @ID
	END
CLOSE @getID
DEALLOCATE @getID

p.s. dont forget to dispose ur temp table:

drop table #Users
drop table #UserFriends

AvanthaSiriwardana:

DECLARE @ID numeric

DECLARE @username NVARCHAR(50)

DECLARE @getID CURSOR

SET @getID = CURSOR FOR SELECT FriendId from #UserFriends where UserId=21

OPEN @getID FETCH NEXT FROM @getID INTO @ID

WHILE @@FETCH_STATUS = 0

BEGIN

--select @username = UserName from #Users where UserId=@id

SET @username  = (SELECT UserName from #Users where UserId=@id) 

PRINT @username

FETCH NEXT 

FROM @getID INTO @ID

END

CLOSE @getID

DEALLOCATE @getID

huh???

try:

create table #listofusernames(UserName nvarchar(50))
  
DECLARE @ID numeric  
DECLARE @username NVARCHAR(50)   
DECLARE @getID CURSOR  
SET @getID = CURSOR FOR SELECT FriendId from #UserFriends where UserId=21   
OPEN @getID FETCH NEXT FROM @getID INTO @ID   
WHILE @@FETCH_STATUS = 0   
    BEGIN  
        select @username = UserName from #Users where UserId=@id
        insert into #listofusernames
        select @username   
        FETCH NEXT    
        FROM @getID INTO @ID   
    END  
CLOSE @getID   
DEALLOCATE @getID 

select * from #listofusernames
drop table #listofusernames

In my sample remove the second join with CTE (try with only one JOIN with CTE).

I don't understand - why do we need to use CURSOR here at all?

Isn't it the same as

with cte_Friends as (select FriendID from UserFriends where UserID = @SomeID)

select UserName from Users where UserID in (select FriendID from cte_Friends)

Can you please explain me what I'm missing here?

and i am not getting any thing out of it

 Post your tables creation script along with some data - then we can suggest something.

Why you are using WHILE ?

This is used for loops, but your query does not require a loop, am I wrong?

An IF statement might solve the problem

IF @id IS NOT NULL

Eralper

T-SQL Programming  for Developers