Tag: DECLARE

Using Variables

admin

I am going to talk about Transaction SQL variables. Any stored program that does complex business processing will use one of more of variables to craft an algorithm. During my college days, I had to take a couple physics classes. Newton’s second law states that force is equal to mass times acceleration. The comic strip on the left shows how variables in the fourth picture can be used as place holders in a complex equation. This concept is key for crafting very complex programs. The DECLARE keyword is used in…

Searching Stored SQL – Part 4

admin

There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…

Searching Stored SQL – Part 3

admin

There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…

Searching Stored SQL – Part 2

admin

There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…

Searching Stored SQL – Part 1

admin

Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…

Temporary Tables – Part 3

admin

Today, I am going to talk about table variables. In general, TSQL variables are defined by using the DECLARE key word and preceeding the variable name with a @ sign. Table variables are created by using the TABLE key word just after the variable name. The declaration following this key word includes column definitions — names, data types, and constraints. The only constraint types allowed in the declaration are PRIMARY KEY, UNIQUE, NULL, and CHECK. Indexes are a bi-product of a PRIMARY and UNIQUE constraints. The scope of a table…