sql - 如何使用 SQL 中存储过程的值填充结果 View 列？

Question

编辑:必须是存储过程，不是函数，抱歉!

我有一张 table :

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

我想要以下输出(作为 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

我有一个存储过程来执行此操作:

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

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

我的最终目标是通过存储过程填充ModifiedNumber col，例如:

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

显然这是行不通的。我该如何实现这一目标。

附注请不要告诉我“就这么做:

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

"

我很清楚我可以做到这一点 - 这显然是一个例子，真正的代码要复杂得多，并且需要一个存储过程。请注意，我故意从示例存储过程中返回一个字符串 - 我需要复杂的值，而不仅仅是 int。

编辑:

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.

编辑2:我需要SP而不是函数的原因是因为我需要动态查找某个表的主键，所以我需要使用动态SQL。我认为这是太多信息，但看起来是必要的:

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

编辑:最后，我试图做一些 openrowset 无法完成的事情。 ，这是一个肮脏的黑客行为。我最终编写了一个存储过程，每 5 分钟写入一个表，第三方软件使用该表。我感谢所有的帮助。我接受了对我帮助最大的答案，但 while 循环答案也很有帮助，并为我提供了我需要的东西，尽管性能 Not Acceptable 。

Answer 1

基于

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

我很困惑。首先为什么要在动态 SQL 中执行此操作。

你也可以这样做:

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 

最终在您的 @Data_Table_Key 变量中得到相同的结果。

也就是说，每当您尝试从主键中有多个字段的表中获取 PK 列时，您的方法都会给您“不完整”的结果。不知道你会得到哪一个，但它只是其中之一(通常是最后一个，但你不能指望它!)。

一些示例代码:

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