Use Variables and Functions in T-SQL

Sử dụng các biến và chức năng trong T-SQL Tôi hiểu rằng tôi có thể sử dụng các thông số trong thủ tục của tôi được lưu trữ, nhưng làm thế nào để tôi sử dụng các biến và chức năng bên trong T-SQL? Kỹ thuật này là thời gian để mở rộng mã hóa bạn bằng cách sử dụng T-SQL. Trong cuốn sách này cho đến nay
Nội dung trích xuất từ tài liệu:
Use Variables and Functions in T-SQL 6.2 Use Variables and Functions in T-SQLI understand that I can use parameters in my stored procedures, but how do I usevariables and functions within T-SQL?TechniqueIt is time to expand the coding you do using T-SQL. In this book so far, you have prettywell been limited to single-line SELECT statements. Now you will learn how to usevariables and built-in functions. To achieve this, look at the routine that will be createdhere:DECLARE @Cust_Id nchar(5), @Order_Date datetimeSET @Cust_Id = ANTONSET @Order_Date = 11/27/1996SELECT OrderID, OrderDate, ShippedDate,DateDiff(day, @Order_Date, ShippedDate) as Days_To_ShipFROM OrdersWHERE CustomerID = @Cust_Id and OrderDate = @Order_DateDeclaring Local Variables in T-SQLYou 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 DECLAREstatement, the ampersand with the variable name, and the data type. You can see anexample of the variable declaration here, where nchar, with the length and datetimevariables, is declared.DECLARE @Cust_Id nchar(5), @Order_Date datetimeNote 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 usethem.Initialing Local Variables in T-SQLTo initialize the variables, you will use the SET command, shown in these two lines ofcode:SET @Cust_Id = ANTONSET @Order_Date = 11/27/1996By setting the initial values, you are then ready to use the variables within the rest of yourprocedure, any way that you need them, again, by using the @varname syntax.Utilizing Built-In FunctionsWithin T-SQL, you can also use functions to perform some of the tasks needed, just asyou do within Visual Basic. Not all of the functions are the same, nor are therenecessarily as many. For instance, instead of a Date() function, which is used in VisualBasic, in T-SQL, you use the GetDate() function. Functions also will not necessarilyreturn the same values or accept the same parameters.This How-To calls the DateDiff() function. As with Visual Basics DateDiff() function,this function takes two dates, and based on the interval requested, it returns the differencebetween the two. To check out other functions that are available, you can look up theword function in the Books On-Line for SQL-SQL Server.StepsOpen and run the Visual Basic .NET-Chapter 6 solution. From the main page, click onthe button with the caption How-To 6.2. When the form loads, you will see a SQLstatement display in a label, and a DataGrid displayed below (see Figure 6.3). 1. Create a Windows Form. Then place the controls listed in Table 6.2 and seen in Figure 6.3 with the following properties set. Table 6.2. Control Property Settings for This How-To Object Property Setting Label Text SQL Statement Label Name lblSQLString Label Text Results DataGrid Name dgResults2. Add the code in Listing 6.2 to the Load event of the form. (Double-click on the 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. Dim odaResults As New OleDb.OleDbDataAdapter(Me.lblSQLString.Text, BuildCnnStr((local), Northwind)) Dim dtResults As New DataTable() T ...