Oracle procedure with input parameter that performs multiple SUBSTR

oracleplsqlstored-proceduressubstring

I have a table Employee with the columns employee_id as type number, and employee_name as type varchar.

I need a procedure that takes an input string to insert employees into the table. The procedure should read and analyze the string which will include multiple names separated by commas.

Example:

'adam,alex,hales,jordan,jackob'

Each name should be extracted up until the comma and inserted into the employees table. So basically using the input string above I should have the names inserted as in the following result:

 employee_id | employee_name
-------------+--------------
 1           | adam
 2           | alex
 3           | hales
 4           | jordan
 5           | jackob

Any solution is appreciated.

Best Answer

You don't need a procedure, you can do this in a single SQL statement (though you could wrap that statement in a procedure if necessary):

insert into employee(employee_name)
with w as (select 'adam,alex,hales,jordan,jackob' names from dual)
select regexp_substr(names,'[^,]+', 1, level) from w
connect by regexp_substr(names, '[^,]+', 1, level) is not null;

select * from employee;

EMPLOYEE_ID | EMPLOYEE_NAME
----------: | :------------
          1 | adam         
          2 | alex         
          3 | hales        
          4 | jordan       
          5 | jackob

dbfiddle here

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 to check for missing fields in a table

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this:

DECLARE @columns TABLE
(
    ID INT IDENTITY NOT NULL,
    NAME NVARCHAR(128) NOT NULL
);

Then, using whatever method you have; split the string and populate the table above. After which you could do something like this to get your desired results:

EDIT: Changed the body of the loop based on your comment below.

CREATE TABLE ##results
(
    RecordID INT NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL
);

DECLARE @i INT = 1;
DECLARE @j INT = (SELECT MAX(ID) FROM @columns);
DECLARE @c NVARCHAR(128); -- used to store column name
DECLARE @s NVARCHAR(max); -- to store the command....

WHILE(@i <= @j)
BEGIN
SET @c = (SELECT NAME FROM @columns WHERE ID = @i);

IF @i > 1
    SET @s += 'OR (' + @c + ' IS NULL) ';
ELSE
    SET @s += '(' + @c + ' IS NULL) ';

SET @s = 'INSERT ##results SELECT RecordID, ''' 
       + @c + ''' FROM some.table WHERE ' 
       + @c + ' IS NULL; '
@i += 1;
END
EXECUTE (@s);

SELECT * FROM ##results;
DROP TABLE ##results;

Be aware though that depending on the amount of rows you have in your table this could take a while...

I hope this helps you.

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 to check for missing fields in a table

Related Question