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...
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...
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 ...
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...
How would I return multiple values (say, a number and a string) from a user-defined function in SQL Server? ...
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...
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...
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...
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...
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 ...
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 ...
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....
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...
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...
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...
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...
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...
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, ' ', '-'), ',', ''), '/', '-'), ...
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 ...
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...