Stored procedure with ROW instead of a list of parameters

insertoracleplsqlstored-procedures

I have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement.

I would like to know if it is possible to declare the list of IN parameters of the stored procedure as a RECORD structure instead of 15 parameter, so when calling this stored procedure, I will only pass a RECORD structure instead of 15 single variables.

Is that possible in PL SQL? If no, is there any shorthand for doing this kind of declarations? Thanks

Best Answer

Sure. You can even use the predefined %rowtype record.

CREATE OR REPLACE PROCEDURE insert_row( p_rec IN table_name%rowtype )
AS
BEGIN
  INSERT INTO table_name
    VALUES p_rec;
END;

Related Solutions

Sql-server – How to consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record

Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

EDIT

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx

I hope this helps you.

Sql-server – Stored Procedure with Table Value Parameter

A cursor should very rarely be your first approach. Especially if you've gone to the trouble of making a table type; the primary goal there is to avoid row-by-row processing, splitting strings, etc.

Making some guesses about the destination table, its columns, and the name of the column in your TVP, you can insert multiple rows in the destination table by using INSERT/SELECT:

INSERT dbo.destination_table
(
  computer_name, serial_number
)
SELECT @ComputerName, column_name
  FROM @Monitor_Serial;

To make sure you only insert new serial numbers, use:

  FROM @Monitor_Serial AS m
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination_table
    WHERE serial_number = m.column_name
    -- AND computer_name = @ComputerName
  );

(You may or may not need that commented line, since it's unclear if you want to prevent any duplicates overall or just for a specific computer name.)

Best Answer

Related Solutions

Sql-server – How to consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record

Sql-server – Stored Procedure with Table Value Parameter

Related Question