T-SQL Foundation--CHP10 Programmable Object Learning notes [bottom]

--September 29, 2014 9:15:29--P338--two ways to execute dynamic SQL--EXEC CommandDECLARE @sql  as VARCHAR( -);SET @sql = 'PRINT"'This message is printed by a dynamic SQL batch"';';--note that for strings in strings, you need two single quotes (not double quotes!) To represent a single quotation markEXEC(@sql);--sp_executesql stored procedure [two input parameters and one parameter assignment part]DECLARE @sql  as NVARCHAR( -);SET @sql =N'SELECT orderid,custid,empid,orderdate from sales.orderswhere orderid = @orderid';EXECsp_executesql@stmt = @sql,--This parameter requires a Unicode string containing the batch code that you want to run  @params =N'@orderid as INT',--This parameter is a Unicode that contains the declarations for all input and output parameters in the @stmt  @orderid = 10248;--specify for input and output parameters  --using dynamic SQL P343 in pivot--A program is a programmatic object that encapsulates code to evaluate results or perform tasks--Routine 1: User-defined function scalar UDF (returns a single data value) + table-valued UDF (returns a table)--table-Valued UDFs can only appear in the FROM clause of a query UseTSQLFundamentals2008;IF object_id('Dbo.fn_age') is  not NULL   DROP FUNCTIONDbo.fn_age;GOCREATE FUNCTIONDbo.fn_age (@birthdate  as DATETIME,  @eventdate  as DATETIME)RETURNS INT asBEGIN  RETURN   DATEDIFF( Year,@birthdate,@eventdate)   -  Case  when  - * MONTH(@eventdate)+  Day(@eventdate)           <  - * MONTH(@birthdate)+  Day(@birthdate)         Then 1 ELSE 0    ENDENDGOSELECTEmpid, FirstName, LastName, Birthdate, Dbo.fn_age (birthdate,Current_timestamp) as Age fromHR. Employees;--stored Procedures (slightly)--trigger [A special stored procedure that cannot be explicitly executed and must depend on the process of an event]--Error Handling--TRY CATCHend of the 10th chapter

T-SQL Foundation--CHP10 Programmable Object Learning notes [bottom]