#sql201

CREATE TABLE

I've showed you how to get data out of a table, put data in, change it, and delete it.  But I haven't showed you how to create your own table....until now.

CREATE TABLE tableName ( columnName )

This statement is pretty straightforward, you have to choose the name of the table, and the names of your columns.  You also have to choose what data type you want to use for each column.  You can also list several columns rather than just one, just remember you have to separate the columns with a comma.  I haven't talked about this before, but you have to be careful what words you choose when you get to decide what to call a column, or a table, or any other object you create.

You can't use reserved words.

A reserved word is a word SQL uses.  For example, you can't create a table called SELECT.  How confused would you be if someone asked you what to do with:

SELECT * FROM SELECT

The interpreter wouldn't know what to do with this either.  And for that reason, you need to be aware that certain words are reserved in SQL.  Basically, if you see me type it in all caps, it's reserved.  If you have colorizing turned on in your SQL editor, and the word changes to a color different than your regular text, it's probably reserved.  If you can search on Microsoft's Books Online, and find it... it's reserved.  The moral; be careful.

There is another way you can create a table.  It comes up when you already have a SELECT statement, and you want to write that data out to a "temporary" table.  I put temporary in quotes, since that phrase actually has a deeper meaning.  I'll cover that more in another post.  For now, let me show you how to make a table from a SELECT statement.

SELECT * INTO tableName2 FROM tableName

This will create a table using the column names and datatypes already defined in tableName.  You can then use tableName2 to do work, and when you are through with it, you can get rid of it.  I'll show you how to delete database objects in another post too, since there are a few more concepts you'll need to be familiar with first.

The two queries I've shared with you today will let you create any number of tables you wish.  You won't be able to create two tables in the same database with the same name, but otherwise you can create any table you wish.  Just keep in mind reserved words, and you'll be alright.  As usual, if you have any questions, send them in.  I'm here to help!

It’s that time again! Time to start up a new class and cover some more advanced topics in SQL Development. Before beginning these lessons, You need to be comfortable with all the material in my SQL101 series. Specifically you’re going to need to know SELECT, INSERT, UPDATE, and DELETE.

With those skills in hand, I’ll help you take the next steps in your education. Here are the topics I plan to cover in this class:

INTERSECT, UNION, EXCEPT –Combine and compare result sets

Sub Queries– when a join just won’t cut it

Correlated Sub Queries

Non-correlated Sub Queries

Ranking Data – who’s the boss?

Views –It’s like a table… but not

CREATE TABLE– some times you’ll have to roll your own.

IDENTITY Columns – the easiest primary key to implement

IDENTITY INSERT –for those time you need to force a record in

COMPUTED – there are some useful time savings you can do with these

CONSTRAINT –make sure your data is what you think it is

Primary Keys

Foreign Keys – the basis of relational database systems

ALTER TABLE – trust me, requirements change…therefore tables change

SELECT INTO – make a backup, the easy way

DROP/TRUNCATE/DELETE

Variables

remember data types

table variables

Errors –learn to deal with them now, before they get bad

error number

error text

RAISERROR -- create and raise your own errors

CREATE PROCEDURE– now you start “real” programming

parameters

output parameters

results

error handling

Functions --remember sum, avg?

cast and convert

patindex

string manipulation

count(*)/Count(col)

CREATE FUNCTION

scalar

tvf

OUTPUT – think of them as on demand triggers

MERGE

TRANSACTIONS

CTE

Recursive CTEs

There’s a LOT more to this class than 101, but when you’re done with this, you’ll be ready for any junior level SQL job you can find. Much of this I didn’t learn until I was well into my mid level positions.

As always, if you have any problems with the class, please let me know… I’m here to help!

I'm working my way through the last of the 201 lessons. Today we're going to dive back into stored procedures. This time I want to cover the three ways you can get data back out. You can simply use a SELECT statement to return a record set, or you can define one or more of your procedure's parameters as an OUTPUT variable. The last method you can use is a return code. The return code can seem like a limited way of returning data, but if you think your return codes through before implementing the procedure, you can pull off some useful tricks!

Record Sets

This is the most common way I've seen people pull data out of a stored procedure. If you wrap a SELECT statement into a stored procedure the record set is returned. The problem with using this method is, you have to be careful in how the code calling the stored procedure will deal with multiple record sets.

Most of the time, people will call a stored procedure and expect only one record set. But look at the query below

CREATE PROCEDURE usp_ExampleWithMultiplerecord sets AS BEGIN SELECT '1' AS ID, 'This is the first record set.' as Message SELECT '2' AS ID, 'This is the second record set.' as Message END GO EXEC usp_ExampleWithMultiplerecord sets

When you run this stored procedure you get two different record sets. If you were calling this from a webpage, if you don't handle the results right, you might only see the first record set.

This gets even trickier if you do a DML (Data Manipulation Language) query and then a SELECT.

CREATE PROCEDURE usp_ExampleWithDMLAndQuery AS BEGIN DECLARE @results TABLE ( ID INT, Message VARCHAR(255) ) INSERT INTO @results SELECT '1' AS ID, 'This is the first record set.' as Message SELECT '2' AS ID, 'This is the second record set.' as Message SELECT * FROM @results END GO EXEC usp_ExampleWithDMLAndQuery

This time you only see one record set, but look at the messages in SSMS. Notice you see a message:

"2 rows affected"

Consider the webpage example again. If your webpage doesn't interpret the results just right, you won't see the two rows. You'll keep seeing a NULL record set. That's because your page is interpreting the "2 rows affected" as the first record set. The common workaround is to add a SET NOCOUNT ON statement to the beginning of your stored procedure, but that's just a band-aid. The real solution is to look at how you're interpreting the stored procedures results in the web page (or application layer).

Output Parameters

After a while of using just record sets to return data from stored procedures, I see people "upgrading" their code to use output parameters. It's very easy to set up a stored procedure to use an output parameter, you simply add the keyword OUTPUT after the parameter's data type, and you're set. The only trick to know is when you call the stored procedure, you have to set up a variable outside the stored procedure to hold the returned value, and you have to then identify that variable as an OUTPUT variable in order for the SQL interpreter to pass the values off to your variable. Let's work through an example now.

CREATE PROCEDURE usp_multiplyTwoNumbers @input1 INT , @input2 INT , @result INT OUTPUT AS BEGIN SET @result = @input1 * @input2 END GO

After running this code, you have a stored procedure that will multiply the two input values, and store the result in an OUTPUT parameter called @result. Now, that we have the stored procedure, let's use it.

DECLARE @currentResult INT EXEC usp_multiplyTwoNumbers 2, 2, @currentResult OUTPUT SELECT 'The result is " + CONVERT(VARCHAR(255), @currentResult)

We multiplied 2 * 2 and stored the result in @currentResult. We then showed the results along with some explanatory notes. We could use the variable any way we wish. The value is there and accessible until the script ends, or you reach a GO statement. Then you go outside the scope of that variable.

With some time and practice, you'll find all sorts of useful ways to use OUTPUT parameters. Things get really interesting once you consider table variables! Return Codes

The last way I know of getting data out of a stored procedure is return codes. A return code is an set of integers you define in your stored procedure. I use them to indicate different types of errors or warnings that can occur when a stored procedure is run.

Let's look at one of the examples from BOL:

USE AdventureWorks; GO IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.usp_GetSalesYTD; GO CREATE PROCEDURE Sales.usp_GetSalesYTD @SalesPerson nvarchar(50) = NULL, -- NULL default value @SalesYTD money = NULL OUTPUT AS -- Validate the @SalesPerson parameter. IF @SalesPerson IS NULL BEGIN PRINT 'ERROR: You must specify a last name for the sales person.' RETURN(1) END ELSE BEGIN -- Make sure the value is valid. IF (SELECT COUNT(*) FROM HumanResources.vEmployee WHERE LastName = @SalesPerson) = 0 RETURN(2) END -- Get the sales for the specified name and -- assign it to the output parameter. SELECT @SalesYTD = SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID WHERE LastName = @SalesPerson; -- Check for SQL Server errors. IF @@ERROR <> 0 BEGIN RETURN(3) END ELSE BEGIN -- Check to see if the ytd_sales value is NULL. IF @SalesYTD IS NULL RETURN(4) ELSE -- SUCCESS!! RETURN(0) END

What we've defined here is a set of return codes that indicate where in the code we've gone wrong. We can now call this stored procedure like this:

DECLARE @returnCode INT , @resultSalesYTD INT; DECLARE @inputSalesPerson VARCHAR(50) = NULL; EXEC @returnCode = EXEC Sales.usp_GetSalesYTD @SalesPerson = @inputSalesPerson , @SalesYTD = @resultSalesYTD OUTPUT

And we can programatically know how the stored procedure did, based on the value of @returnCode.

If @returnCode is 0, then we know the stored procedure executed successfully.

If we get a 1, then we know we didn't pass in a valid LastName for the sales person.

If we get a 2, then we know that LastName doesn't exist in the HumanResources.vEmployee view.

If we get a 3, we know we got some other SQL error.

If we get a 4, then we know the @salesYTD is going to be NULL.

Of course we would know if there was some other SQL error, since we didn't implement any error trapping. So @returnCode 3 is superfluous. We could test for @result codes 2 and 4 other ways too, but I can think of none that wouldn't require you to consider the output on the application side of things.

Conclusion

As always, knowing when to use each of the three options is key. Practice with them, and you'll learn when each option is the best solution for the task at hand. As usual, if you have any questions send them in...I'm here to help!

Let's say you have the following table on two servers, one in development and one in production.

CREATE TABLE person ( personID INT IDENTITY(1,1) , firstName VARCHAR(50) , lastName VARCHAR(50) )

You're about to implement a new query, but you need to test it out in development before you move it to production.  The problem is you need production data to complete your test.  The problem is you can't simply run

INSERT INTO development.yourDatabase.dbo.person SELECT * FROM production.yourDatabase.dbo.person

If you did try to run that you'd get an error like this.

Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'person' when IDENTITY_INSERT is set to OFF.

Which should bring you immediately to the question, how do I turn that on?

SET IDENTITY_INSERT tablename ON

Once you do that, all you have to do is list all the columns you want to insert to, and you can override the IDENTITY(1,1). You'll still get an error if you try to insert a value that is already in use.  But otherwise, you can custom declare the values you want.

SET IDENTITY_INSERT development.yourDatabase.dbo.tablename ON

INSERT INTO development.yourDatabase.dbo.person (personID, firstName, lastName) SELECT * FROM production.yourDatabase.dbo.person

That's it!  Pretty easy right?  Well it is, as long as you don't forget to turn IDENTITY_INSERT back OFF, when you're done!

SET IDENTITY_INSERT tablename OFF If you have any questions, send them in... I'm here to help!

Time and time again, I find myself trying to find all the stored procedures that reference a specific database object.  It usually comes around when I’m working on changes to a table’s structure, or I’m replacing a table with a view.  I’ll usually reach for the following query (or something like it.

1: SELECT ROUTINE_NAME

  2: FROM INFORMATION_SCHEMA.ROUTINES

  3: WHERE

  4: ROUTINE_DEFINITION LIKE '%searchTerm%'

  5: AND ROUTINE_TYPE='PROCEDURE'

  Today I decided it was time I created a function to accomplish this same task.  That way I could always use the same tool to locate stored procedures that reference X.

So I created this.

1: CREATE FUNCTION f_findProceduresReferencing (@searchTerm VARCHAR(500) )

  2: RETURNS @results TABLE (

  3: routine_Name SYSNAME

  4: )

  5: AS

  6: BEGIN

  7: INSERT INTO @results

  8: SELECT ROUTINE_NAME

  9: FROM INFORMATION_SCHEMA.ROUTINES

  10: WHERE

  11: ROUTINE_DEFINITION LIKE '%' + @searchTerm + '%'

  12: AND ROUTINE_TYPE='PROCEDURE'

  13:

  14: RETURN

  15: END

  I want you to see that for the input, I’m taking a VARCHAR.  That way you can give partial names.  but for the output, I’m returning SYSNAME, that way the results can be used as inputs to other queries.

Now that we have a function, you can use it in a simple query:

1: SELECT * FROM dbo.f_findProceduresReferencing('tableName')

  But you could also apply the function to the results of another query.  Try this on for size.

1: SELECT TOP 10 so.name, f.ROUTINE_NAME

  2: FROM sys.objects so

  3: CROSS APPLY dbo.f_findProceduresReferencing(so.name) f

  4: WHERE

  5: type = 'u'

  6: ORDER BY

  7: so.Name

  This is the reason I created my function.  I can then select the tables I’m replacing, and this function will show me all the stored procedures I have to update before removing the original tables.  That’s the payoff!