jonosue
/ LastValue_ScalarFunction.sql
Last active
December 23, 2015 06:38
I wanted to create a function with the LAST_VALUE clause, because I haven't seen it used too often. When you call this function with a Customer ID value, it will return their last payment date, check/invoice number, and last payment amount (if they've made at least one payment before). I've also provided the script for the SalesDB database.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USE SalesDB | |
| GO | |
| IF OBJECT_ID (N'dbo.LastPaymentFxn') IS NOT NULL | |
| DROP FUNCTION dbo.LastPaymentFxn | |
| GO | |
| CREATE FUNCTION dbo.LastPaymentFxn | |
| ( | |
| @CustomerID int |
jonosue
/ Scalar_Function_Returns_XML.sql
Created
December 23, 2015 03:57
I'm not sure if there's a practical use case for this, but I think it's pretty cool nonetheless. In this example, I've designed a function that accepts an input parameter for Author ID and returns the Author's name and residence in XML format from the "authors" table in the pubs database. The script to create the pubs database is included as well.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USE pubs | |
| GO | |
| IF OBJECT_ID('dbo.AuthorIDXML', 'FN') IS NOT NULL | |
| DROP FUNCTION dbo.AuthorIDXML | |
| GO | |
| CREATE FUNCTION dbo.AuthorIDXML | |
| ( | |
| @AuID varchar(13) |
jonosue
/ QueryingXML_Using_XQuery_CTE.sql
Created
December 23, 2015 03:38
In general, I think CTEs are awesome. In this example, I'm using XQuery in a CTE to query an XML document in SQL Server. As you can see, performing aggregate functions or filtering on my result set is painless as a result! (I also included the original XML document if you want to bulk insert it into your database.)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- =================================================================== | |
| /* ABOUT THIS SQL SERVER QUERY: Using XQuery can be a bit of a painful experience in SQL Server, in my opinion. What makes it more fun and manageable, however, is being able to use a CTE (common table expression) when querying your XML. In this example, we will query an XML document that contains day-by-day weather information for the city of Victoria, Canada over an entire year (except December). We are aiming to generate a result set that shows the total rainfall, minimum temperature, and maximum temperature that occurred in each calendar month of the year. In this case, using a CTE allows you to query an XML document and generate this desired relational result set (with aggregated data) pretty easily in SQL Server. */ | |
| -- =================================================================== | |
| -------------------------------------------------------------------- | |
| /* IMPORTANT NOTE: In this example, I have declared a variable with the XML data |