-1

I have a stored procedure MyProcedure which needs an int as parameter, now I need to pass an int column from another table as parameter to the procedure. How can I write a statement to implement that? I can't use cursor in my code for some reasons. Below is my pseudo code. Thanks.

Create Procedure MyProcedure
  @i_input INT
AS
... ...

create table MyTable
(
    id int,
    otherdata float
)

exec MyProcedure (select id from MyTable)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jun Ge
  • 408
  • 4
  • 13
  • 2
    Go for function because you can't run Procedure through DML/DDL – Jay Shankar Gupta Mar 30 '18 at 11:35
  • Not sure I understand - apart from returning the input parameter, what else are you doing with it? Are you intending to limit the result from MyResult? BTW, if you are intending to return the input parameter you need to add an @ symbol before the parameter. Is the commented out code only there to show your structure or are you intending to create the tables in the procedure? – Alan Mar 30 '18 at 11:38
  • what you want cannot be done like that, because your query will return more than one value and your procedure can only accept one value – GuidoG Mar 30 '18 at 11:41
  • maybe this can help https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure – GuidoG Mar 30 '18 at 11:42
  • I can't use function, becaues I have to call a procedure in my code, and i can't change the code of that procedure – Jun Ge Mar 30 '18 at 11:52
  • If you have to execute a procedure for every row in a result set you are stuck using a cursor or while loop. Depending on the stored procedure perhaps it can be rewritten to receive a table valued parameter. This would be the ideal solution. – Sean Lange Mar 30 '18 at 13:20

2 Answers2

0

Go for function because you can't run Procedure through DML/DDL

CREATE TABLE MyResult
(
  id INT,
  results VARCHAR(MAX)
)
Go
CREATE FUNCTION MyFunction(@i_input INT)
RETURNS TABLE
AS RETURN
(
  SELECT @i_input AS ID, 'result1' AS results
)
Go
INSERT INTO MyResult SELECT * FROM MyFunction(1)
Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27
  • It is a poor suggestion to create a function named "MyProcedure". Your sample usage query is syntactically incorrect. It is also a poor suggestion to use an insert statement without specifying a column list. Nor does this help OP pass a value from a row in a table as the parameter to a function/procedure. – SMor Mar 30 '18 at 13:50
0

You can do like this 

Create Procedure MyProcedure
  @i_input INT
AS
 ... ...

create table MyTable
(
   id int,
   otherdata float
)
Declare @temp_id int  
select @temp_id  =id from MyTable 

exec MyProcedure @temp_id
srp
  • 560
  • 1
  • 5
  • 14