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! ...
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...
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 ... ...
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...
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...
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 ...
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...
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...
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. ...
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? ...
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...
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...
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 ...
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 | ...
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...
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...
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 ...
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...
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...
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...