SQL – Stored Procedures and Functions

Creating a stored procedure

A stored procedure is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.
Stored procedures can be found in SQL Server under the database tree view > Programability > Stored Procedure.
You can create one using the Query Analyser or by right clicking on “Stored Procedures” folder in SQL and selecting “New Stored Procedure”.

Here is a sample

CREATE PROCEDURE tblProducts_GetAllForType
	-- Add the parameters for the stored procedure here
	@Type int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM tblProducts WHERE [Type]=@Type
END
GO

This can be called for example with the following statement:

exec dbo.tblProducts_GetAllForType 1

Creating a function

CREATE FUNCTION creates a user-defined function in SQL Server.
A user-defined function is a Transact-SQL that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways:

  • In Transact-SQL statements such as SELECT
  • In applications calling the function
  • In the definition of another user-defined function
  • To parameterize a view or improve the functionality of an indexed view
  • To define a column in a table
  • To define a CHECK constraint on a column
  • To replace a stored procedure

Example:

CREATE FUNCTION Add_two_Numbers
(
	-- Add the parameters for the function here
	@number1 int, @number2 int
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @result int

	-- Add the T-SQL statements to compute the return value here
	SET @result = @number1 + @number2

	-- Return the result of the function
	RETURN @result

END
GO


This function can be executed like this:

SELECT dbo.Add_Two_Numbers (1,5) as Total

Will display 6
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s