The SQL Toolkit Can Pass Parameters to a Called Stored ProcedurePrimary Software: LabVIEW Toolkits>>Enterprise Connectivity ToolsetPrimary Software Version: 3.0 Primary Software Fixed Version: N/A Secondary Software: N/A
Problem: The SQL Toolkit can pass parameters to a called stored procedure (i.e., a set of SQL statements). This is not supported in MS ACCESS, but it is available in MS SQL Server. Solution: The SQL toolkit can pass parameters to a stored procedure. First let me explain how to invoke a stored procedure. All the testing and verification of this procedure has been done with MS SQL Server 7 (MSDE) and the NorthwindsCS example database, with the stored procedures being edited in MS Access 2000. The sample stored procedure that does not require any parameters is the "Ten Most Expensive Products" stored procedure. To invoke this simply use an SQL statement of: "Ten Most Expensive Products" The quotes must be used in this case because the stored procedure name has whitespace. The quotes seem to be necessary to delimit the name. If the name of this stored procedure is changed to "Ten", then the procedure can be invoked using an SQL statement of: Ten A stored procedure was created using the "Ten Most Expensive Products" stored procedure as a starting point. The new procedure is named Simple and the procedure definition is as follows: Alter Procedure Simple @Idx int AS SET ROWCOUNT @Idx SELECT Products.ProductName AS Simple, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC Notice the name of the variable following the word Simple is @Idx. This must be specified in the SQL statement. Basically this Stored procedure displays "@Idx number" of the Most Expensive Products. The SQL statement used to invoke this stored procedure with a parameter is as follows: "Simple" @Idx=5 The quotes around the name Simple are not necessary, but are used for the sake of consistency. Attached to this document is an archive containing two LabVIEW 5.1 VIs that demonstrate how to invoke stored procedures, with and without parameters. Of course the datasource Name Control in each is the appropriately configured DSN name as it appears in the ODBC administrator. If you do not know the name of the parameters being used in the stored procedure, then try invoking the stored procedure and look at the error code and explanation that LabVIEW returns. The following is what LabVIEW returned when the "Simple" stored procedure was invoked without any parameter: Error 673492112 occurred at Execute SQL - [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'Simple' expects parameter '@Idx', which was not supplied. Related Links: Attachments:
Report Date: 08/27/1999 Last Updated: 11/30/2004 Document ID: 1OQ7KNZ8 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
