DB2 10.5 stored Procedure question

db2db2-luwstored-procedures

I'm having an issue with a stored procedure in DB2 LUW 10.5 using Data Studio 4.1.3 when trying to call more then one statement at a time. I've narrowed the problem down to the way semi-colon's (;) are being treated. For easier trouble-shooting I created a really simple example of what I am trying to do.

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";
    insert into "DB2ADMIN"."TestTable"
    values('a','a','a');
    insert into "DB2ADMIN"."TestTable"
    values('b','b','b');
    select * from "DB2ADMIN"."TestTable";

If I run the code above it completes fine but when I look at the sp I only see:

--<ScriptOptions statementTerminator=";"/>

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";

It only reads to the first semi-colon and then stops. If I remove all semi-colons but the last one the procedure will not get created. My code in real life is much longer but is behaving the same. Any help or ideas would be greatly appreciated.

Best Answer

Inside a stored procedure body you have to use a semi-colon to delimit statements. But semi-colons are also the default for delimiting statements in most clients so you end up needing to change your client's delimiter to something else (I use @). You end up with something like:

create procedure cleanup()
begin
    delete from mytable;
    delete from myothertable;
end @

Related Solutions

Sql-server – stored procedure with multiple statements does not execute all statements when run from job

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of.

The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor who developed the software set it up, but as they left no documentation, no one here was aware of the security layer on the back-end. It was confusing, largely, because the security only affects certain views - so all those other jobs that were working were calling for information from tables and views that were not influenced by the security.

When testing logged in as me, the procedure worked because I am set up in the CRM table as an administrator, but the credentials used to run SQL Server agent don't even exist in the CRM user table.

Had nothing to do with SQL Server security, but everything to do with permissions (didn't see that coming)...

Sql-server – How to get a local variable that’s a list in a stored procedure

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN link.

You would just need to take each requirement you list in your question or that the C# is performing, and write the query to pull the correct values from your tabled-values list and create your INSERT, DELETE, and UPDATE statements based on those values.

So for

Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Something like:

INSERT INTO CompanyDomains (<column list>
SELECT <column list>
FROM @TableParameter t
WHERE NOT EXISTS ( SELECT domain FROM CompanyDomains cd WHERE t.domain = cd.domain );

Best Answer

Related Solutions

Sql-server – stored procedure with multiple statements does not execute all statements when run from job

Sql-server – How to get a local variable that’s a list in a stored procedure

Related Question