For some of you, this will be fairly basic, but I have found that there is a good deal of misunderstanding about this subject, so I thought I’d clear some of them up. SQL Server ships with tons of Stored PROCedures (SPROCS), but as you are probably aware, you can create your own. So, let’s take a look at how to create a SPROC that contains Input parameters, Output parameters and Return values.
First, let’s create a simple SPROC:
CREATE PROC Production.sp_LongLeadProducts
AS
AS
SELECT Name, ProductNumber
FROM Production.Product
WHERE DaysToManufacture >= 1
GO
There are a couple of things to note about this SPROC. The first is that it violates several Best Practices:
- You should SET NOCOUNT ON in all SPROCS
- You should not name SPROCS with a sp_ prefix. When the DB engine encounters a call to an object with this prefix, it first looks in Master to find the object. Avoid this by using a different prefix, or don’t use a prefix at all. Some naming convention experts recommend that the names of objects should not reflect the kind of object it is.
- The table name (Production.Product) is not fully qualified. In a SPROC you cannot use the “USE database” statement so you have no way of knowing exactly which database the caller of your SPROC will be using. Therefore, whenever you reference an object in a SPROC you should use the full 3-part name which includes the name of the database (AdventureWorks. Production.Product)
- Statements are not terminated with a semi-colon (;). True, this has nothing to do with SPROCs particularly, but still, it is becoming more important to follow this practices.
- The WHERE clause hard-codes the value of 1 to filter for. If you wanted a SPROC that searched for values that were 2 or more days out to manufacture, you would have to write a new SPROC; and another for 3 days, another for 4 days etc. Obviously this is not sustainable or desirable. We need a way to pass the number of days as a parameter. Obviously you can do this, or I wouldn’t be writing this article.
So, let’s try it again:
CREATE PROC Production.LongLeadProducts @MinimumLength int = 1
AS
CREATE PROC Production.LongLeadProducts @MinimumLength int = 1
AS
SET NOCOUNT ON;
SELECT Name, ProductNumber
FROM AdventureWorks.Production.Product
WHERE DaysToManufacture >= @MinimumLength;
GO
Now the code which calls the SPROC can pass a value into it as a parameter that will be used in the WHERE clause. Providing a value for the parameter (@MinimumLength int = 1) is optional but is often a good idea so that if the caller forgets to pass a value you have a default.
Notice also that I corrected the other problems that were listed.
Notice also that I corrected the other problems that were listed.
OK, let’s create a SPROC that contains multiple Input parameters and an Output Parameter. The SCOPE_IDENTITY() function returns the last identity value inserted into an identity column in the same scope. The HumanResources.Department table in the sample database AdventureWorks contains 4 columns. The first is an identity column, then there are the Name and GroupName columns which will be used in the SPROC, finally there is a ModifiedData column which defaults to GETDATE(). This SPROC uses 2 Input parameters and 1 Output parameter. You are allowed up to 2100 parameters! If you need more than that, something has gone horribly wrong.
CREATE PROC HumanResources.AddDepartment
@Name nvarchar(50), @GroupName nvarchar(50), @DeptID smallint OUTPUT
AS
@Name nvarchar(50), @GroupName nvarchar(50), @DeptID smallint OUTPUT
AS
SET NOCOUNT ON;
INSERT INTO AdventureWorks.HumanResources.Department (Name, GroupName)
VALUES(@Name, @GroupName);
SET @DeptID = SCOPE_IDENTITY();
GO
/ TEST the procedure /
DECLARE @dept int;
EXEC HumanResources.AddDepartment 'Refunds', 'Refund Department', @dept OUTPUT;
SELECT @dept;
DECLARE @dept int;
EXEC HumanResources.AddDepartment 'Refunds', 'Refund Department', @dept OUTPUT;
SELECT @dept;
@Name and @GroupName are input parameters. @DeptID is labeled as an Output parameter. Input parameters are not specially designated while Output parameters must be. Additionally, Output parameters cannot be of Table data type.
So, this SPROC inserts a record into the Department table and then populates @DeptID with the Identity value of the newly inserted record. Now take a look at the code which calls the SPROC. It first declares a variable (@dept) to contain the value which is output. In this code, I purposely made that variable have a different name and data type than the Output parameter to emphasize the fact that this variable is NOT the same as the parameter. Normally, I would be sure to use the same data type to avoid forcing the DB engine to do an implicit conversion. When I call the SPROC, the variable which is passed as an Output parameter is updated with the actual parameter value. (For those of you who are familiar with other programming languages, it appears that this parameter is being passed ByRef.)
So, this SPROC inserts a record into the Department table and then populates @DeptID with the Identity value of the newly inserted record. Now take a look at the code which calls the SPROC. It first declares a variable (@dept) to contain the value which is output. In this code, I purposely made that variable have a different name and data type than the Output parameter to emphasize the fact that this variable is NOT the same as the parameter. Normally, I would be sure to use the same data type to avoid forcing the DB engine to do an implicit conversion. When I call the SPROC, the variable which is passed as an Output parameter is updated with the actual parameter value. (For those of you who are familiar with other programming languages, it appears that this parameter is being passed ByRef.)
OK, last thing to introduce is Return Values. When creating a SPROC, it is a nice idea to return a value to indicate condition or sate. It is common to run TRY / Catch error handling in a SPROC and in the Catch block you may choose to not Rollback a transaction. Of course you can always Raise or Throw an error, but it might suit your needs better to simply return a value that indicates an error condition. So, let’s modify the previous example to include a Return value.
Create PROC HumanResources.AddDepartment
@Name nvarchar(50), @GroupName nvarchar(50), @DeptID smallint OUTPUT
AS
@Name nvarchar(50), @GroupName nvarchar(50), @DeptID smallint OUTPUT
AS
SET NOCOUNT ON;
IF (@Name = '') OR (@GroupName = '')
RETURN -1;
INSERT INTO AdventureWorks.HumanResources.Department (Name, GroupName)
VALUES(@Name, @GroupName);
SET @DeptID = SCOPE_IDENTITY();
RETURN 0;
GO
/ TEST the procedure /
DECLARE @dept int, @result int;
EXEC @result = HumanResources.AddDepartment
'Redundancy', 'Department of Redundancy Department', @dept OUTPUT;
DECLARE @dept int, @result int;
EXEC @result = HumanResources.AddDepartment
'Redundancy', 'Department of Redundancy Department', @dept OUTPUT;
IF (@result = 0)
SELECT @dept;
ELSE
SELECT 'Error during insert';
GO
In this change, I first test the Input parameters to ensure that they contain an actual value and if either contains no value the SRPOC returns a -1. As soon as the SPROC encounters a RETURN statement, it completes the RETURN and then stops. The following code never runs. If, however, the caller supplies a value for both parameters, the insert occurs and then returns a 0. Take note of the way the SPROC is called. A variable is declared (@result) to contain the return value and the SPROC is called by executing it with @result = HumanResources.AddDepartment.
Clearly, this is not a FULL description of how to create a SPROC. However, I hope this gave you some insight into creating a parameterized procedure.
For more information check out the full CREATE PROCEDURE article from SQL Books Online:
http://technet.microsoft.com/en-us/library/ms187926.aspx
For more information check out the full CREATE PROCEDURE article from SQL Books Online:
http://technet.microsoft.com/en-us/library/ms187926.aspx
Comments
Post a Comment