Q:

sql Function not returning value

Wrote a function for accepting a Number and a user id and returning a table having individual digit in a single row with that User id. Function created successfully but when i tried to execute function its returning a blank table.
Attaching screenshot of function and output.

Create Function ExtractingDigits(@InputNumber INT, @UserId varchar)
    RETURNS @ReturnTable Table(UserId Varchar(20),  ModNumber Int)
as
begin
Declare @ModNumber Int
While @InputNumber!=0
begin
set @ModNumber= @InputNumber%10
set @InputNumber=@InputNumber/10
insert into @ReturnTable(UserId, ModNumber)
select * from @ReturnTable
end
return
end

select * from ExtractingDigits(123, 'as')

A:

The fix is to use a VALUES clause for your INSERT rather than a SELECT from an empty table:
alter Function ExtractingDigits(@InputNumber INT, @UserId varchar)
    RETURNS @ReturnTable Table(UserId Varchar(20),  ModNumber Int)
as
begin
Declare @ModNumber Int
While @InputNumber!=0
begin
set @ModNumber= @InputNumber%10
set @InputNumber=@InputNumber/10
insert into @ReturnTable(UserId, ModNumber)
values (@UserId,@ModNumber)
end
return
end
go
select * from ExtractingDigits(123, 'as')

Just as style comments though, I'd also recommend a) changing either the parameter order or the table definition so that they're both consistent (i.e. have both be int, varchar or varchar, int rather than swapping) but b) recommend not including @UserId in this function at all. This would make the function much more generically usable:
alter Function ExtractingDigits(@InputNumber INT)
    RETURNS @ReturnTable Table(ModNumber Int)
as
begin
Declare @ModNumber Int
While @InputNumber!=0
begin
set @ModNumber= @InputNumber%10
set @InputNumber=@InputNumber/10
insert into @ReturnTable(ModNumber)
values (@ModNumber)
end
return
end
go
select * from ExtractingDigits(123) cross apply (select 'as' as UserId)

