CTEC2902 Advanced Programming

1. CTEC2902Advanced Programming Parameters In Stored Procedures Parameters

2. CTEC2902Advanced Programming • The story so far… • You know how to • Use parameters in Sub and Function procedures but ... • How to use parameters in SQL in stored procedures? • Let us find out… Parameters

3. SQL Parameters SELECT Product, QuantityFROM InventoryWHERE Warehouse = ‘Leeds' SELECT Product, QuantityFROM InventoryWHERE Warehouse = ‘Hull' Make the warehouse location a parameter SELECT Product, QuantityFROM InventoryWHERE Warehouse = ‘Coventry' Parameters

4. SQL Parameters in Stored Procedures CREATE PROCEDURE sp_Inventory_GetLocProductQuantityASSELECT Product, QuantityFROM InventoryWHERE Warehouse = ‘Leeds’ Instead of hard-coding the data, we place it in a parameter … Parameters

5. SQL Parameters in Stored Procedures CREATE PROCEDURE sp_Inventory_GetLocProductQuantity @Location NVarChar(20)ASSELECT Product, QuantityFROM InventoryWHERE Warehouse = @Location Whatever value you place here… ... gets used here Q: how do you place values in parameters? Parameters

6. Placing Values in Stored Procedure Parameters Use the AddParameter method of clsSQLServer • Create your data table • Dim dtInventory As New DataTable • Connect to your SQL-Server database, via clsSQLServer • Dim DB As New nsDataBasePortal.clsSQLServer(“SomeDB.mdf") • Place your value in the named parameter • DB.AddParameter(“@Location”, “Leeds”) • Run the stored procedure to select records for the given location • DB.Execute("sp_Inventory_GetLocProductQuantity") • Save in your data table the records selected by stored procedure • dtInventory = DB.QueryResults Value Same name as in the procedure Parameters

7. Multiple SQL Parameters CREATE PROCEDURE sp_Inventory_GetLocProductQuantity @Location NVarChar(20), @MinQIntASSELECT Product, QuantityFROM InventoryWHERE Warehouse = @Location AND Quantity <= @MinQ As many as you need Used in SQL command Q: how do we supply values for multiple parameters? Parameters

8. Placing Values in Multiple Parameters • Create your data table • Dim dtInventory As New DataTable • Connect to your SQL-Server database, via clsSQLServer • Dim DB As New nsDataBasePortal.clsSQLServer(“SomeDB.mdf") • Place your values in the named parameters • DB.AddParameter(“@Location”, “Leeds”) • DB.AddParameter(“@MinQ”, 10) • Run the stored procedure to select records for the given location • DB.Execute("sp_Inventory_GetLocProductQuantity") • Save in your data table the records selected by stored procedure • dtInventory = DB.QueryResults These lists must match the order of parameters & data types in the procedure Parameters

9. SQL Parameters Exercise Given the table Inventory (ID, Warehouse, Product, Quantity, Price, SupplierID) Write a stored procedure, with parameters, to return the quantity and price of any product at any warehouse. Also show how you would supply actual values for the parameters defined. CREATE PROCEDURE sp_Inventory_GetLocProductQuantity @Product NVarChar(40), @Warehouse NVarChar(20)ASSELECT Quantity, PriceFROM InventoryWHERE Warehouse = @Warehouse AND Product = @Product • Place your values in the named parameters (in your code) • DB.AddParameter(“@Product”, “Window Frame 12x8”) • DB.AddParameter(“@Warehouse”, “Edinburgh”) Parameters

10. SQL Parameters • Advice • Investigate how to use parameters in • DELETE • UPDATE • INSERT INTO Parameters