1

I am trying to insert values from one table to another one using the following insert sql query 

INSERT INTO [dbo].[table2] 
SELECT Exec [StoredProcedure],
       [Column1]
      ,[Column2]
FROM [dbo].[table1]

[table2] has the following columns:

  1. RecNo, <-- INT
  2. Column1, <--VARCHAR(50)
  3. Column2 <--VARCHAR(50)

[StoredProcedure] main purpose is that every time a new row to be inserted in table2 it selects the maximum value from RecNo from table2 and adds 1 to that number to create the next number (sequential). Here is the script for the stored procedure .

GO
ALTER  PROCEDURE [dbo].[UpdateRcnoNumbers]
@MaxRcno INT OUTPUT
AS
BEGIN 
 SELECT  @MaxRcno=MAX(Recno) FROM [table2]
 SELECT @MaxRcno=@MaxRcno+1
 RETURN @MaxRcno
END

But I am getting an error and I am not able to call the stored procedure ? Any suggestion please .

Thank you in advance

LoveData
  • 11
  • 2

1 Answers1

1

You should write a function for this purpose , read here

The problem is, Stored Procedures don't really return output directly. They can execute select statements inside the script, but have no return value. MySQL calls stored procedures via CALL StoredProcedureName(); And you cannot direct that output to anything, as they don't return anything (unlike a function).

Here

MySQL Call Command

Nouman Janjua
  • 410
  • 2
  • 9