Chapter 6 PROGRAMMING SQL SERVER 2000 337 After
Chapter 6 PROGRAMMING SQL SERVER 2000 337 After executing this, and assuming the variables are all declared properly, you ll end up with some number in the @id variable. That s one way to put a value into a variable. What s another way? SET @id = 42 If the value you re putting into the variable does not come from a table, you can use the SET statement to put the value in. That includes things like this: DECLARE @today datetime SET @today=getdate() The getdate() function is one of the built-in SQL server functions; it returns the current date and time, which the SET statement places into the variable @today. You can set a variable to the value of any SQL Server function, provided the data returned by the function will fit into the variable you declare. So, although it s appropriate to do something like the datetime example above, doing something like this DECLARE @today datetime SET @today=substring( hello , 1, 3) doesn t work, because the substring function returns a string, not a date. The exact error message you get is Syntax error converting datetime from character string. Another way to put a value into a variable is with the EXEC statement, like this: DECLARE @ReturnCode int EXEC @ReturnCode = sp_who This executes the sp_who stored procedure, which returns a result set, which is displayed, and a return code, which is not the result set. A return code is a status code that the stored procedure can use to describe whether there were any errors. Return codes are limited to datatype int, so you can also use return codes to return integer data. Chapter 9 covers this topic in detail. The final way to set a variable is also to use an EXEC, but with an output parameter. That s covered in Chapter 9 as well. You need to know a few other things about setting and using variables. First of all, setting a variable to an arithmetic expression follows the classic arithmetic orders of operation. So, to set the variable @value to 14, use this: SET @value = 2 + 3 * 4
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.