0

Can i pass table valued parameter in store procedure in MY-SQL like MS-SQL.

For sql procedure below:

CREATE PROCEDURE dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.Production.Location  
           (Name  
           ,CostRate  
           ,Availability  
           ,ModifiedDate)  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
        GO

For MySQL what should i do?

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
annu
  • 1
  • 1
  • 6
  • Does this helps? https://stackoverflow.com/questions/2977356/in-mysql-how-to-pass-a-table-name-as-stored-procedure-and-or-function-argument – Pradip Borde Jan 09 '18 at 12:43
  • NO, i want to pass multiple rows data(like table) sent from the front end and send to store procedure in my-sql. above link is to pass data from one table which is already store in table which is in database. – annu Jan 09 '18 at 12:46
  • the PREPARE function is what you need like @PradipBorde suggested within his link.. But you need to be carefull because it can open you up to SQL injections. – Raymond Nijland Jan 09 '18 at 12:47
  • MySQL doesn't provide a better way @annu . MySQL doesn't provide a valued parameter within store procedures.. The only method is to go dynamic SQL building with the PREPARE function. – Raymond Nijland Jan 09 '18 at 12:49
  • @annu You can store your data in temp table and then access it in stored proc. – Pradip Borde Jan 09 '18 at 12:51
  • @pradeip, but how can i pass table type value from front end to store procedure? – annu Jan 09 '18 at 12:54
  • I don't think MySQL has the TABLE type for variables. It's better if you have the stored procedure insert one line at a time, then use the front end to loop and call it multiple times. – flip Jan 09 '18 at 13:04
  • @flip yes, i don't have any other option except loop :( – annu Jan 09 '18 at 13:06
  • @annu It's not a bad thing. Your database is harder to scale than your front-end servers. – flip Jan 09 '18 at 13:24

0 Answers0