Pass select result as parameter of stored procedure

1.One way is:

a) Declare your variables

b) Assign values to them with a single select statement

c) Execute the procedure passing the local variables

d) Execute the following in a loop using WHILE or CURSOR in order to apply this for all rows in TABLE1

DECLARE @param1 <DATATYPE>, @param2 <DATATYPE>, ...

SELECT TOP 1 @param1 = col1,    @param2 = col2, ...
FROM TABLE1
WHERE <where_clause>

EXEC SaveDate @param1, @param2, ...

2.Other way is to define your own table type, fill it, and pass it to procedure. However this requires changing a little bit your stored procedure (in params list your custom type should be followed by READONLY):

CREATE TYPE [dbo].[TYPENAME] AS TABLE(
    [ID] [int] NOT NULL,
    ...
)
GO

DECLARE @myTypeVar TYPENAME;

INSERT @myTypeVar
SELECT col1, col2, ...
FROM TABLE1

EXEC SaveData @myTypeVar

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)