Create a stored procedure named spBalanceRange that accepts three optional param

Question

Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure should return a result set consisting of VendorName, InvoiceNumber, and Balance for each invoice with a balance due, sorted with largest balance due first. The parameter @VendorVar is a mask that's used with a LIKE operator to filter by that vendor name, as shown in figure 15-5. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters or with a maximum value of 0, the procedure should return all invoices with a balance due

Explanation / Answer

   --------------------------------------------- -- Set up test data: --------------------------------------------- IF OBJECT_ID('dbo.Vendors') IS NOT NULL        DROP TABLE dbo.Vendors; IF OBJECT_ID('dbo.Invoices') IS NOT NULL        DROP TABLE dbo.Invoices;  CREATE TABLE dbo.Vendors ( VendorID Int, VendorName nvarchar(30) );  CREATE TABLE dbo.Invoices ( VendorID Int, InvoiceNumber Int, InvoiceTotal Decimal(10,2) );  INSERT INTO dbo.Vendors VALUES (1, 'First Vendor'); INSERT INTO dbo.Vendors VALUES (2, 'Second Vendor'); INSERT INTO dbo.Vendors VALUES (3, 'Third Vendor');  INSERT INTO dbo.Invoices VALUES (1, 101, 330.25); INSERT INTO dbo.Invoices VALUES (1, 102, 100.00); INSERT INTO dbo.Invoices VALUES (1, 103, 215.40);  INSERT INTO dbo.Invoices VALUES (3, 104, 33.17); INSERT INTO dbo.Invoices VALUES (3, 105, 87.66); INSERT INTO dbo.Invoices VALUES (3, 106, 45.00); INSERT INTO dbo.Invoices VALUES (3, 107, 106.30);  

   create Proc dbo.spBalanceRange  @VendorVar nvarChar(40) = null,  @BalanceMin decimal(10,2) = null,  @BalanceMax decimal(10,2) = null  AS BEGIN  DECLARE @SQLString nvarchar(Max);  DECLARE @ParmDefinition nvarchar(500);  DECLARE @pVendorVar nvarchar(40); DECLARE @pBalanceMin decimal(10,2); DECLARE @pBalanceMax decimal(10,2);  -- Build the SQL string one time. SET @SQLString =      N'SELECT          VendorName,          InvoiceNumber,          Balance = InvoiceTotal FROM         dbo.Invoices         JOIN dbo.Vendors On Invoices.VendorID = Vendors.VendorID WHERE         InvoiceTotal > 0          AND VendorName LIKE @pVendorVar         AND InvoiceTotal >= @pBalanceMin         AND InvoiceTotal <= @pBalanceMax ORDER BY         InvoiceTotal DESC';  /* Execute the string with the input parameter values. */ SET @pVendorVar = CASE WHEN @VendorVar IS NULL THEN '%' ELSE @VendorVar END; SET @pBalanceMin = CASE WHEN @BalanceMin IS NULL THEN 0 ELSE @BalanceMin END; SET @pBalanceMax = CASE WHEN @BalanceMax IS NULL THEN 9999999.99 ELSE @BalanceMax END;  EXECUTE sp_executesql @SQLString,                        N'@pVendorVar nvarchar(40), @pBalanceMin decimal(10,2), @pBalanceMax decimal(10,2)',                       @pVendorVar = @pVendorVar,                       @pBalanceMin = @pBalanceMin,                       @pBalanceMax = @pBalanceMax; END 

   Test procedure:
 

   EXEC spBalanceRange NULL, NULL, NULL  EXEC spBalanceRange '%First%', NULL, NULL EXEC spBalanceRange '%Third%', 40, NULL EXEC spBalanceRange '%Third%', 40, 90  EXEC spBalanceRange NULL, 100, 200  

   create Proc dbo.spBalanceRange  @VendorVar nvarChar(40) = null,  @BalanceMin decimal(10,2) = null,  @BalanceMax decimal(10,2) = null  AS BEGIN  DECLARE @SQLString nvarchar(Max);  DECLARE @ParmDefinition nvarchar(500);  DECLARE @pVendorVar nvarchar(40); DECLARE @pBalanceMin decimal(10,2); DECLARE @pBalanceMax decimal(10,2);  -- Build the SQL string one time. SET @SQLString =      N'SELECT          VendorName,          InvoiceNumber,          Balance = InvoiceTotal FROM         dbo.Invoices         JOIN dbo.Vendors On Invoices.VendorID = Vendors.VendorID WHERE         InvoiceTotal > 0          AND VendorName LIKE @pVendorVar         AND InvoiceTotal >= @pBalanceMin         AND InvoiceTotal <= @pBalanceMax ORDER BY         InvoiceTotal DESC';  /* Execute the string with the input parameter values. */ SET @pVendorVar = CASE WHEN @VendorVar IS NULL THEN '%' ELSE @VendorVar END; SET @pBalanceMin = CASE WHEN @BalanceMin IS NULL THEN 0 ELSE @BalanceMin END; SET @pBalanceMax = CASE WHEN @BalanceMax IS NULL THEN 9999999.99 ELSE @BalanceMax END;  EXECUTE sp_executesql @SQLString,                        N'@pVendorVar nvarchar(40), @pBalanceMin decimal(10,2), @pBalanceMax decimal(10,2)',                       @pVendorVar = @pVendorVar,                       @pBalanceMin = @pBalanceMin,                       @pBalanceMax = @pBalanceMax; END 

   Test procedure:
 

   EXEC spBalanceRange NULL, NULL, NULL  EXEC spBalanceRange '%First%', NULL, NULL EXEC spBalanceRange '%Third%', 40, NULL EXEC spBalanceRange '%Third%', 40, 90  EXEC spBalanceRange NULL, 100, 200  

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.