how to pass multiple rows using a single column in a stored procedure in SQL (SSMS)? or how to pass single column values in a procedure.
如何使用SQL(SSMS)中的存储过程中的单个列传递多行?或者如何在过程中传递单个列值。
CREATE TYPE tblAge_Type as TABLE
(
ID int,
Age int
)
--------------------------------------------------------------------------------------
alter PROCEDURE spAddAge (
@datasource tblAge_Type READONLY)
AS
update tblAge set Age = (select Age from @datasource ) where Id = (select ID from @datasource )
GO
------------------------------------------------------------------------------------------
DECLARE @data AS tblAge_Type
INSERT into @data VALUES(1,22) ;
INSERT into @data VALUES(2,55) ;
INSERT into @data VALUES(3,44);
EXEC spAddAge @data
getting this error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
收到此错误:子查询返回的值超过1。当子查询遵循=,!=,<,<=,>,> =或子查询用作表达式时,不允许这样做。
1 个解决方案
#1
1
there is no standard way to do this. The most common ways that this is done is: 1) make a comma delimited string, pass it as a parameter, and in the procedure, split the string into a table. There are many string splitting functions around, preferrably use one that does not use loops. I think the latest version of SQL Server has also a string splitting function. 2) pass a table valued parameter, as is explained here: How to pass a table-value parameter
没有标准的方法可以做到这一点。完成此操作的最常见方法是:1)创建逗号分隔的字符串,将其作为参数传递,并在过程中将字符串拆分为表。周围有许多字符串拆分函数,最好使用不使用循环的函数。我认为最新版本的SQL Server也有字符串拆分功能。 2)传递一个表值参数,如下所述:如何传递一个表值参数
#1
1
there is no standard way to do this. The most common ways that this is done is: 1) make a comma delimited string, pass it as a parameter, and in the procedure, split the string into a table. There are many string splitting functions around, preferrably use one that does not use loops. I think the latest version of SQL Server has also a string splitting function. 2) pass a table valued parameter, as is explained here: How to pass a table-value parameter
没有标准的方法可以做到这一点。完成此操作的最常见方法是:1)创建逗号分隔的字符串,将其作为参数传递,并在过程中将字符串拆分为表。周围有许多字符串拆分函数,最好使用不使用循环的函数。我认为最新版本的SQL Server也有字符串拆分功能。 2)传递一个表值参数,如下所述:如何传递一个表值参数