T-SQL Programming Part 1 – Defining Variables, and logic #articles, #databases, #microsoft


#

T-SQL Programming Part 1 – Defining Variables, and IF. ELSE logic

Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. This is the first of a series discusses defining variables, and using the IF. ELSE logic.

This is the first of a series of articles discussing various aspects of T-SQL programming. Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. This first article will discuss defining variables, and using the IF. ELSE logic.

Local Variables

As with any programming language, T-SQL allows you to define and set variables. A variable holds a single piece of information, similar to a number or a character string. Variables can be used for a number of things. Here is a list of a few common variable uses:

  • To pass parameters to stored procedures, or function
  • To control the processing of a loop
  • To test for a true or false condition in an IF statement
  • To programmatically control conditions in a WHERE statement

More than one variable can be defined with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you separate each variable definition with a comma, like so:

Here is an example of how to use the SELECT statement to set the value of a local variable.

One of the uses of a variable is to programmatically control the records returned from a SELECT statement. You do this by using a variable in the WHERE clause. Here is an example that returns all the Customers records in the Northwind database where the Customers Country column is equal to ‘Germany’

IF. ELSE

T-SQL has the “IF” statement to help with allowing different code to be executed based on the results of a condition. The “IF” statement allows a T-SQL programmer to selectively execute a single line or block of code based upon a Boolean condition. There are two formats for the “IF” statement, both are shown below:

Format one: IF condition then code to be executed when condition true

Format two: IF condition then code to be executed when condition true ELSE else code to be executed when condition is false

In both of these formats, the condition is a Boolean expression or series of Boolean expressions that evaluate to true or false. If the condition evaluates to true, then the “then code” is executed. For format two, if the condition is false, then the “else code” is executed. If there is a false condition when using format one, then the next line following the IF statement is executed, since no else condition exists. The code to be executed can be a single TSQL statement or a block of code. If a block of code is used then it will need to be enclosed in a BEGIN and END statement.

Let’s review how “Format one” works. This first example will show how the IF statement would look to execute a single statement, if the condition is true. Here I will test whether a variable is set to a specific value. If the variable is set to a specific value, then I print out the appropriate message.

The above code prints out only the phrase “The number is 29”, because the first IF statement evaluates to true. Since the second IF is false the second print statement is not executed.

Now the condition statement can also contain a SELECT statement. The SELECT statement will need to return value or set of values that can be tested. If a SELECT statement is used the statement needs to be enclosed in parentheses.

Here I printed the message “Found A-D Authors” if the SELECT statement found any authors in the pubs.dbo.authors table that had a last name that started with an A, B, C, or D.

So far my two examples only showed how to execute a single T-SQL statement if the condition is true. T-SQL allows you to execute a block of code as well. A code block is created by using a “BEGIN” statement before the first line of code in the code block, and an “END” statement after that last line of code in the code block. Here is any example that executes a code block when the IF statement condition evaluates to true.

Above a series of “PRINT” statements will be executed if this IF statement is run in the context of the master database. If the context is some other database then the print statements are not executed.

Sometimes you want to not only execute some code when you have a true condition, but also want to execute a different set of T-SQL statements when you have a false condition. If this is your requirement then you will need to use the IF. ELSE construct, that I called format two above. With this format, if the condition is true then the statement or block of code following the IF clause is executed, but if the condition evaluates to false then the statement or block of code following the ELSE clause will be executed. Let’s go through a couple of examples.

For the first example let’s say you need to determine whether to update or add a record to the Customers table in the Northwind database. The decision is based on whether the customer exists in the Northwind.dbo.Customers table. Here is the T-SQL code to perform this existence test for two different CustomerId’s.

The first IF. ELSE logic checks to see it CustomerId ‘ALFKI’ exists. If it exists it prints the message “Need to update Customer Record”, if it doesn’t exist the “Need to add Customer Record” is displayed. This logic is repeated for CustomerId = ‘LARS’. When I run this code against my Northwind database I get the following output.

As you can see from the results CustomerId ‘ALFKI’ existed, because the first print statement following the first IF statement was executed. Where as in the second IF statement CustomerId ‘LARSE’ was not found because the ELSE portion of the IF. ELSE statement was executed.

If you have complicated logic that needs to be performed prior to determining what T-SQL statements to execute you can either use multiple conditions on a single IF statement, or nest your IF statements. Here is a script that determines if the scope of the query is in the ‘Northwind’ database and if the “Customers” table exists. I have written this query two different ways, one with multiple conditions on a single IF statement, and the other by having nested IF statements.

As you can see I tested to see if the query was being run from the Northwind database and whether the “Customers” table can be found in sysobjects. If this was true, I printed the message “Table Customers Exists”. In the first example I had multiple conditions in a single IF statement. Since I was not able to determine which parts of the conditions in the IF statement where false the ELSE portion printed the message “Not in Northwind database or Table Customer does not exist”. Now in the second example where I had a nested IF statement I was able to determine whether I was in the wrong database or the object “Customers” did not exist. This allowed me to have two separate print statements to reflect exactly what condition was getting a false value.

I hope that this article has helped you understand how to declare and use local variables, as well as IF. ELSE logic. Local variables are useful to hold the pieces of information related to your programming process. Where as the IF statement helps control the flow of your program so different sections of code can be executed depending on a particular set of conditions. As you can see nesting IF statements and/or having multiple conditions on an IF statement allows you to further refine your logic flow to meet your programming requirements. My next article in this T-SQL programming series will discuss how to build a programming loop.

See All Articles by Columnist Gregory A. Larsen


What is database (DB)? Definition from #nosql #databases #compared


#

database (DB)

A database is a collection of information that is organized so that it can be easily accessed, managed and updated.

Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information. Data gets updated, expanded and deleted as new information is added. Databases process workloads to create and update themselves, querying the data they contain and running applications against it.

Download this free guide

Ultimate Developer’s Guide to SQL Server

Jam-packed with tips to improve SQL Server database performance: How to best create tables and columns, normalize and restore your database, and configure aliases.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy .

Computer databases typically contain aggregations of data records or files. such as sales transactions, product catalogs and inventories, and customer profiles.

Typically, a database manager provides users with the ability to control read/write access, specify report generation and analyze usage. Some databases offer ACID (atomicity, consistency, isolation and durability) compliance to guarantee that data is consistent and that transactions are complete.

Databases are prevalent in large mainframe systems, but are also present in smaller distributed workstations and midrange systems, such as IBM’s AS/400 and personal computers.

Evolution of databases

Databases have evolved since their inception in the 1960s, beginning with hierarchical and network databases, through the 1980s with object-oriented databases. and today with SQL and NoSQL databases and cloud databases .

In one view, databases can be classified according to content type: bibliographic, full text, numeric and images. In computing, databases are sometimes classified according to their organizational approach. There are many different kinds of databases, ranging from the most prevalent approach, the relational database, to a distributed database. cloud database or NoSQL database.

Relational database

A relational database. invented by E.F. Codd at IBM in 1970, is a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways.

Relational databases are made up of a set of tables with data that fits into a predefined category. Each table has at least one data category in a column, and each row has a certain data instance for the categories which are defined in the columns.

The Structured Query Language (SQL) is the standard user and application program interface for a relational database. Relational databases are easy to extend, and a new data category can be added after the original database creation without requiring that you modify all the existing applications.

Distributed database

A distributed database is a database in which portions of the database are stored in multiple physical locations, and in which processing is dispersed or replicated among different points in a network.

Distributed databases can be homogeneous or heterogeneous. All the physical locations in a homogeneous distributed database system have the same underlying hardware and run the same operating systems and database applications. The hardware, operating systems or database applications in a heterogeneous distributed database may be different at each of the locations.

Cloud database

A cloud database is a database that has been optimized or built for a virtualized environment, either in a hybrid cloud, public cloud or private cloud. Cloud databases provide benefits such as the ability to pay for storage capacity and bandwidth on a per-use basis, and they provide scalability on demand, along with high availability .

A cloud database also gives enterprises the opportunity to support business applications in a software-as-a-service deployment.

NoSQL database

NoSQL databases are useful for large sets of distributed data.

NoSQL databases are effective for big data performance issues that relational databases aren’t built to solve. They are most effective when an organization must analyze large chunks of unstructured data or data that’s stored across multiple virtual servers in the cloud.

Object-oriented database

Items created using object-oriented programming languages are often stored in relational databases, but object-oriented databases are well-suited for those items.

An object-oriented database is organized around objects rather than actions, and data rather than logic. For example, a multimedia record in a relational database can be a definable data object, as opposed to an alphanumeric value.

Graph database

A graph-oriented database, or graph database. is a type of NoSQL database that uses graph theory to store, map and query relationships. Graph databases are basically collections of nodes and edges, where each node represents an entity, and each edge represents a connection between nodes.

Graph databases are growing in popularity for analyzing interconnections. For example, companies might use a graph database to mine data about customers from social media .

Accessing the database: DBMS and RDBMS

A database management system (DBMS) is a type of software that allows you to define, manipulate, retrieve and manage data stored within a database.

A relational database management system (RDBMS) is a type of database management software that was developed in the 1970s, based on the relational model, and is still the most popular way to manage a database.

Microsoft SQL Server, Oracle Database, IBM DB2 and MySQL are the top RDBMS products available for enterprise users. DBMS technologies began in the 1960s to support hierarchical databases, and they include IBM’s Information Management System and CA’s Integrated Database Management System.

This was last updated in February 2017

Next Steps

Expert Adrian Lane explains what database security tools are and how they work.

Continue Reading About database (DB)

Related Terms

database normalization Database normalization is intrinsic to most relational database schemes. It is a process that organizes data into tables so that. See complete definition flat file A flat file contains records that have no structured interrelationship. A flat file typically consists of a text file, from which. See complete definition Microsoft Visual FoxPro (Microsoft VFP) Microsoft Visual FoxPro (VFP) is an object-oriented programming environment with a built-in relational database engine. See complete definition

Dig Deeper on SQL Server Database Modeling and Design


Software development magazine: software testing, project management, Agile, Scrum, DevOps, programming, requirements


#

Methods Tools is a free software development magazine on Project Management, Software Testing, Agile, Scrum,
Lean, Kanban, Requirements ( User Stories, UML, Business Analysis), Programming, Databases, User Experience (UX)
Open Source Tools, Software Process Improvement, Software Architecture, Peopleware, Mobile Apps
Subscribe to Methods Tools if you are not afraid to read more than one page to get smarter!

Last Articles Published by Methods Tools

Lean UX and the Language of Change
Learn how the language used in a Lean UX approach can help uniting the team and solving customers problems.

Project Deadlines are Elastic by Value
There is nothing wrong with deadlines but deadlines should come from business need, not effort estimates which are magically transformed into deadlines.

Improve Software Testing with Containers
This article presents how container technology improves different software testing tasks and its benefits.

Automated Testing Strategy for a Microservices Architecture
This article explains how adopting microservices opened up new ways to design automated end-to-end system tests.

Agile Forecasting: Asking Why and Discovering What is Behind the When
Forecasting is guesswork, it may be scientific guesswork and it may be based on past experience, metrics, and clever projection tools, but it is a guess. You will be wrong far more often than you are right.

InversifyJS – IoC container for JavaScript, TypeScript Node.js
InversifyJS is an inversion of control library that works with frontend and backend JavaScript applications like Node.js.

Codetrack – Free .NET Performance Profiler and Execution Analyzer
Codetrack is a free performance profiler and execution analyzer for .NET applications.

Ada: a C Developer’s Perspective
Learn how the Ada programming language helps solving modern software development challenges like coding for IoT.

Explore the best in Agile practices Agile2017, August 7-11 2017, Orlando, USA
Recommended project management software for Agile teams and enterprises
TimeShiftX – Test automation software for date shift testing time travel of applications.
TestMatick – independent software testing and quality assurance company
Discover the best available Open Source Project Management Tools


DocuMed Electronic Medical Records Software – Home Page #documed, #emr, #ehr, #cpr,


Welcome

DocuMed Technology

• The DocuMed technology is based on the Microsoft family of products including Microsoft SQL Server and Microsoft Server

• DocuMed is a Microsoft Certified Partner

• An assessment of the optimal blend of Microsoft products is performed to meet each individual client’s needs

• The resulting combination may encompass Microsoft client server, terminal services and wireless technologies allowing for secure connectivity whether in the office, surgical suite or home

• Seamless and secure web-based access may be granted to third party service providers like the transcriptionist or a billing service company

• DocuMed is wireless pen-tablet compatible

• DocuMed is a Ricoh-Lanier solutions partner for integrated enterprise printing, copying, scanning and faxing.

Comprehensive Electronic Clinical Office Solutions

At DocuMed, we believe that effortlessly generating clear, concise medical documentation while achieving your charting style and workflow is just the first step of a multi-component process that encompasses the Electronic Clinical Office (ECO). DocuMed has continually perfected the art of capturing and generating patient encounter data with templates that incorporate text, voice recognition, voice dictation and point-and-click technology since 1988.

DocuMed set its targets beyond traditional EMR to realize a comprehensive ECO. Applying physician experiences with EMR’s, we developed the most complete and well thought out electronic medical documentation management tool available. DocuMed efficiencies are most apparent when observed in a real setting. Eliminate the stress and confusion. We invite you to learn more about the DocuMed Electronic Clinical Office and the many benefits of generating, managing and distributing electronic medical documentation.

Features

Copyright © 2017. DocuMed, Inc. All Rights Reserved.


SQL server databases stuck in restoring state – Database Administrators Stack Exchange


#

This is likely caused by the restore script adding the WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. The database is now waiting for the latest transaction log file.

  1. Apply the latest transaction log (Using RESTORE LOG. WITH RECOVERY ). or
  2. Restore the database again, but this time using WITH RECOVERY. or
  3. Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY

Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

See this for details:

When those don t work, see dba.stackexchange.com/questions/11175/ — again, LOTS OF WARNINGS OF DOOM if you don t know what you re doing and understand the full risk of data loss. But if you re in a bind on a non-production server, you ve got good backups to do a fresh restore from, and you can afford to restart SQL on said server, it works. NateJ Feb 26 ’16 at 1:18

I just had this situation, and the cure was quite surprising:

Apparently the NetBackup restore that broke left it in a weird state. No other solution worked (though I hadn’t yet tried restarting the SQL Server service)

I’d be careful with the database, though, as theoretically once a restore starts, then fails, you could have corrupted data. I’m just going to restore the database again anyway, so it doesn’t matter to me.