How Tos » All Tags » User Defined Functions (RSS)

Browse by Tags

  • Determining Age through T-SQL

    Sometimes it is necessary to calculate a person's age. I've seen people use a simple datediff on YEAR but that is not sufficient. The reason why that is not sufficient is because it ONLY evaluates a difference on the YEAR value of the date. So that means from December 31, 2008 to January 1, 2009 it would return 1. Obviously that's not correct. You have to also compensate for the day of the year. There are a couple of ways this can be done. You can do inline SQL to calculate the value...

  • How to determine the Sunday of a given week!

    Sometimes it is necessary for reporting purposes to query data for a caldendar week. When doing so, us the the datepart function to retrieve the weeknum will not work because if a week spans more than one year it will only return the data for the same year. For example, January 1, 2009 is a Thursday. So the only dates that are in Week one of 2009 are January 1-3 (Thursday-Saturday). So how would you include from Sunday forward? The below function will help you. This function will return, for a given...

  • Splitting a string into a table of characters

    Sometimes you may not be able to see the characters in your data in SQL Server Mangement Studio (or another application). This simple function will allow you to pass an input string in, and it will return the individual characters as rows from a Table Valued Function. First the code: if object_id('[dbo].[fn_CharsToTable]') is not null drop function [dbo].[fn_CharsToTable] go create function [dbo].[fn_CharsToTable](@InputString varchar(max)) returns @Values TABLE (position int not null primary...

  • Hashing large data strings with a User Defined Function

    If you have ever tryed to hash data in SQL Server using SQL Server's build in HASHBYTES , you know that that it doesn't accept greater than 8000 bytes of data. While that may work for a lot of scenarios, it doesn't help when you have large strings. Below we will go over the basics of a function that I use for HASHING large data strings. The basic way that this function works is that it takes the input @String which is nvarchar(max), splits it into 8000 byte (4000 character) chunks and...

  • User Defined Functions

    User defined functions have many uses in SQL Server. Functions are useful to help clean up complex string manipulation logic in other areas of code and many other things. But convenience is not free. There is overhead in using functions and we will go over that later in the article. There are two types of User Defined Functions in SQL Server, Scalar Valued and Table Valued Functions. Scalar Valued functions are used to return a single value whereas Table Valued functions return a table. The table...

  • Table Valued Functions

    A table valued function is a function that returns a table instead of a single value. In this post, we will go over the Table Valued Functions in more detail than we did in the general topic covering User Defined Functions . There are many uses for table valued functions so we will go over a couple of examples to ensure that you will be successful when you begin to create your own. The first thing you need to understand is how you define a function as a Table Valued Function. This is done by defining...

  • A View of Numbers

    The below view will generate a list of numbers ranging from 1 - 4,294,967,296. This view can be used in place of a physical table of numbers for some small operations but since it utilizes several large cross joins it is not very efficient once it starts counting into large numbers. create view vw_Nums as with cte0 as (select 1 as c union all select 1), cte1 as (select 1 as c from cte0 a, cte0 b), cte2 as (select 1 as c from cte1 a, cte1 b), cte3 as (select 1 as c from cte2 a, cte2 b), cte4 as (select...

  • Converting a delimited string into a table

    In this article, I will show you how you can convert a delimited string of data into a table of data. In order to do this we will use a table valued function. There are many ways to achieve this but this how I like to do it. In order to use the function, you will need a numbers table. Since that's a topic that we haven't covered here yet, we will utilize a view that builds one on the fly. The view can be found in the A View of Numbers post. If you have a numbers table, replace "vw_nums"...
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems