In our last lesson, we developed a stored procedure that does some pretty cool things. It combines a lot of concepts we've learned thus far and even gives the user some feedback about what's happening. However, there are still some things we could do to make it work a little better.

For example, we could add more print statements to let the user know exactly what's going on. One of the things that'd be nice to do is tell the user the amounts in play. We could even let them know if how much they have in case we can't make the transfer they've asked for.

The Powerful Print Statement

The PRINT statement is a great little tool for experimenting with SQL. Let's take a look at some simple examples using the print statement before going with the modifications to our stored procedure.

DECLARE @Name varchar(50)
SET @Name = 'Bob'
PRINT 'Hello'
PRINT @Name

When you run this, you'll see the following output:

Hello
Bob

As you can see, the PRINT statement works on both literals, the static value "Bob" and variables like @Name. It might be nice to put things on the same line. SQL gives us a way to do this. This is called concatenation and it's pretty simple in SQL.

Simple Concatenation of Strings

To stick together several strings of data to create a sentence, we can simply use the + operator as if we were adding numbers.

DECLARE @Name varchar(50)
SET @Name = 'Bill'
PRINT 'Hello, ' + @Name + '! It''s good to see you today!'

The output from this statement is the following.

Hello, Bill! It's good to see you today!

The values seamlessly mesh together and look like a normal sentence. However, things get a little more complex when it comes to sticking a number or date into a SQL PRINT statement.

DECLARE @Cash money
SET @cash = 2.50
PRINT 'You have ' + @cash + ' left in your account.'

This will generate an error saying that there was a problem with conversion. SQL is a language with explicit conversions. That means you can't tell treat a number variable like a string and have SQL play along with the charade. Some languages will automatically understand that you mean to use @cash as a string in this situation, but that can actually be counterproductive.

How can it be a bad thing? Well, sometimes programmers accidently use a variable in the wrong place. It might be hard enough to find their mistake but it gets even harder to figure out when the programming language starts covering over their mistake and trying to make things work. SQL prefers to be a little more annoying but make sure you really want to do what you're attempting.

Converting SQL Variables With CONVERT

First, we'll look at how to explicitly tell SQL how to treat a variable. Let's look at our example above again. We'll add some new code to it. First, let's look at the syntax of the CONVERT statement.

Syntax:

CONVERT([variable declaration], [variable])

Example:

CONVERT(varchar(50), @cash)

This is not a stand-alone statement. We have to use it in another SQL statement. The conversion happens at real time when we run the SQL code.

DECLARE @Cash money
SET @cash = 2.50
PRINT 'You have ' + CONVERT(varchar(50), @cash) + ' left in your account.'

As you can see, the variable declaration section looks just like when we define a SQL variable. We're telling SQL to treat @cash as if it were a varchar(50) for the time being. Fortunately, "2.50" could be a currency value as well as a string of characters, so SQL has no complaints.

Converting Literal Values With CAST

Sometimes we have a value that is not stored in a variable that we need to convert. Perhaps a constant in a math formula that never changes. SQL makes assumptions if we put a literal value in place without defining its type. It usually does not cause any trouble, but it's best to be consistent.

Syntax:

CAST ( [expression] AS [variable declaration])

Example:

CAST(2.50 AS Money)

Let's say we want to do some math and put the result into a string of text. Here is an example:

PRINT 'Two quarters equals $' + (.25 + .25) + '.'

SQL won't let us do this, we need to wrap the math in a CAST statement.

PRINT 'Two quarters equals $' + CAST(.25 + .25) + '.'

Now, we can stick things into variables easily. Let's take a look at our procedure from the last lesson and add some nice PRINT statements to tell users what's going on with their money.

ALTER PROCEDURE TransferMoneyToSavings
(Amount money, AccountID int, CheckingID int, SavingsID int)
AS

DECLARE @current money

SELECT @Current = Total 
FROM Checking
WHERE CheckingID = @CheckingID
PRINT 'You have requested to transfer ' + CONVERT(varchar(50), @amount) + '…'
IF @current => @amount THEN
BEGIN

UPDATE Checking 
SET Total = Total + @Amount
FROM Checking
WHERE CheckingID = @CheckingID

UPDATE Savings
SET Total = Total – @Amount
FROM Savings
WHERE SavingsID = @SavingsID

INSERT INTO Log  (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, @amount, @CheckingID, @SavingsID, @amount, -(@amount))

PRINT CONVERT(varchar(50), @amount) + ' was successfully transferred!'
END

ELSE
BEGIN

Print 'You cannot transfer more money than you have!'		 
Print 'You only have ' + CONVERT(varchar(50), @current) + ' at the moment, but you asked to transfer ' + CONVERT(varchar(50), @ammount) + '…this will not do.'
INSERT INTO Log  (AccountID, AmtTransfered, CheckingID, SavingsID, CheckingChange, SavingsChange)
VALUES (@accountID, 0, @CheckingID, @SavingsID, 0, -(0))

END

Now we have some nice alerts built into our system to let users know when something is going wrong. Up next, we'll take a look at how to format some of these items. We'll learn how to do math on dates and further manipulate variables into string values that we can show to the user. Stay tuned!

Categories : SQL
Tags : t-sql, SQL Basic


author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories