6.2 Use Variables and Functions in T-SQL
I understand that I can use parameters in my stored procedures, but how do I use
variables and functions within T-SQL?
Technique
It is time to expand the coding you do using T-SQL. In this book so far, you have pretty
well been limited to single-line SELECT statements. Now you will learn how to use
variables and built-in functions. To achieve this, look at the routine that will be created
here:
DECLARE @Cust_Id nchar(5), @Order_Date datetime

SET @Cust_Id = 'ANTON'
SET @Order_Date = '11/27/1996'

SELECT OrderID, OrderDate, ShippedDate,
DateDiff(day, @Order_Date, ShippedDate) as Days_To_Ship
FROM Orders
WHERE CustomerID = @Cust_Id and OrderDate = @Order_Date
Declaring Local Variables in T-SQL
You can use variables in T-SQL much like you would in your other coding languages.
First, you must declare them. To declare variables in T-SQL, you will use the DECLARE
statement, the ampersand with the variable name, and the data type. You can see an
example of the variable declaration here, where nchar, with the length and datetime
variables, is declared.
DECLARE @Cust_Id nchar(5), @Order_Date datetime
Note
Besides the standard SQL Server data types, such as nchar, int, and
datetime, you can also declare and create a Table datatype. You will see
an example of this in How-To 6.8, found later in this chapter.

After you have declared the variables, you need to initialize them before you can use
them.

form to bring up the code.) Creating the T-SQL routine described in the
"Technique" section, this code then assigns the routine to the Text property of the
Label called lblSQLString. It then creates a data adapter using the string and fills
the dtResults DataTable. Last, the code assigns the data adapter as the data source
for the dgResults data grid.
Listing 6.5 frmHowTo6_2.vb: Storing the SQL Statement and Then
Executing
Private Sub frmHowTo6_2_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load '-- Build the SQL String
Dim strSQL As String

strSQL = "DECLARE @Cust_Id nchar(5), @Order_Date datetime " & _
vbCrLf & vbCrLf

strSQL &= "SET @Cust_Id = 'ANTON'" & vbCrLf
strSQL &= "SET @Order_Date = '11/27/1996'" & vbCrLf & vbCrLf

strSQL &= "SELECT OrderID, OrderDate, ShippedDate, "
strSQL &= "DateDiff(day, @Order_Date, ShippedDate) as Days_To_Ship "
strSQL &= "FROM Orders" & vbCrLf
strSQL &= "WHERE CustomerID = @Cust_Id and OrderDate =
@Order_Date"

'-- Store the SQL String
Me.lblSQLString.Text = strSQL

'-- Use the SQL String to build the data adapter and fill the data table.