MS SQL Server

Sometime a stored procedure not only want to take input values from the calling statement batch, but it also want to send output values back to the calling statement batch. This can be done by defining output parameters in the CREATE PROCEDURE statement.

To define an output parameter, you should use this format: "@parameter_name data_type OUTPUT", as shown in the following tutorial exercise:

DROP PROCEDURE diff_in_days;

GO

-- Defining an output parameter

CREATE PROCEDURE diff_in_days

@start_date DATETIME,

@end_date DATETIME = '19-May-2007',

@days VARCHAR(40) OUTPUT

AS BEGIN

SET @days = CONVERT(VARCHAR(20),@end_date,107)

+ ' - '

+ CONVERT(VARCHAR(20),@start_date,107)

+ ' = '

+ STR(DATEDIFF(DAY, @start_date, @end_date));

END;

GO

Command(s) completed successfully.

EXEC diff_in_days

@start_date='01-Jan-2007'

GO

Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0

Procedure or Function 'diff_in_days' expects

parameter '@days', which was not supplied.

A local temporary stored procedure is a special stored procedure that:

* Is created like a normal (permanent) stored procedure with the name prefixed with a number sign (#).

* Are only valid in the same client session where it was created.

* Will be deleted when creating session is terminated.

This tutorial exercise here creates two stored procedures, one is permanent and the other is local temporary:

DROP PROCEDURE Hello;

DROP PROCEDURE #Hello;

GO

CREATE PROCEDURE Hello

@url nvarchar(40)

AS

PRINT 'Welcome to ' + REVERSE(@url);

GO

CREATE PROCEDURE #Hello

@url nvarchar(40)

AS

PRINT 'Welcome to ' + @url;

GO

EXECUTE Hello 'rendc.org';

GO

Welcome to globalguideline

EXECUTE #Hello 'rendc.org';

GO

Welcome to rendc.org

A user defined function is a collection of Transact-SQL statements that stored in the SQL Server. A user defined function will return data when executed.

A user defined function works in the same way as a system function. It can be used as a scalar expression or a temporary table depending on the turning data format. A system function is provided as part of the SQL Server. But a user defined function must be created and managed by yourself.

If you are using a group of statements to calculate the same data repeatedly in different places, you should consider to create a user defined function for that group of statements.

User defined functions are also called functions.

Differences between user defined functions and stored procedures are:

* Stored procedures does not return any data and they can not be used in expressions.

* User defined functions does return data and they can be used in expressions.

* Stored procedures only takes data constants as input parameters.

* User defined functions can take data constants and expressions as input parameters.

* Stored procedures support output parameters.

* User defined functions do not support output parameters.

* Stored procedures take a parameter's default value, if it is not specified in the calling statement.

* User defined functions take a parameter's default value, if it is specified with the keyword DEFAULT in the calling statement.

* Stored procedures are called with no parenthesis to include parameters.

* User defined functions are called with parenthesis to include parameters. Empty parenthesis is needed if no parameters.

* Stored procedures can be created locally and temporarily.

* User defined functions can not be created locally and temporarily.

If you want to know how an existing user defined function was created, you can use SQL Server Management Studio to automatically generate a "CREATE FUNCTION" script The following tutorial shows you how to do this:

1. Run SQL Server Management Studio and connect to SQL server.

2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Programmability > Functions > Scalar-valued Functions > dbo.Sundays.

3. Click right mouse button on dbo.Sundays. The context menu shows up.

4. Select "Script Function as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:

USE [GlobalGuideLineDatabase]

GO

/****** Object: UserDefinedFunction [dbo].[Sundays]

Script Date: 05/19/2007 23:24:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[Sundays]()
 RETURNS INT
 AS BEGIN
 DECLARE @date DATETIME;
 DECLARE @count INT;
 SET @date = '2006-12-31';
 SET @count = 0;
 WHILE DATEPART(YEAR, @date) <= 2008 BEGIN
  SET @date = DATEADD(DAY, 1, @date);
  IF DATENAME(WEEKDAY, @date) = 'Sunday'
  SET @count = @count + 1;
  END;
 RETURN @count;
 END;

An user defined function must return a value, which can be used in any expression as long as the return value data type matches the expression.

To execute a user defined function and use its return value in an expression, you just need to enter the schema name and the function name as a value in the expression. The tutorial exercise below shows you how use a user defined function in an expression:

-- Calling a function without schema name

PRINT 'Hi there, '+Welcome();

GO

Msg 195, Level 15, State 10, Line 1

'Welcome' is not a recognized built-in function name.

-- Calling a function with schema name

PRINT 'Hi there, '+dbo.Welcome();

GO

Hi there, Welcome to rendc.org

If you want to create a simple user defined function, you can use the "CREATE FUNCTION" command with a statement block in a simple format as shown in below:

CREATE FUNCTION function_name()

RETURNS data_type

AS BEGIN

statement_1;

statement_2;

...

statement_n;

RETURN expression;

END;

GO

The following tutorial exercise shows you how to create a simple user defined function:

USE GlobalGuideLineDatabase;

GO

CREATE FUNCTION Welcome()

RETURNS VARCHAR(40)

AS BEGIN

RETURN 'Welcome to rendc.org';

END;

GO

PRINT dbo.Welcome();

GO

Welcome to rendc.org

If you want to see a list of all user defined functions in your current database, you can use the system view, sys.objects as shown in this tutorial exercise:

USE GlobalGuideLineDatabase;

GO

-- Number of Sundays in this year

CREATE FUNCTION Sundays()

RETURNS INT

AS BEGIN

DECLARE @date DATETIME;

DECLARE @count INT;

SET @date = '2006-12-31';

SET @count = 0;

WHILE DATEPART(YEAR, @date) <= 2008 BEGIN

SET @date = DATEADD(DAY, 1, @date);

IF DATENAME(WEEKDAY, @date) = 'Sunday'

SET @count = @count + 1;

END;

RETURN @count;

END;

GO

SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION';

GO

name  object_id schema_id type type_desc  

-------- ----------- ---------- ---- -------------------

Welcome 2085582468 1  FN SQL_SCALAR_FUNCTION

Sundays 2117582582 1  FN SQL_SCALAR_FUNCTION

(2 row(s) affected)

sys.objects contains all types of objects in the current database. You need select only the FUNCTION object type.

Sometimes you need to convert numeric values enclosed in character strings back to numeric values by using the CONVERT() function. When converting character strings to values with CONVERT(), you need to remember two rules:

* Leading and trailing space characters are ignored.

* The input string must represent a numeric literal that matches the target numeric data type.

* If the input string does not represent a numeric literal that matches the target numeric data type, you will get a conversion error.

The tutorial exercise below shows you how to use the CONVERT() function to convert strings to values:

SELECT CONVERT(NUMERIC(10,5), ' 12345.12 ');

GO

12345.12000

-- Input does not match the target data type

SELECT CONVERT(INT, '12345.12');

GO

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar

value '12345.12' to data type int.

-- Input does not match the target data type

SELECT CONVERT(NUMERIC(10,5), '12345.12E+00');

GO

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

-- Double conversion works

SELECT CONVERT(NUMERIC(10,5),

CONVERT(FLOAT(24), ' 12345.12E+00 '));

GO

12345.12012

SELECT CONVERT(FLOAT(24), ' 12345.12E+00 ');

If you are converting a numeric expression to an integer data type and the value is too big for integer storage size, you will get an arithmetic overflow error as shown in the following examples:

-- Overflow error on implicit conversion

DECLARE @x FLOAT(24);

DECLARE @y TINYINT;

SET @x = 12345.12E+00;

SET @y = @x;

GO

Msg 232, Level 16, State 1, Line 4

Arithmetic overflow error for type tinyint,

value = 12345.120117.

-- Overflow error on explicit conversions

DECLARE @x FLOAT(24);

SET @x = 12345.12E+00;

SELECT CAST(@x AS TINYINT);

SELECT CONVERT(TINYINT, @x);

GO

Msg 232, Level 16, State 1, Line 4

Arithmetic overflow error for type tinyint,

value = 12345.120117.

Msg 232, Level 16, State 1, Line 5

Arithmetic overflow error for type tinyint,

value = 12345.120117.

SQL Server 2005 supports 23 mathematical functions: ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, and TAN. The return data types of mathematical functions are determined by two rules:

* Arithmetical functions: ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN are returning the same data type as the input values. No implicit data type conversions are performed.

* Other mathematical functions: COS, COT, EXP, LOG, LOG10, SIN, SQRT, SQUARE and TAN are taking only FLOAT data type as input and returning FLOAT data type. Implicit data type conversion will be performed if you are calling these functions with a non-FLOAT data type.

The tutorial exercise gives you some good examples on how to use mathematical functions:

 -- ABS retuns the same data type as the input

 DECLARE @x FLOAT(53);

 DECLARE @y NUMERIC(9,2);

 DECLARE @z INT;

 SET @x = -12345.123456789E+20;

 SET @y = -12345.12;

 SET @z = -12345

 SELECT ABS(@x);

 SELECT ABS(@y);

 SELECT ABS(@z);

 GO

 1.2345123456789E+24

 12345.12

 12345

Sometimes you need to round a numeric value to a specific precision. For example, you may want to round values in your financial statement to the precision of 1000.00. This can be done by the ROUND() function with the following syntax:

ROUND(value, precision, type)

value: The input value to be rounded.

precision: The location of the precision digit relative

to the decimal point.

type: 0 - Round to nearest value;

1 - Truncate to a lower value.

The tutorial exercise below gives some good examples of how to use the ROUND() function:

SELECT ROUND(1234.5678, 0, 0);

SELECT ROUND(1234.5678, -3, 0);

SELECT ROUND(1234.5678, -4, 0);

SELECT ROUND(1234.5678, 3, 0);

SELECT ROUND(1234.5678, 3, 1);

GO

1235.0000

1000.0000

0.0000

1234.5680

1234.5670

Sometimes you need to round a numeric value into an integer. SQL Server 2005 offers you a number of ways to do this:

* FLOOR(value) - Returning the largest integer less than or equal to the input value. The returning data type is the same as the input value.

* CEILLING(value) - Returning the smallest integer greater than or equal to the input value. The returning data type is the same as the input value.

* ROUND(value, 0, 0) - Returning the integer most close to the input value. The returning data type is the same as the input value.

* CAST(value AS INT) - Returning the largest integer less than or equal to the input value. The returning data type is INT.

* CONVERT(INT, value) - Returning the largest integer less than or equal to the input value. The returning data type is INT.

The tutorial exercise below gives some good examples of converting numeric values to integers:

 SELECT FLOOR(1234.5678);

 SELECT CEILING(1234.5678);

 SELECT ROUND(1234.5678, 0, 0);

 SELECT CAST(1234.5678 AS INT);

 SELECT CONVERT(INT, 1234.5678);

 GO

 1234

 1235

 1235.0000

 1234

 1234

Concatenating two character strings together is most commonly used string operation. SQL Server 2005 allows to concatenate two character strings into a single string with the (+) operator. The following tutorial exercise shows you some string concatenation examples:

DECLARE @site VARCHAR(40);

SET @site = 'GlobalGuideLine.com';

SELECT 'Welcome to '+@site;

SELECT 'Current date and time is '

+CONVERT(VARCHAR(20), GETDATE());

GO

Welcome to GlobalGuideLine.com

Current date and time is May 19 2007 5:18PM

DECLARE @start INT, @end INT, @total INT;

SET @start = 21;

SET @end = 30;

SET @total = 728;

SELECT 'Search result '

+ CONVERT(VARCHAR(20),@start)

+ ' - '

+ CONVERT(VARCHAR(20),@end)

+ ' of '

+ CONVERT(VARCHAR(20),@total);

GO

Search result 21 - 30 of 728

If a Unicode string NVARCHAR is concatenated with a non-Unicode string VARCHAR, SQL Server will implicitly convert the non-Unicode string to Unicode string for concatenation.

DECLARE @regcode VARCHAR(40);

DECLARE @unicode NVARCHAR(40);

SET @regcode = 'Some Unicode characters: '

SET @unicode = NCHAR(9733)+NCHAR(9734)+NCHAR(9792)

+NCHAR(9794);

SELECT @regcode + @unicode;

SELECT DATALENGTH(@regcode);

SELECT DATALENGTH(@unicode);

SELECT DATALENGTH(@regcode + @unicode);

Some Unicode characters: ????

25

8

58

Note that the non-Unicode string @regcode has been converted to a Unicode string. The number of bytes of @regcode changed from 25 to 50. With 8 bytes from @unicode, the number of bytes of the concatenated string becomes 58.

Random numbers are very useful for generating test data, passwords, or other security related data. SQL Server 2005 offers you the random number generator function RAND in two format:

* RAND(seed) - Starting a new sequence of random numbers based on the given integer "seed" and returning the first random number in FLOAT(53) from the sequence.

* RAND() - Returning the next random number in FLOAT(53) from the current sequence. If there has been no current sequence, SQL Server will start a new sequence with a random "seed".

Note that calling RAND(seed) with the same seed will start the same sequence and return the same number. To avoid this repeating pattern, you should always call RAND() without any seed and let the server to randomly pickup a sequence. The tutorial exercise below shows some good examples on how to generate random numbers:

SELECT RAND(100), RAND(), RAND(); -- new sequence

SELECT RAND(100), RAND(), RAND(); -- same sequence again

SELECT RAND(), RAND(), RAND();

SELECT RAND(), RAND(), RAND();

GO

0.715436657367485 0.28463380767982 0.0131039082850364

0.715436657367485 0.28463380767982 0.0131039082850364

0.28769876521071 0.100505471175005 0.292787286982702

0.868829058415689 0.370366365964781 0.58334760467751

If you are converting a numeric expression to a NUMERIC data type and the value is too big for the storage size, you will get an arithmetic overflow error as shown in the following examples:

-- Overflow error on implicit conversion

DECLARE @x FLOAT(24);

DECLARE @y NUMERIC(5,2);

SET @x = 12345.12E+00;

SET @y = @x;

GO

Msg 8115, Level 16, State 6, Line 4

Arithmetic overflow error converting real to data type

numeric.

-- Overflow error on explicit conversions

DECLARE @x FLOAT(24);

SET @x = 12345.12E+00;

SELECT CAST(@x AS NUMERIC(5,2));

SELECT CONVERT(NUMERIC(5,2),@x);

GO

Msg 8115, Level 16, State 6, Line 3

Arithmetic overflow error converting real to data type

numeric.

Msg 8115, Level 16, State 6, Line 4

Arithmetic overflow error converting real to data type

numeric.

If you want get the definition of an existing user defined function back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of functions, stored procedures, and views.

The sys.sql_modules holds user defined function definitions identifiable by the object id of each function. The tutorial exercise below shows you how to retrieve the definition of stored procedure, "Sundays" by joining sys.sql_modules and sys.objects:

SELECT m.definition

FROM sys.sql_modules m, sys.objects o

WHERE m.object_id = o.object_id

AND o.name = 'Sundays';

GO

definition

-----------------------------------------------

CREATE FUNCTION Sundays()

RETURNS INT

AS BEGIN

DECLARE @date DATETIME;

DECLARE @count INT;

SET @date = '2006-12-31';

SET @count = 0;

WHILE DATEPART(YEAR, @date) <= 2008 BEGIN

SET @date = DATEADD(DAY, 1,

(1 row(s) affected)