Alternative to dynamic SQL in Table Valued Function?

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.
0
Frank
7/1/2010 3:54:03 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
905 Views

Similar Articles

[PageSpeed] 36

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


0
Bob
7/1/2010 4:06:33 PM
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
0
Eric
7/1/2010 6:29:25 PM
Hi Bob,

Thanks for the pointer.
When I Googled for solutions yesterday, Erland's site was top of the
results list, but curiously I was unable to access any of the links
due to corporate restrictions.
I understand that Facebook, MySpace, blogs and personal email sites
etc are often seen as being a time sink for employees, but don't know
what the problem is with Erland's site. I could speak to my tech
support team, but they are off-shore, not native English speakers and
not exactly 'pro-active'. I can only guess that there is a word or
phrase on Erland's site being taken out of context, like the word
'sex' (being used to mean gender instead of the physical act)  that is
causing the problem. I had to wait until I got home last night to
access the site.

Anyone else have the same problem?

Thanks,
Frank.
0
Frank
7/2/2010 7:53:01 AM
Hi Eric,

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

Many thanks,
Frank.
0
Frank
7/2/2010 10:38:51 AM
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
0
Eric
7/2/2010 8:22:37 PM
Reply:

Similar Artilces:

SQL 2005 Saving Stored Procedures and Saving / Implementation
I am not totally familiar with the proper terminology and I really appreciate help with the following problem: My two databases, originally from a third party, have over 250 stored procedures each. I have changed about 150 of them in one database. Due to the testing and implementation requirements of the application, I need to save these stored procedures from the one database, restore the two databases according to what I received from the third party, and then implement my stored procedure changes onto the two restored databases. Any suggestions as to easiest and most foolpro...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

How to change cell shading/fill based on value in another cell?
I have a sheet with two columns of experimental data. Let's say the data is in F10:F25 (column 1) & G10:G25 (column 2). There is a value in another cell (A1) that contains a 1 or a 2. If A1=1, I want to turn shading in column 1 agree and in column 2 off. If A1=2, I want to reverse that. If there a worksheet function that will do that? I was hoping for something like: =cellshading(range,color) I could then execute one of these two statements: If A1=1 then cellshading(F10:F25,"Green") cellshading(G10:G25,"None") Else cellshading(G10:G25,"Green&...

Problen with OnKeyDown function
I add OnKeyDown function to my dialog class COnKeyDownDlg but it doesn't work correctly. I don't see any message boxes. ////////////////// void COnKeyDownDlg::OnKeyDown(UINT nChar, UINT nRepCnt, UINT nFlags) { // TODO: Add your message handler code here and/or call default AfxMessageBox("Test"); switch(nChar) { case VK_END: AfxMessageBox("End Pressed"); break; default: AfxMessageBox("default"); } CDialog::OnKeyDown(nChar, nRepCnt, nFlags); } /////////////////// On 30 Jul 2006 12:22:42 -0700, "hex" <triclosan@mail.ru> wrote: ...

Printing string from string table inside for loop?
#define IDS_STR1 _T("Str1"); #define IDS_STR2 _T("Str2"); #define IDS_STR3 _T("Str3"); #define IDS_STR4 _T("Str4"); #define IDS_STR5 _T("Str5"); for(int i = 0; i < 5; i++) { ............................ ............................ } I am looking for the logic that can be used to print those strings inside the "for" loop. Any help is welcome. The #define's are put into place as part of the pre-compilation process, thats why they are also called pre-processor directive. So, you can not use a variable with a symbol defined ...

add result column to pivot table
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. Add another copy of the Amount field to the pivot table's data area. To display, the fields horizontally, drag the Data button, onto the cell that contains the word 'Total' -- there's a picture here: http://www.contextures.com/xlPivot02.html Right-click on the heading for the second Amount column Choose Field Settings Click the Options button Type a Name for the field, e.g. Percent Choose to 'Show Data as' % of column Cli...

Pivot Table Month Order
The month order as a column heading is in the correct order except that Sep is at the very end rather than after Aug. I'm new at working with pivot tables and have been unsuccessful in researching and correcting the problem. Thanks. The problem is you are using month names, which Excel, naturally sorts into alphabetical order. Your choices are: 1. Use month numbers -- at least they will sort properly. 2. Use the full date, which you can format to display just the month name. You the display that you want. and your results will be sorted properly. Regards, Fred &quo...

import pdf table
What is the best way to inmport a table in a pdf document into excel? Check out: http://www.library.mcgill.ca/edrs/services/publications/how to/PDFtoXLS/PDFtoExcel.html#basicexports HTH Jason Atlanta, GA >-----Original Message----- >What is the best way to inmport a table in a pdf document >into excel? >. > Those directions are for university students that have Adobe Acrobat installed (not just the reader). Though there is a reference immediately above that does refer to the acrobat reader Most people do not have the full Adobe Acrobat software. With just the Acrob...

pivot table page field hidden items
Is there a vba code that can determine the "pivot table page field hidden items"? I tried the "visible" property for the page field items. It returned "True" only for the "CurrentPage" item and "False" for all the others. You could set each item as the current page. Those that are hidden will create an error, instead of becoming visible. For example: '======================== Sub PageItemsHidden() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim piCurr As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFiel...

SQL 2005 for CRM 3.0
What are the special requirements do we need to do on our SQL 2005 for CRM 3.0? Does anyone have a technet article on this? We will be applying SQL 2005 Service Pack 1. Thanks, Eric Sabo There really aren't any. If you read the Implementation Guide, there are a few extra config steps related to SRS for 2005, but that is it. -- Matt Parks MVP - Microsoft CRM "Sabo, Eric" <sabo_e@cup.edu> wrote in message news:%237cImzytGHA.3964@TK2MSFTNGP04.phx.gbl... What are the special requirements do we need to do on our SQL 2005 for CRM 3.0? Does anyone have a technet a...

dynamic named range #2
thank-you, as usual, top notch! E -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=26678 ...

Creating a template for a cross-functional flowchart
Hi, I use Visio 2003. I am trying to create a template for a cross-functional flowchart. 1) The title bar needs to be 0.5in in height, containing the logo, revision number, approver name, etc. 2) This is followed by a blank frame of height 0.25in. 3) Then come 5 bands. The user should be able to add or delete bands as required. I created a cross-functional flowchart with 5 bands (There is a title bar, a blank frame, and 5 bands). I then used the shapesheet to increase the height of the title bar. Then I added the logo and other details to the title bar and saved this as a template. Af...

No data in dynamic spreadsheet
Hi One of our users is using the MS CRM 3.0 web client and terminal services. When this users exports a dynamic excel spreadsheet and enables automatic refresh no data is visible in the spreadsheet. The user is using Office 2003 and is able to view all records in CRM so the permissions are correct. Anyone have an idea as to what could be causing this? Static excel spreadsheets display data. Does the user need to connect to the SQL database in some way? Appreciate your assistance. Thanks Mark When the spreadsheet is opened, check if there is a message at the top asking to unlock the ...

Append Some of a table to another?
I have two tables tbl_Invoices and tbl_PaymentTotals: tbl_Invoices includes InvoiceNum InvoiceAmount tbl_PaymentTotals includes InvoiceNum TotalPayments I want to add TotalPayments to tbl_Invoices but only if InvoiceNum matches in both records. Here is an example of what I want to end up with InvoiceNum/////InvoiceAmount/////TotalPayments 123432123/////$10342.67 /////$8000.00 987678900/////$1234.56 ///// 456776544/////$4567.54 /////$4567.54 This will allow me to accomplish three things with further queries. 1. The invoice has "X" paid and "X" more t...

Formatting default Entity and Value
In ORM Source documents is it possible to set the default background colour (Fill) of Entity and Value elements? To save me having to continually setting the values manually. Cheers, McGiv Hi McGiv, > In ORM Source documents is it possible to set the default background > colour (Fill) of Entity and Value elements? To save me having to > continually setting the values manually. Sorry, no. The ORM info is separate from the shape info and as such a new shape is created every time you drop a given ORM object on a page. If you want them all to be a given color (which I don't thi...

Cell ref of max value in array
Using XL2002. Other answers in these discussions have given me nearly what I'm after; please would someone complete this? A large array of numbers: which cell in each row has the max value? =MATCH(MAX(range),range,0) gets me its position, e.g. 44th cell along, but how can I translate that to Column AR or cell ref AR6? Try this on for size - if it doesn't work, let me know and we can twea it. Later, Chad =ADDRESS(MATCH(MAX(range),range,0)+ROW(range)-1,COLUMN(range),1 -- cvolker ----------------------------------------------------------------------- cvolkert's Profile: http://...

selecting and inserting existing and non existing rows into table parameters and variables
I need to do the following: 1. Select all rows that exist both in the TVP @Headlines and in the table [Headlines].[Headlines] and insert them into a table variable @ExistingHeadlines. 2. Select the rows that exist only in the TVP @Headlines and not in [Headlines].[Headlines] and insert them into a table variable @NonexistentHeadlines. How would you do this? The stored procedure takes 2 totally different paths depending on whether or not the items in @Headlines already exist in the main Headlines table. 1. If they exist already, return an error telling the client that thes...

Advanced find querying data from multiple tables.
Hello, Here's my specific senario. At our Office we would like to able to find all Contacts where the Account they belong to (Parent Account) is marked (radial button Yes/No) customer. The Account has the customer, vendor or reseller attibute because it is easier to mark one Account instead of every Contact belonging to that Account. Problem is from with in advance find on Contacts I can not select any fields belonging to Accounts. I don't want to use Crystal Reports because I hate it and there would be too many reports to create. Any ideas how to do this from CRM 1.2? Can this ...

Format cells to display truncated values?
I would like to display numbers rounded or truncated by 1000 - eg $254,123 would be displayed as $254K. I do not want to change the underlying value, and I do not want to add an additional column with a calculated value Use a custom number format of $0,K -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" <Mike@discussions.microsoft.com> wrote in message news:06C70E35-F87D-4DF7-8494-A46F0E93B507@microsoft.com... >I would like to display numbers rounded or truncated by 1000 - >eg $254,123 > would be displ...

Should forms be created based on tables or queries, or does it mat
Title prettly much says it all. Is it better to create forms based on table or queries? Or, is there any real difference? If so, are there any benefits with one or the other? I'm fairly new at this and was just wondering what the logic or path of thought I should be using to create my database. Thanks in advance. I always base my forms on queries. The benefit is that you can control the order in which the records are presented by using an appropriate ORDER BY clause in the query. In addition, if not all of the fields (or not all the rows) are required for the specifi...

Table Design
I'm the treasurer of a Badminton Club. I want to create a database which will record which members monthly subscriptions are due, who has paid, and which will also keep track of what the club's bank account balance should be. I've ended up thinking I should have a single table of transactions. However, some transactions are payments (a cheque from a member, or a payment to our coach) and some are "charges", e.g. a batch of appended records to indicate that the November monthly subscription is due for each member. A screenshot of my relationships diagr...

SQL Parameter BUG
A BUG? I am trying to pass a parameter to report server such as ttp://server/ReportServer/ReportViewer.aspx?MyReport...&tmpParam='mydata'.. I build the http string in vb.net and do a process start.(hyperlink). The web calls the report(which pulls data from a stored proc by passing the param). Works great. If I pass a wildcard with a NUMBER.. say my param is '%514%', Something weird happens. The 51 is changed to 'Q' and my param looks like '%Q4%' when SQL gets it from the SQL report. (I dumped the param from the stored proc and found it to be c...

ADDRESS function
Yesterday someone was fortunate enough to provide me with the following formula, which returns the address of the cell with the maximum value in a certain range: =ADDRESS(8,9+MATCH(MAX(J8:T8),J8:T8,0)) The formula, however, returns absolute cell addresses, and I need it to return relative addresses. I've tried modifying it like this: =ADDRESS(8,9,4+MATCH(MAX(J8:T8),J8:T8,0)) but when I do, the cell in which it's entered seems to think it's text and leaves the entire thing in the cell. (It doesn't even generate any error messages--it just sits in the cell like I would.)...

Want to create sliders to change cell values in EXCEL for PC
As a math teacher, being able to view chart/graph changes as equation parameters change is an extremely valuable tool. An article in the NCTM Mathematics journal describes this capability using "Scroll Bar type Sliders" in EXCEL 2004, which is evidently for MAC's. I have a PC with EXCEL 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in th...