Transact SQL :- Temporary Table & Conditional Creation

Background Transact SQL offers the best programming space of all the top tier relational database. But, there are areas one can stumble over, as well. Temporary Table Simple Let us create a simple temporary table Different Structure Objective Depending on certain conditions, our table structure might need to be different. Column 2 If @type is … Continue reading Transact SQL :- Temporary Table & Conditional Creation

SQL Server – Temp Table Structure

Background Created a temporary table on the fly. But, before wrapping things up, I wanted to review the temp table's structure.   SQL sp_help Syntax   Sample Output   Generate Table Create Statement dbo.itvf_getTempTableCreateStatement Procedure Sample Output   Summary Both sp_help and object_id work equally well with temp objects. In the case of sp_help, please … Continue reading SQL Server – Temp Table Structure

Transact SQL – Drop Temp Table if it it exists

Background Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL. Code Original Code SQL Explanation When the temp table does not exist, an error is raised. Because the drop table is enclosed in a try/catch block the … Continue reading Transact SQL – Drop Temp Table if it it exists

Transact SQL – Retrieving the structure of Temp Tables

Background Out of laziness, I sometimes create temporary tables on the fly. Sample Select/Into   Metadata Let us get metadata info on our temp table columns Outline Checked the code for sp_help using sp_helptext 'sp_help' and used that stolen code here. Code Output Script Temp Tables Stored Procedure script.sp_TableTempStructure Code Invocation   Output

Transact SQL – Create Object with different Structure based on runtime decisions

Background Based on the version of SQL Server being targeted one might need to create a slightly different table structure. Here is the original code that fails. And, slight variations. Implementation Original Code Output Revised ( Add Go Statement ) Premise: We add a go to ensure object is in fact created. Code Revised ( … Continue reading Transact SQL – Create Object with different Structure based on runtime decisions