udf

What's a good way to check if two datetimes are on the same calendar day in TSQL?

Here is the issue I am having: I have a large query that needs to compare datetimes in the where clause to see if two dates are on the same day. My current solution, which sucks, is to send the datetimes into a UDF to convert them to midnight of the same day, and then check those dates for equality. When it comes to the query plan, thi...

Error creating UDF in SQL Server 2005

Hi, I'm trying to create a UDF in SQL Server 2005 Express as below: CREATE FUNCTION [CombineValues] () RETURNS VARCHAR(8000) AS BEGIN DECLARE @CuisineList VARCHAR(8000); RETURN ( SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + CAST(Cuisine AS varchar(20)) FROM Cuisines ) END Cuisines has the str...

Using tables in UDF's in Excel 2007

I am writing a UDF for Excel 2007 which I want to pass a table to, and then reference parts of that table in the UDF. So, for instance my table called "Stock" may look something like this: Name            Cost            Items in Stock Teddy Bear    £10              10 Lollipops         20p              1000 I have a UDF ...

CLR UDF Exception In SQL Server 2005

When I try my CLR UDF, I am getting this error: Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "getFileSize": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture...

Return multiple values from a SQL Server function

How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server? ...

User-Defined Functions SQL Server 2005 flagged incorrectly as non-deterministic?

Related to this question, I decided to check the UDFs in my data warehouse (which should largely have been deterministic), and I found several which aren't which should be. For instance: CREATE FUNCTION [udf_YearFromDataDtID] ( @DATA_DT_ID int ) RETURNS int AS BEGIN RETURN @DATA_DT_ID / 10000 END Shows up in this query: SELE...

How to make conversions from varchar to datetime deterministic?

In the tradition of this question and in light of the documentation, how does one make this function deterministic: ALTER FUNCTION [udf_DateTimeFromDataDtID] ( @DATA_DT_ID int -- In form YYYYMMDD ) RETURNS datetime WITH SCHEMABINDING AS BEGIN RETURN CONVERT(datetime, CONVERT(varchar, @DATA_DT_ID)) END Or this one (because of t...

Efficient ISNUMERIC() replacements on SQL Server?

So I just spent 5 hours troubleshooting a problem which turned out to be due not only to the old unreliable ISNUMERIC but it looks like my problem only appears when the UDF in which ISNUMERIC is declared WITH SCHEMABINDING and is called within a stored proc (I've got a lot of work to do to distill it down into a test case, but my first n...

How do I supply the FROM clause of a SELECT statement from a UDF parameter

In the application I'm working on porting to the web, we currently dynamically access different tables at runtime from run to run, based on a "template" string that is specified. I would like to move the burden of doing that back to the database now that we are moving to SQL server, so I don't have to mess with a dynamic GridView. I thou...

How to trace T-SQL function calls

I'm trying to debug a rather complicated formula evaluator written in T-SQL UDFs (don't ask) that recursively (but indirectly through an intermediate function) calls itself, blah, blah. And, of course, we have a bug. Now, using PRINT statements (that can then be read from ADO.NET by implementing a handler for the InfoMessage event), I ...

SQL Server 2005 Scalar UDF performance

I have a table where I'm storing Lat/Long coordinates, and I want to make a query where I want to get all the records that are within a distance of a certain point. This table has about 10 million records, and there's an index over the Lat/Long fields This does not need to be precise. Among other things, I'm considering that 1 degree ...

How do I write a User Defined Function?

I would like to write this as a user defined function: private double Score(Story s){ DateTime now = DateTime.Now; TimeSpan elapsed = now.Subtract(s.PostedOn); double daysAgo = elapsed.TotalDays; return s.Votes.Count + s.Comments....

Simulating group_concat MySQL function in MS SQL Server 2005?

Hello everyone. So I'm trying to migrate a MySQL-based app over to MS Sql Server 2005 (not by choice, but that's life). In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat function fairly frequently. group_concat, by the way, does this: given a tabl...

SQL Cursor w/Stored Procedure versus Query with UDF

I'm trying to optimize a stored procedure I'm maintaining, and am wondering if anyone can clue me in to the performance benefits/penalities of the options below. For my solution, I basically need to run a conversion program on an image stored in an IMAGE column in a table. The conversion process lives in an external .EXE file. Here ar...

NULL parameters in scalar UDFs on MSSQL

The option "RETURNS NULL ON NULL INPUT" for a scalar UDF (see CREATE FUNCTION) stops the function body executing if the parameter is null and simply returns NULL. That is, it short circuits. Does anyone know how it handles multiple parameters? It would be useful to short circuit a function call with multiple parameters, say if the fir...

Why does SQL 2005 say this UDF is non-deterministic?

I have the following function: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[IP4toBIGINT]( @ip4 varchar(15) ) RETURNS bigint WITH SCHEMABINDING AS BEGIN -- oc3 oc2 oc1 oc0 -- 255.255.255.255 -- Declared as BIGINTs to avoid overflows when multiplying later on DECLARE @oct0 bigint, @oct1 b...

Help! - SQL - IF Else IF logic for returning Containstable selection

Hi guys, I'm trying to use IF else If logic inside an inline table valued function for SQL and returning a containstable based on that logic. but i'm having syntax problems with the IF Else IF block. thanks for the help. since i can't parametrize the columns in the containstable i have to resort to using if else statements. here's the co...

How can I clean up this SELECT query?

I'm running PHP 5 and MySQL 5 on a dedicated server (Ubuntu Server 8.10) with full root access. I'm cleaning up some LAMP code I've inherited and I've a large number of SQL selects with this type of construct: SELECT ... FROM table WHERE LCASE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( strSomeField, ' ', '-'), ',', ''), '/', '-'), ...

Getting single records back from joined tables that may produce multiple records

I've got a student table and an enrollment table; a student could have multiple enrollment records that can be active or inactive. I want to get a select that has a single student record and an indicator as to whether that student has active enrollments. I thought about doing this in an inline UDF that uses the student ID in a join to ...

User Defined Functions in Automation Addin behave differently Excel 2007

I have created an automation addin in C# .NET and have a shim dll for it. The shim dll has all the User Defined methods in it that calls the appropriate methods in the .NET assembly. The problem that I am facing is related to Excel 2007. In Excel 2003, 1. Add a new shape to a worksheet 2. Insert >> Function >> SUM. The Function argum...