August 4, 2012 at 8:35 am
I could really use some help with this!
Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns 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 vendor name. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters, the procedure should return all invoices with a balance due.
I am using SQL server 2008, here is what I have so far.
Create Proc spBalanceRange
@VendorVar VarChar(40) = '%',
@BalanceMin money = null,
@BalanceMax money = null
AS
IF @BalanceMin IS Null
Select @BalanceMin = Min(InvoiceTotal )
From Invoices
Select @BalanceMax = Max(InvoiceTotal )
From Invoices Join Vendors
On Invoices.VendorID = Vendors.VendorID
Where (InvoiceTotal >= @BalanceMin) AND
(Vendorname Like @VendorVar)
August 6, 2012 at 2:42 am
Hi
Please can you post DDL, sample data and expected outcomes so that we can help you out further.
The second link in my signature will help if you get stuck.
Thanks
August 6, 2012 at 3:03 am
joshphillips7145 (8/4/2012)
I could really use some help with this!Create a stored procedure named spBalanceRange that accepts three optional parameters. The procedure returns 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 vendor name. @BalanceMin and @BalanceMax are parameters used to specify the requested range of balances due. If called with no parameters, the procedure should return all invoices with a balance due.
I am using SQL server 2008, here is what I have so far.
Create Proc spBalanceRange
@VendorVar VarChar(40) = '%',
@BalanceMin money = null,
@BalanceMax money = null
AS
IF @BalanceMin IS Null
Select @BalanceMin = Min(InvoiceTotal )
From Invoices
Select @BalanceMax = Max(InvoiceTotal )
From Invoices Join Vendors
On Invoices.VendorID = Vendors.VendorID
Where (InvoiceTotal >= @BalanceMin) AND
(Vendorname Like @VendorVar)
Remove the vendorname like from the filter it will select all the records for the vendor.
Select @BalanceMax = Max(InvoiceTotal )
From Invoices Join Vendors
On Invoices.VendorID = Vendors.VendorID
Where (InvoiceTotal >= @BalanceMin) AND
Hope this helps you.
August 6, 2012 at 3:05 am
duplicate post, and from other posts comments seems like homework.
August 6, 2012 at 4:51 am
Hi
You could use sp_executesql like this:
Test data:
---------------------------------------------
-- 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);
Procedure:
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
This will avoid the danger of SQL injection (see following for more details):
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/a8d68d72-0f4d-4ecb-ae86-1235b962f646.htm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply