Prev: Best way to store image, voice, text file?
Next: Selecting dynamic number of columns into fixed-structure table
From: Frank on 1 Jul 2010 11:54
Hi,

I have the following SQL Server table valued function that I've
written to use as a parameterised view. It takes a bunch of parameters
and returns a table of results. Each of the parameters can contain a
value or can be NULL. If the parameter is NULL then no rows will be
filtered by that value. The function is called from an Excel
spreadsheet (using VBA) against a SQL Server 2005 database.

CREATE FUNCTION [dbo].[fnLoanData]
(
@loan_start_date VARCHAR(8) = NULL
, @loan_end_date VARCHAR(8) = NULL
, @loan_currency VARCHAR(3) = NULL
, @customer_number VARCHAR(8) = NULL
, @department_id VARCHAR(4) = NULL
, @loan_id VARCHAR(11) = NULL
, @product_type VARCHAR(3) = NULL
)
RETURNS TABLE AS
RETURN(

SELECT l.*
, c.strCustomer_Name
FROM dbo.Loan_History AS l
LEFT OUTER JOIN dbo.Customer AS c
ON l.Borrower_Id = c.Customer_ID
WHERE (l.Loan_Start_Date >= @loan_start_date OR
@loan_start_date IS NULL)
AND (l.Loan_Start_Date <= @loan_end_date OR @loan_end_date IS
NULL)
AND (l.Loan_Currency = @loan_currency OR @loan_currency IS
NULL)
AND (l.Borrower_Id = @customer_number OR
@customer_number IS NULL)
AND (l.Department_ID = @department_id OR @department_id IS
NULL)
AND (l.Loan_Id = @loan_id OR @loan_id IS NULL)
AND (l.Product_Type = @product_type OR @product_type IS
NULL)
);

I've now been asked to change this so that a user can enter a comma-
delimited string to represent a list of items.
So for instance, instead of a single department id of 999, a user can
now enter 997,998,999.
And the code above will need to change to handle this (presumably
using an IN statement).
I'm guessing that the only way to achieve what I want is to use
dynamic SQL, which apparently isn't possible from within a function.
So is there a better (or more idiomatic) way of handling this?

Many thanks,
Frank.
From: Bob Barrows on 1 Jul 2010 12:06
Frank wrote:
> I've now been asked to change this so that a user can enter a comma-
> delimited string to represent a list of items.
> So for instance, instead of a single department id of 999, a user can
> now enter 997,998,999.
> And the code above will need to change to handle this (presumably
> using an IN statement).
> I'm guessing that the only way to achieve what I want is to use
> dynamic SQL, which apparently isn't possible from within a function.
> So is there a better (or more idiomatic) way of handling this?
>
Check out Erland's articles on using arrays and lists:
http://www.sommarskog.se/arrays-in-sql.html

--
HTH,
Bob Barrows


From: Eric Isaacs on 1 Jul 2010 14:29
The easiest way to do it and keep it in a FUNCTION would be to create
multiple parameters for each instance of a department. It really
depends on the requirements if this is politically feasible. Assuming
that the spreadsheet builds the function call and that the user isn't
interacting with the T-SQL function call directly, the user could key
998, 999, 997 into a cell and the code could could parse it out into 3
(or X) parameters that get passed to the function. The limitation
here is that you have to set a max number of departments that can be
included by creating a @Department1 ... @DepartmentX parameters for
each possible department parameter. Currently you have a string of
length 4. Your existing function will also need to limit the number
of parameters as well, just based on the length of that string. The
question is, do you want that string to be able to handle 999
departments at a time or say 10 at a time?

Another option would be to do the department check outside of SQL.
Limit the departments after the data is returned in the result set.

Beyond this, you could use a stored procedure and then you have a lot
more flexibility in how you handle it. You can call a stored
procedure from a function.

You could also try something like this where you parse through the
department ids and insert the data into the return table with multiple
inserts...

CREATE FUNCTION [dbo].[fnLoanData]
(
@loan_start_date VARCHAR(8) = NULL,
@loan_end_date VARCHAR(8) = NULL,
@loan_currency VARCHAR(3) = NULL,
@customer_number VARCHAR(8) = NULL,
@department_ids VARCHAR(400) = NULL,
@loan_id VARCHAR(11) = NULL,
@product_type VARCHAR(3) = NULL
)
RETURNS @LoanData TABLE
(
CustomerName VARCHAR(100)
)
AS BEGIN
DECLARE
@DepartmentId VARCHAR(4),
@Position INT

IF @Department_ids IS NOT NULL
BEGIN

SET @department_ids = LTRIM(RTRIM(@department_ids)) + ','
SET @Position = CHARINDEX(',', @department_ids, 1)

IF REPLACE(@department_ids, ',', '') <> ''
BEGIN
WHILE @Position > 0
BEGIN
SET @DepartmentId =
LTRIM(RTRIM(LEFT(@department_ids, @Position - 1)))
IF @DepartmentId <> ''
BEGIN
INSERT INTO
@LoanData (CustomerName)
SELECT
--l.*,
c.strCustomer_Name
FROM
dbo.Loan_History AS l
LEFT OUTER JOIN
dbo.Customer AS c ON l.Borrower_Id = c.Customer_ID
WHERE
(l.Loan_Start_Date >=
@loan_start_date
OR @loan_start_date IS
NULL)
AND (l.Loan_Start_Date <=
@loan_end_date
OR @loan_end_date IS
NULL)
AND (l.Loan_Currency =
@loan_currency
OR @loan_currency IS
NULL)
AND (l.Borrower_Id =
@customer_number
OR @customer_number
IS NULL)
AND (l.Department_ID =
@departmentid)
AND (l.Loan_Id = @loan_id
OR @loan_id IS NULL)
AND (l.Product_Type =
@product_type
OR @product_type IS
NULL)

END
SET @department_ids =
RIGHT(@department_ids, LEN(@department_ids) - @Position)
SET @Position = CHARINDEX(',',
@department_ids, 1)
END
END
END

ELSE
BEGIN
INSERT INTO
@LoanData (CustomerName)
SELECT
--l.*,
c.strCustomer_Name
FROM
dbo.Loan_History AS l
LEFT OUTER JOIN dbo.Customer AS c ON l.Borrower_Id
= c.Customer_ID
WHERE
(l.Loan_Start_Date >= @loan_start_date
OR @loan_start_date IS NULL)
AND (l.Loan_Start_Date <= @loan_end_date
OR @loan_end_date IS NULL)
AND (l.Loan_Currency = @loan_currency
OR @loan_currency IS NULL)
AND (l.Borrower_Id = @customer_number
OR @customer_number IS NULL)
AND (l.Loan_Id = @loan_id
OR @loan_id IS NULL)
AND (l.Product_Type = @product_type
OR @product_type IS NULL)
END

RETURN
END

....That SQL is untested, but hopefully you get the idea.


-Eric Isaacs
From: Frank on 2 Jul 2010 06:38
Hi Eric,

Amazing! And works straight out of the box as well.

Many thanks,
Frank.
From: Eric Isaacs on 2 Jul 2010 16:22
Yeah, I find that testing is for amateurs. :o) No, Seriously, I'm
glad it worked out of the box. The multiple insert method isn't the
most efficient solution. A function that calls a stored procedure
would probably allow you to do it with one statement instead of
multiple statements. But if the multiple departments isn't very
common, then this approach is probably the most cost-effective from a
development time standpoint.

-Eric Isaacs
 | 
Pages: 1
Prev: Best way to store image, voice, text file?
Next: Selecting dynamic number of columns into fixed-structure table