Working with Stored Procedures in an MS Access Project - A Stored Procedure Without Parameters
(Page 3 of 4 )
The screen shown above has three tabs. The Stored Procedure tab shows the options with some of them grayed out since the Group By option was not chosen first. The name of the Stored Procedure is also frozen as StoredProcedure1. This can be changed when you save the stored procedure as will be shown when we save this stored procedure.
There are other options such as Output all columns, Distinct values, and Set NOCOUNT on. The first two items do not require explanation as they mean what they say they are. Count in the context of a stored procedure in Transact SQL refers to the number of rows affected by the TSQL Statement. When you Set NOCOUNT on, the count is not returned. This gives a performance boost because the interchange of messages between the client and the server is greatly reduced. The Set NOCOUNT permission defaults to users.
Now without making any changes to this, exit from this screen and save the StoredProcedure1 as BasicSP (it was saved as BasicSingleParam but later renamed to BasicSP). This is a parameter-less procedure. You could look at the SQL from MS Access' View menu drop-down, SQLView, which shows the code shown in the next paragraph. The selections made from the table are included together with the Order By clause.
ALTER PROCEDURE dbo.BasicSP
AS SELECT ProductID, ProductName,
SupplierID, QuantityPerUnit,
UnitPrice, UnitsInStock
FROM dbo.Products
ORDER BY ProductName
The stored procedure can be run from the project's main screen by double clicking the stored procedure icon for the BasicSP stored procedure, which shows all the items in the select query packaged in the stored procedure as shown.
Modifying the BasicSP for a stored procedure which takes one input parameter
In the design view in the row for SupplierID for the column criterion, type the character ? and step out. It immediately changes to "=@Param1". This means you have created a parameter called @Param1. Notice that a filter symbol appears in the table. This is similar to creating a parameter query in MS Access. If want to have a greater number of parameters you may do so in this screen.
Now right click on an empty area and bring up the properties page for the BasicSP stored procedure. In the Stored Procedure Parameters tab you will see the @Param1 name. It also shows it is of type int. You may give a default value of, for example, 2.
The next picture shows the Data tab of this window. We make no changes to this as we are only dealing with one table.
This has changed our BasicSP. The SQL code from the View-->SQL View is as shown in the next paragraph.
ALTER PROCEDURE dbo.BasicSP
(@Param1 int = 2
)
AS SELECT ProductID, ProductName, SupplierID,
QuantityPerUnit, UnitPrice, UnitsInStock
FROM dbo.Products
WHERE (SupplierID = @Param1)
ORDER BY ProductName
Now if you close this window it gets saved to the same name, BasicSP. If you want to save it with a different name you have to do so from the project's File menu. The next window shows the result of running this altered stored procedure from the project's main window as described earlier.
The result of running this procedure, which takes the built-in parameter, is as shown below:
Now go back to the property of the stored procedure BasicSP, remove the default value and close out of that screen. Save the StoredProcedure as BasicOneParam.
The SQL View of this is as shown below. Since an existing stored procedure was modified, the Alter Procedure data modification statement was created.
ALTER PROCEDURE dbo.BasicOneParam
(@Param1 int)
AS SELECT ProductID, ProductName, SupplierID,
QuantityPerUnit, UnitPrice, UnitsInStock
FROM dbo.Products
WHERE (SupplierID = @Param1)
ORDER BY ProductName
Notice the change between the previous SQL and the one above. The @Param1 has no default value. When you double click the BasicOneParam icon in the queries window, you will bring up the window which is asking you the value of the Param1 as shown in the next screen.
For the value 7 for the Param1, the following window shows up.
This has now become a parameter query which takes one parameter. Every time you run this procedure from main window in MS Access project you will be asked to enter the parameter.
Next: Does MS Access Project support the directionality of the parameters? >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
/ 7 
