How do I Populate a Result View Column with Value from Stored Proc in SQL?

Question

Edit: HAS to be a stored proc, NOT a function, sorry!

I have a table:

TABLE: dbo.Numbers 
Number_ID | Number
1         | 0
2         | 1
3         | 2
4         | 3
5         | 4
6         | 5
7         | 6     
8         | 7

I want the following output (as a View):

Number_ID | ModifiedNumber
1         | lol the num is 0
2         | lol the num is 1
3         | lol the num is 2
4         | lol the num is 3
5         | lol the num is 4
6         | lol the num is 5
7         | lol the num is 6      
8         | lol the num is 7

I have a stored procedure to do this:

CREATE PROCEDURE dbo.UselessStoredProc @inputNum int
AS
SELECT 'lol my number is: ' + CONVERT(varchar(max), @inputNum)
GO

--TEST
EXEC dbo.UselessStoredProc @inputNum=2;

My end goal is to populate theModifiedNumber col via stored proc, e.g.:

SELECT Number_ID, EXEC UselessStoredProc @inputNum = Number_ID  as ModifiedNumber
FROM [TestDb].[dbo].[Numbers]

Obviously this doesn't work. How do I accomplish this.

P.S. Please don't tell me "just do:

SELECT Number_ID, 'lol my number is: ' + CONVERT(varchar(max), Number_ID) as ModifiedNumber
FROM [TestDb].[dbo].[Numbers]

"

I am well aware I can do that - this is obviously an example, the real code is much more complicated and requires a STORED PROCEDURE. Note, I intentionally return a string from the sample stored proc - I need complex values, not just int.

Edit:

SQL Server 2012

Functions are NOT an option. I apologize for the confusion. My "function" needs to call built in Stored Procs, and functions can't do that.

Edit 2: The reason I need an SP, rather than a function, is because I need to dynamically find the primary key of some table, so I need to use dynamic SQL. I thought this was too much info, but looks like it's necessary:

DECLARE @sql nvarchar(max) = 'SELECT @Data_Table_Key = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + QUOTENAME(CONSTRAINT_NAME)), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ''' + @Data_Table_Name + ''''
EXECUTE sp_executesql @sql, N'@Data_Table_Key varchar(200) OUTPUT', @Data_Table_Key OUTPUT

Edit: In the end, I was trying to do something that can't be done with openrowset, which is a dirty, dirty hack. I ended up writing a stored proc which writes to a table every 5 mins, and the third-party software uses that table. I appreciate all the help. I accepted the answer which helped me most, but the while-loop answer was also helpful and got me what I need, albeit at unacceptable performance.

Answer 1

Based on

Edit 2: The reason I need an SP, rather than a function, is because I need to dynamically find the primary key of some table, so I need to use dynamic SQL. I thought this was too much info, but looks like it's necessary:

DECLARE @sql nvarchar(max) = 'SELECT @Data_Table_Key = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + QUOTENAME(CONSTRAINT_NAME)), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ''' + @Data_Table_Name + ''''
EXECUTE sp_executesql @sql, N'@Data_Table_Key varchar(200) OUTPUT', @Data_Table_Key OUTPUT

I'm confused. Why would you do this in dynamic SQL in the first place.

You can just as well do:

SELECT @Data_Table_Key = COLUMN_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 
  AND TABLE_NAME = @Data_Table_Name 

and end up with the same result in your @Data_Table_Key variable.

That said, your approach will give you 'incomplete' results whenever you try to fetch the PK columns from a table that has multiple fields in the primary key. No telling which one you'll get, but it will be just one of them (usually the last one, but you can't count on it!).

Some example code:

CREATE TABLE t_test_multiple_pk_fields ( key1 int NOT NULL,
                                         key2 int NOT NULL,
                                            constraint pk_test PRIMARY KEY (key1, key2),
                                         value1 int,
                                         value2 int)

DECLARE @Data_Table_Name sysname,
        @Data_Table_Key  sysname

SELECT @Data_Table_Name = 't_test_multiple_pk_fields'

SELECT Data_Table_Key = COLUMN_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 
  AND TABLE_NAME = @Data_Table_Name 

SELECT @Data_Table_Key = COLUMN_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 
  AND TABLE_NAME = @Data_Table_Name 

SELECT @Data_Table_Key