Solved

Dynamic SQL in Function UDF

Posted on 2011-03-03
8
514 Views
Last Modified: 2012-05-11
Hi,

following problem, SQL Server 2005:

I have a user definded function (needs to be a function because i use it within a query) that returns a value such like this:

CREATE FUNCTION [dbo].[fct_prg_GetCostsPerProject](@Project_ID int)

	RETURNS money
AS
BEGIN
	DECLARE @Result money

	SELECT     @Result=ISNULL(SUM(Costs),0)
	FROM         dbo.myTable
	WHERE     Mx_Project_fID = @Project_ID

		
	return @Result
END

Open in new window


My problem now is that the name of the table (here: myTable) depends on the variable @Project_ID that is passed to the function (for instance if @Project_ID=5 the table name would be somothing like myTable_5)

I know that dynamic SQL is not working withing UDF's but has anyone an idea for a workaround?

Temporary tables are no solution because the table handles huge amount of records

Thanks in advance
Andy
0
Comment
Question by:cas1
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35025869
the only "workaround" is to put the IF @projectid = '5' then use table mytable_5 ...
but that is very far from "dynamic" and "manageable"

sorry, but there is no solution for this.
0
 
LVL 4

Author Comment

by:cas1
ID: 35025899
Hi, angellll,

unfortunately this is impossible to manage due to the table names are not limited to a certain numbers

Andy
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35025922
the only thing I see is that you build up a view on all the relevant tables, along with the field that you would map the projectid value to.
is the structure of those table(s) the same? in which case, why not have 1 single table (eventually partitioned) ...

anyhow, the view method would be like this:
CREATE VIEW dbo.MyView
AS
 SELECT 5 ProjectID, Costs
     FROM         dbo.myTable5
   UNION ALL
 SELECT 6 ProjectID, Costs
     FROM         dbo.myTable6 

Open in new window


and your function:
CREATE FUNCTION [dbo].[fct_prg_GetCostsPerProject](@Project_ID int)

        RETURNS money
AS
BEGIN
        DECLARE @Result money

        SELECT     @Result=ISNULL(SUM(Costs),0)
        FROM         dbo.myView
        WHERE     ProjectID = @Project_ID
                
        return @Result
END

Open in new window



0
 
LVL 4

Author Comment

by:cas1
ID: 35025965
My problem is: at this time that I have one table with all values in, using the @Project_ID to separate beteween the different projects. Since there are > 17.000.000 recordsets in the table (lots of projects, each project up to 800.000 recordsets) i have heavy performance issues. The table holds plenty of columns that all needs to be evaluated in different evaluations. I tried around with setting the right indexes using the execution plan of the queries and the index recommondations, but I would need to place over 15 indexes to solve the performance issuse when evaluating the table. But the disadvantage is that all operations with the table itself become very very slow (due to the number of indexes). So deleting and recreating the 800.000 records of a single project take much to long.

So my approach was to have a seperate table for each project. That would solve my performance issues.

Maybe you have a solution for this ???

Andy
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35025999
the best help I can offer is that you get a sql tuning specialist on site to help to optimize the indexes properly.
the index tuning wizards can only offer limited help in that regards, and you end up with plenty of indexes, though normally 2-3 indexes would be enough.

the first index to be sure to be created correctly would be the clustered index on the projectid being first.
that way, all the records for the same project are stored close to each other.
other indexes would then need evaluation of the queries that have to run, grouping which fields are in most/all the queries where clauses
0
 
LVL 1

Expert Comment

by:vandalesm
ID: 35026790
If you are using SQL Server 2005 or greater. I think partitioning your table is the best option.
It is very simple to setup and there are lot of information in the web.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35027238
hi

another solution that is very easy to implement is "Filtered Indexes"
if you have 2008 version.

a index that will hold only data requierd for you in queries by filter.
0
 
LVL 4

Author Closing Comment

by:cas1
ID: 35054174
Setting the clustered index works great and prevent me from a database redesign

Thanks a lot
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now