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
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