This question grew out of SQLServer: Why avoid Table-Valued User Defined Functions?. I began asking questions in some of the comments, and the replies to my comments moved off topic. So that you don't have to read the entire discussion: I had never heard it said that user defined functions (UDF) were slow, or to be avoided. Some links...
Hello, This is the situation. table a cola1 cola2 table b colb1 colb2 colb3 colb4 colb5 table c colc1 colc2 colc3 for every value of cola2 = colb1 and colb4 = colc12 , fetch colb2 for every value of cola2 = colb1 and colb5 = colc3, fetch colb3 calculate (colb3- colb2) * size * factor1 for every cola2. Calculate SUM((colb3- colb2) * ...
I actually want to achieve the following description This is the table argument I want to pass to the server <items> <item category="cats">1</item> <item category="dogs">2</item> </items> SELECT * FROM Item WHERE Item.Category = <one of the items in the XML list> AND Item.ReferenceId = <the corresponding value of ...
Hi, I have a cell in Excel that I want to format differently based on a user defined formula (UDF) - my formula tests whether there is a formula in the cell... I am trying to use conditional formatting with my UDF to format the cell - but it does not seem to be working. My condition is this: ="isManualPrice(R22C12)" I tried without...
CREATE VIEW View1 AS SELECT Col1, Col2, dbo.fn1(col2) as Col3 FROM TestTable /* --Instead of writing below query I created a new View View2 SELECT Col1, Col2, dbo.fn1(col2) as Col3 dbo.fn2(dbo.fn1(col2)) as Col4 FROM TestTable */ CREATE VIEW View2 AS SELECT Col1, ...
This is going to be both a direct question and a discussion point. I'll ask the direct question first: Can a stored procedure create another stored procedure dynamically? (Personally I'm interested in SQL Server 2008, but in the interests of wider discussion will leave it open) Now to the reason I'm asking. Briefly (you can read more e...
I want to write a non-CLR user-defined function in SQL Server 2005. This function takes an input string and returns an output string. If the input string is invalid, then I want to indicate an error to the caller. My first thought was to use RAISERROR to raise an exception. However, SQL Server does not allow this inside a UDF (though yo...
Hello, I am writing a user defined function to DB2 on AS/400 in Java and the strangest thing happen.. I am always getting the same result from the function even when i am changing it, even if i am dropping it and create it again and even when i specify NOT DETERMINISTIC.. Does any one have ever encountered a behavior like that? ...
Test case schema and data follow as: create table tmp ( vals varchar(8), mask varchar(8) ); insert into tmp values ('12345678',' '); insert into tmp values ('12_45678',' _ '); insert into tmp values ('12345678',' _ '); insert into tmp values ('92345678',' '); insert into tmp values ('92345678',' _ ')...
I have a firebird database that I need to recreate. It contains an external UDF function. I made an SQL dump of the DB structure using IB Expert: DECLARE EXTERNAL FUNCTION LPAD CSTRING(255), INTEGER, CSTRING(1) RETURNS CSTRING(255) FREE_IT ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf' However, I get an error when I run th...
I am using Excel 2007. I have C# code written in a separate binary. The code uses static classes and static methods on the classes. I have a reference to the DLL in my VSTO Excel Worksheet project. What do I have to add or modify to get this to work? My C# code looks like this: using System; using System.Collections.Generic; using Micr...
It's not in INFORMATION_SCHEMA.COLUMNS, so where is it? This is a schemabound inline table valued function, so it doesn't have the issues which a stored procedure might have in being able to vary its output schema(s) based on parameters. ...
My application serializes data into various XML attributes, and depending on data it might send it as text, or as base64. In the latter case, attribute name will be "attribute-base64". So, on SQL server side it is possible to use following convention to decode XML: declare @DataXml xml set @DataXml='<root v="test data"/>' ; --or: set @D...
Are there any good free C/C++ libraries that enable reading from common devices with filesystems such as UDF, and ISO9660 and extracting files/metadata etc.? So far all I've been able to find is GNUs libcdio which is promising, and some "Magic UDF" which has so many hits I'm disgusted, pushes other results in Google, and comes with a pr...
Hey, I'm new to SQL Server development and I need to deploy an unsafe UDF assembly to a 2005 server. I'm using C# and VS2008. I need to know what are the steps that i need to take to allow smooth deployment of unsafe assembly to the client's machine - the client's DBA is a very strict fellow so i need to satisfy him with a good reasons f...
I have written a complex query that will return me a list of IDs. Now I want to re-use this query so that I join the results with another query. For that I plan to put this into a Stored Proc or an UDF and then use it to insert into a temp table. Something like below 1) Put the query in a Stored Proc and insert it into temp table INSE...
Microsoft Books Online (BOL) on Using Change Data explains a misleading error messages for cdc.fn_cdc_get_all_changes_* & cdc.fn_cdc_get_net_changes_* when an invalid, out-of-range LSN (Log Sequence Number) has been passed to them. Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure ...
I've never worked with Database Functions, but my current project requires it. I need to put a common sql query into a function so we don't have to type it out in our code hundreds of times. I've got the function created, but I don't know how to use it. Here's the function code: USE [DB_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENT...
how can i decide this problem? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetDataById] () RETURNS INT AS BEGIN DECLARE @query NVARCHAR(500) DECLARE @j INT SET @query=N'select * from catalog' EXEC sp_executesql @query RETURN @j END When I try to exec this one: select dbo.GetDataById() I get an ...
Hello everyone, How can I return a table from an UDF that executes a custom query string? Eg: CREATE FUNCTION fx_AdvancedSearch ( @keywords varchar(255), (..... other params ....) ) RETURNS TABLE AS BEGIN DECLARE @SqlQuery varchar(1000) ..... SET @SqlQuery = 'my custom select string previously generated' EX...