user-defined-functions

SQL User-Defined Functions: Fetching TOP n records in a user-defined function...

How come the following doesn't work? CREATE FUNCTION Test (@top integer) RETURNS TABLE AS RETURN SELECT TOP @top * FROM SomeTable GO I just want to be able to be able to specify the number of results to be returned. [SQL Server 2000.] Thanks! ...

Does query plan optimizer works well with joined/filtered table-valued functions?

In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm bett...

SQL User Defined Function Within Select

I have a user defined function in SQL called getBuisnessDays it takes @startdate and @enddate and returns the number of business days between the two dates. How can I call that function within my select? Here's what I'd like to do.. SELECT getBusinessDays(a.opendate,a.closedate) FROM account a WHERE ... ...

Working around UDF Performance Issues - Manual caching

My system does some pretty heavy processing, and I've been attacking the performance in order to give me the ability to run more test runs in shorter times. I have quite a few cases where a UDF has to get called on say, 5 million rows (and I pretty much thought there was no way around it). Well, it turns out, there is a way to work aro...

why is UDF so much slower than subquery

I have a case where i need to translate (lookup) several values from the same table. First way I wrote it, was using subqueries: SELECT (SELECT id FROM user WHERE user_pk = created_by) AS creator, (SELECT id FROM user WHERE user_pk = updated_by) AS updater, (SELECT id FROM user WHERE user_pk = owned_by) AS owner, [name] FRO...

TSQL How do you output PRINT in a user defined function?

Basically I want to use PRINT statement inside a user defined function to aide my debugging. However I'm getting the following; Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function. Can this not be done? Anyway to aide my user defined function debugging? Cheers ...

Cannot create index on view with User Defined Function in SQL Server

In SQL Server 2005, I'm trying to use a User Defined Function in a indexed view that will be used in a full-text index. I have been able to get the UDF to work with a stored procedure and the view in question. But, when I try to create an index on the view I get the following error... Cannot create index on view "DevDatabase.dbo.View_Pe...

Union two or more tables, when you don't know the number of tables you are merging

I am working with MS SQL 2005. I have defined a tree structure as: 1 |\ 2 3    /|\ 4 5 6 I have made a SQL-function Subs(id), that gets the id, and returns the subtree table. So, Subs(3) will return 4 rows with 3,4,5,6, while Subs(2) will return one row, with 2. I have a select statement that returns the above Ids (joining this t...

What does it mean by "Non-deterministic User-Defined functions can be used in a deterministic manner"?

According to MSDN SQL BOL (Books Online) page on Deterministic and Nondeterministic Functions, non-deterministic functions can be used "in a deterministic manner" The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner. ...

How do I write, compile and use a user defined function for mysql running on Windows 2003 server?

I am using - MySQL v 5.0.22, PHP v 5.2.0, Windows 2003, Apache. My skills - Basic PHP knowledge, Basic MySQL. What is the best way for me to write and use a mysql user defined function in my PHP scripts? ...

Is a mysql user defined function suitable for signing Amazon S3 url?

Hi, I need to retrieve a result from a mysql database using a user defined function that recreates the following PHP code - $signature = urlencode(base64_encode((hash_hmac("sha1", utf8_encode($string_to_sign), awsSecretKey, TRUE)))); Are UDF's up to the job? What are the security implications for storing the AWS secret key in the U...

How can I execute a UDF on multiple rows?

I have a user defined function defined in SQL Server. I want create a single select statement that would be able to execute the function on all rows in a particular column. Is this possible and what would be the best way to do it? Thank You I saw this 'Execute table-valued function on multiple rows' and dont believe it answers my que...

Create a custom worksheet function in Excel VBA

I have a faint memory of being able to use VBA functions to calculate values in Excel, like this (as the cell formula): =MyCustomFunction(A3) Can this be done? EDIT: This is my VBA function signature: Public Function MyCustomFunction(str As String) As String The function sits in the ThisWorkbook module. If I try to use it in the ...

sql function to return table of names and values given a querystring

Anyone have a t-sql function that takes a querystring from a url and returns a table of name/value pairs? eg I have a value like this stored in my database: foo=bar;baz=qux;x=y and I want to produce a 2-column (key and val) table (with 3 rows in this example), like this: name | value ------------- foo | bar baz | qux x | ...

How can I optimize this horrendously inefficient User Defined Function in SQL Server 08

Is there anyway to optimize this horrible inefficient UDF in SQL Server 08. I am fairly new to UDF's and especially looking at them for optimizations. UPDATE: Should I be sending a column to a function like this if I wanted to perform it on each row and each column in a query? Is there a better way to go about this? Thank You ** @v...

How can I use the SqlFunctionAttribute in a database project to generate the SQL to deploy the functions myself? Visual studio fails.

Background: Visual studio fails at deploying a database project. It tries to drop functions that are already referenced (e.g. in a check constraint), rather than just adding the new ones and updating the existing ones, so the deployment always fails. As a result, I'm writing my own code to update the assembly and add/update any functi...

User-defined derived data in Django

How do I let my users apply their own custom formula to a table of data to derive new fields? I am working on a Django application which is going to store and process a lot of data for subscribed users on the open web. Think 100-10,000 sensor readings in one page request. I am going to be drawing graphs using this data and also showing ...

Refactoring PostgreSQL SProcs

Hi, with a moderate PostgreSQL installation we accumulated quite a few stored procedures/functions and types. Now in the lowest level composite type (i.e. 3 types are built with it and a myriad functions reference any of those types) one element of the type is of wrong type (i.e. smallint instead of bigint), thus handling it is identi...

SQL Concat field from multiple rows.

I would like to create a function that returns a concatinated string of a given field of given query. Here is what I did. And this one gives me an error. Must declare the table variable "@qry". CREATE FUNCTION dbo.testing ( @qry varchar(1000), @fld varchar(100), @separator varchar(15) = '; ' ) RETURNS varchar AS...

User Defined Functions in Excel and Speed Issues

I have an Excel model that uses almost all UDFs. There are say, 120 columns and over 400 rows. The calculations are done vertically and then horizontally --- that is first all the calculations for column 1 are done, then the final output of column 1 is the input of column 2, etc. In each column I call about six or seven UDFs which call o...