tailieunhanh - 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 | 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 found later in this chapter. After you have declared the variables you need to initialize them before you can use them. Initialing Local Variables in T-SQL To initialize the variables you will use the SET command shown in these two lines of code SET @Cust_Id ANTON SET @Order_Date 11 27 1996 By setting the initial values you are then ready to use the variables within the rest of your procedure any way that you need them again by using the @varname syntax. Utilizing Built-In Functions Within T-SQL you can also use functions to perform some of the tasks needed just as you do within Visual Basic. Not all of the functions are the same nor are there necessarily as many. For instance instead of a Date .