SqlServer and Oracle RDBMS notes

Here are some notes (function definitions and small hacks) I took while working on the last project which was based on both SqlServer and Oracle RDBMS. I’ll try to explain what they do, and I’ll also try to add new ones as the time passes. I don’t expect you to read this post as a typical article, but I do expect Google search to be miraculous as it is so maybe someone else at the end also finds it useful 🙂

Microsoft SQL Server

Procedure MyParameterizedProcedure should solve the problem of running a SQL query with input coming from another SQL query (e.g. running a select or update query with unknown table names — future input). In this case first we need to declare this procedure (this block) and then use it on some predefined SQL query pattern together with generated values (next block):

Now that we created procedure MyParameterizedProcedure, lets see how it can be used. Imagine the following scenario: There’s a first table (TableA) in which we can find IDs we want to select and the selection should be based on a precondition (where clause). Than we want to use those IDs to generate names of other tables and update records in them (TableWithGeneratedNameX)

The following block of SQL code creates new temporary table (#counts) in which we can save names and numbers of rows in each table in our database:

Oracle

 

Leave a Reply

Your email address will not be published. Required fields are marked *