-4

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.

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.

Babita
  • 1
  • 1
  • you could try in simple way `EXEC TestParams 'First', 'Second' GO` – Yogesh Sharma Nov 15 '17 at 07:40
  • 1
    Please add more details about your problem and your own efforts this far to solve the problem so that people can help you better. Read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) – pirho Nov 15 '17 at 07:41

1 Answers1

0

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

nico boey
  • 389
  • 1
  • 5
  • used table- valued parameter. added the code and error in the problem statement – Babita Nov 15 '17 at 09:56
  • This is not how it works; you formulated a question, I answered it. The problem you now have is different than the one in the title. You should formulate and submit another question. – nico boey Nov 16 '17 at 09:21