Performance, User Defined Function Or Sub Query
Mar 11, 2008
What is better for performance, using a user defined function or a sub query to perform an aggregate calculation in a select statement. for eg would it be best to call a user defined function which performs the below calculation of the sub query shown. I'm speaking purely from a performance point of view.
SELECT o.ordersid,
o.orders,
(SELECT SUM(i.total) FROM items AS i WHERE i.ordersid = o.ordersid) AS [total]
FROM order AS o
View 15 Replies
ADVERTISEMENT
Apr 30, 2008
I'm experiencing quite an interesting issue with our database. We're currently running SQL Server 2005 Standard (x64) (v9.00.3054.00) and I have a block of code that just insists on not working within a UDF. Everything I've read seems to point that I'm doing this corerctly but apparently I'm missing something.
Executing the query below:
DECLARE @sp DATETIME
DECLARE @ep DATETIME
DECLARE @trackType INT
SET @sp = '4/21/2008'
SET @ep = '4/28/2008'
SET @trackType = 1
SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
Yields no issues however if I put this into a UDF, it runs and never finishes executing. I'm not quite sure what my issue was so I'm hoping someone could point out where my goof is.
Here is the function:
CREATE FUNCTION [dbo].[fnEmployeeSchedulingResults]
(
@sp DATETIME,
@ep DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
)
Any help would be greatly appreciated!
-Justin
View 19 Replies
View Related
Apr 19, 2006
I have the following procedure, that calls a Padding function to pad characters to a field.
Here is what the procedure looks like
Code:
CREATE PROCEDURE [dbo].[Pad_Left]
@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT
AS
DECLARE @Query Varchar(5000)
SET @Query = 'UPDATE ' + @Table + '
SET ' + @Column + ' = dbo.Function_PadLeft(' + @Column + ', ''' + @PadChar + ''', ' + @PadToLen + ')'
EXECUTE(@Query)
GO
When I run this I get the error
Server: Msg 245, Level 16, State 1, Procedure Pad_Left, Line 13
Syntax error converting the varchar value 'UPDATE Lincoln
SET baths = dbo.Function_PadLeft(baths, '0', ' to a column of data type int.
But when I just run this query, it works
Code:
CREATE PROCEDURE [dbo].[Pad_Left]
@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT
AS
UPDATE Lincoln
SET Baths = dbo.Function_PadLeft(Baths, '0', 4)
GO
Why would one work but not the other? I don't understand, as they are the same thing, just one calls the function dynamically?
I must be missing something very obvious
Thanks for any help!
View 2 Replies
View Related
Aug 1, 2005
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies
View Related
Mar 22, 2006
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
View 1 Replies
View Related
Jan 10, 2007
I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE FUNCTION GetStartDate (@startdate datetime, @days int) RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules. If 4 business days means you count starting tomorrow, set counter to 0. If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date. we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
--If the day is between 2 and 6 (meaning it's a week --day and the day is not in the holiday table, we'll --increment the counter IF (DATEPART(dw, @enddate) between 2 and 6) AND (@enddate not in ( select HolidayDate from tFederalHoliday where [HolidayYear] = datepart(yyyy,@enddate) ) ) BEGIN set @counter = @counter + 1 END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------
View 1 Replies
View Related
Aug 17, 2005
Hi everyone,
I am tring to pass acomma delimited string to a function and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this. How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks
View 1 Replies
View Related
Dec 14, 1999
How can I create a user defined function in SQL and call it inline from a SQL statement? I need the ability to execute a statement such as:
select myFunc(x,y) from table
I need myFunc(x,y) to return a value, not a recordset and use that value in the select statement. Any and all ideas would be appreciated.
View 1 Replies
View Related
Aug 2, 1999
Dear friends,
I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.
Thanks in advance!
Kai
View 2 Replies
View Related
Jun 23, 2004
I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?
CREATE FUNCTION fnShipments ()
RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
AS
BEGIN
INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth =
(SELECT MIN(EffectiveMonth)
FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
(SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth Is Null )
) AS Territory
FROM Shipments
ORDER BY Shipments.AccountID, Shipments.DateInvoice;
RETURN
END
View 1 Replies
View Related
Nov 10, 2004
I need a UDF that will do the following:
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF
I would also like now to be my getdate(), but it seems like you can't use it in UDF
View 1 Replies
View Related
Mar 20, 2004
I create the following UDF just for learning purposes and I cannot seem to get it to return a value.
Stored Procedure
REATE PROCEDURE usp_GetXSection
@fWidth float,
@fHeight float,
@fResult float OUTPUT
AS
BEGIN
set @fResult = [dbo].[XSECTION](@fWidth, @fHeight)
END
GO
Ane the following UDF
CREATE FUNCTION XSECTION
(@Width float, @Height float)
RETURNS float
AS
BEGIN
RETURN (@Width * @Height)
END
I call it using ADO in a c++ application, but all returns empty
Any ideas? C++ code to follow.
Mike B
void CFormTemplateSetup::OnCalculate()
{
_CommandPtr pCmd(__uuidof(Command));
_ParameterPtr pWidth(__uuidof(Parameter));
_ParameterPtr pHeight(__uuidof(Parameter));
_ParameterPtr pResult(__uuidof(Parameter));
try
{
pCmd->ActiveConnection = GetDocument()->GetConnection();
pCmd->CommandText = _T("usp_GetXSection");
pCmd->CommandType = adCmdStoredProc;
_variant_t vWidth, vHeight;
vWidth.vt = VT_R4;
vHeight.vt = VT_R4;
vWidth.fltVal = 10;
vHeight.fltVal = 10;
pWidth = pCmd->CreateParameter(_T("@fWidth"), adInteger, adParamInput, sizeof(int), vWidth);
pHeight = pCmd->CreateParameter(_T("@fHeight"), adInteger, adParamInput, sizeof(int), vHeight);
pResult = pCmd->CreateParameter(_T("@fResult"), adDouble, adParamOutput, sizeof(float), vtMissing);
pCmd->Parameters->Append(pWidth);
pCmd->Parameters->Append(pHeight);
pCmd->Parameters->Append(pResult);
_variant_t vResult = pResult->Value;
float fArea = pResult->Value.fltVal;
CString csArea;
csArea.Format("Area = %d", fArea);
::AfxMessageBox(csArea);
}
catch(_com_error& e)
{
::AfxMessageBox(e.Description());
}
}
View 1 Replies
View Related
Apr 17, 2007
Hi,
Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.
View 13 Replies
View Related
Apr 6, 2006
I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.
To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.
The access function is as follows:
Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long
If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If
End Function
The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.
I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.
Any direction would be greatly appreciated!
View 6 Replies
View Related
Aug 28, 2007
hai,
the problem is - I have created a userdefined function using SQL 2000
create function getfulldate (@date varchar(10))returns datetimeasbegindeclare @getfulldate datetime set @getfulldate = dateadd (mi,55,@date)
return @getfulldateend
and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp
This works fine and what I need was, I need to invoke the user-defined function like
select *, getfulldate('2006-05-03') from emp that is, without using "dbo".
If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.
So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.
anyone guide me, thanks!
View 3 Replies
View Related
Aug 4, 2005
Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
View 2 Replies
View Related
Feb 28, 2006
I have a sproc that passes an ID to a user defined function. I just realized that the udf can only return scalar type value. I need it to return a varchar(10). How can i do this with similiar functionality. Any ideas?
View 1 Replies
View Related
Jun 9, 2004
Hi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.
View 11 Replies
View Related
Aug 13, 2004
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @currYYMM VARCHAR(20)
SET @currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
...............
.....................
View 2 Replies
View Related
Nov 1, 2006
Hi,
While i execute the followgin code. it gives error. Please resolve it as while calling the function i want to use getdate() or any other dynamic variable or any select such as
select title from SalesByStore( select admission_Dte from addmission )
It gives error.
--------------------------------------------------------------------------
use pubs
go
createFUNCTION SalesByStore (@storeid datetime)
RETURNS TABLE
AS
RETURN (SELECT top 5 title, qty
FROM sales s, titles t
)
go
select title from SalesByStore(getdate())
View 1 Replies
View Related
Mar 3, 2004
Hi,
I posted a question sometime back about any one has written a function which converts a given date to a a new timezone.
As it turns out I have actually written the function and it is functionally sound.
the issue I am not facing is the performace.
The Algorithm is pretty simple.
step 1
I have create a table called TZ1 which has the TImezoneCODE (PST,EST..), daylightsavingsstart (DATETIME on which the daylight savings start), daylightsavingsend ( end of DLS) and offset (EG -8 for PST, -5 for EST etc)
step 2
I check the timezone and pick out corresponding start and enddate for daylight savings.
Step 3
Depending on where the date lies I create a new date by adding offset to the original datetime (+1) for the new date and then return this new datetime.
As simple as this looks the response time is 16 times than that of a select statement without the function.
Do you see something that I can tune to get a better response time.
Heres my code
================================================== ================================================== ================================================== ================================================== ==================================
CREATE FUNCTION TC3
-- drop function tc3
(@dt datetime, @Src varchar(10), @Dest varchar(10))
Returns datetime
begin
declare @v_src varchar(10)
Declare @v_dest varchar(10)
declare @V_dt datetime
declare @v_newdt datetime
declare @v_year int
declare @v_offset float
declare @v_sd datetime
declare @v_ed datetime
-- select * into TZ1 from TZ1
-- create table #tz1 (fn int)
select @v_year = datepart (year, @dt)
if (@v_year between 2000 and 2004)
begin
-- -------------------
-- PST BEgin
-- -------------------
if (@dest = 'PST')
begin
-- select * from TZ1
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
-- AND @DT between daylightsavingsstart and daylightsavingsend
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=pst end
-- -------------------
-- PST END
-- -------------------
-- -------------------
-- EST BEGIN
-- -------------------
ELSE
if (@dest = 'EST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Est end
-- -------------------
-- EST END
-- -------------------
-- -------------------
-- HST BEgin
-- select * from timezones
-- -------------------
else
if (@dest = 'HST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int) , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Ast end
-- -------------------
-- HST END
-- -------------------
-- -------------------
-- MST BEgin
-- select * from timezones
-- -------------------
else
if (@dest = 'MST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Mst end
-- -------------------
-- MST END
-- -------------------
else
return '19990909'
end -- year end
return @v_newdt
end
================================================== ================================================== ================================================== =============================================
View 1 Replies
View Related
Jul 12, 2002
Dave writes "Anybody knows of a good resource for "generic" SQL Server User Defined Functions ?"
View 1 Replies
View Related
Jul 23, 2005
This is driving me crazy.I need to create a UDF that would return a TRUE/FALSE (bit) value basedon a comparison it does.CREATE FUNCTION dbo.SelectedByApplication(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint)RETURNS BIT ASBEGINDECLARE @SQLNVARCHAR(1000)DECLARE @Param NVARCHAR(500)DECLARE @Result intSET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + '] '+'WHERE [' + @ColumnToCompare + '] = @ValueInTable ANDApplicationID = @ApplicationID'SET @Param = N'@result int out, @ValueInTable int, @ApplicationIDint'EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,@ApplicationIDif @result > 0return 1return 0ENDAll I need the function to do is fill in a column based on whetherthere is a relation between a list of data and the item. I'm trying touse it in the following query:SELECT *, EXEC dbo.SelectedByApplication(4, 'IPM_Application_DataType','DataTypeID', DataTypeID)FROM IPM_DataType DTThe idea is to make this call and then be able to populate a list ofcheckboxes based on the information it returns. It should returnsomething similiar to:Column1 Column2 UDFColumn1 SomeValue 02 OtherValue 13 DifferentValue 04 LastValue 1After reading some of the posts and discovering you can't executedynamic SQL in a UDF I decided to split the function into a functionand stored procedure:CREATE FUNCTION dbo.SelectedByApplication(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint)RETURNS BIT ASBEGINDeclare @Result INTEXEC DynamicCompare @ApplicationID, @TableToCheck, @ColumnToCompare,@ValueInTable, @Resultif(@Result > 0)return 1return 0ENDCREATE PROCEDURE dbo.DynamicCompare(@ApplicationID int,@TableToChecknvarchar(50),@ColumnToComparenvarchar(50),@ValueInTableint,@Resultint out)ASDECLARE @SQLNVARCHAR(1000)DECLARE @Param NVARCHAR(500)SET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + ']' +'WHERE [' + @ColumnToCompare + '] = @ValueInTable ANDApplicationID = @ApplicationID'SET @Param = N'@result int out, @ValueInTable int, @ApplicationIDint'EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,@ApplicationIDI get the same error message about only being able to execute functionsand extended stored procedures in a user defined function.Does anyone have any ideas as to how I can dynamically execute thisquery? The reason I say dynamic is I need this same comparison forabout 25 different tables. Thanks!
View 2 Replies
View Related
Jul 20, 2005
Is it possible to call a user-defined function without prefixing itwith 'dbo.' within a SELECT clause somehow? Just curious; it's not abig issue but just a stylistic one for me.Thanks!Joel Thornton ~ <groups@joelpt.eml.cc>
View 1 Replies
View Related
Jul 20, 2005
I have a user defined function that may be called multiple times inthe same query - here's a rough exampleselect col1, get_random_number(), col2, col3, get_random_number() fromtable1The problem I am having is that I need to have both calls to the userdefined function return the same value if it is called multiple timesfor one row. The values returned from the query would be somethig likethis -John 23456 Engineer Junior 23456Frank 33333 Engineer Senior 33333Tom 80331 Engineer Junior 80331I have thought about having two seperate udf's likeget_random_number() and use_last_random_number(), but I'm not sure howto store the value of the get_random_number() to be used byuse_last_random_number(). These udfs are used in bulk load requests,so performance is a big issue. Any suggestions would be greatlyappreciated. I know the example is kind of silly, but I think itillustrates the problem.
View 2 Replies
View Related
Jun 1, 2006
Hi,
How can I do dynamical exec to query in user-defined function? At the end I need to return the result.
Thank's
Alexei
View 1 Replies
View Related
May 21, 2006
I have a user defined function (UDF) that takes 2 parameters. These parameters will be mapped from OLE DB source column values. I want the function result to be a new column that will be mapped to an OLE DB destination. Should this be done using a lookup task, OLE DB command task, or...? If this is possible I would appreciate a very specific example of how it's done.
BTW I tried using a lookup task with a SQL command before the UDF idea occurred to me and ran into problems using parameters in the SQL query:
Parameter Information cannot be derived from SQL statments. Set parameter information before preparing command.
I've seen the posts about going to the advanced tab and messing with the parameters there but I was hoping using a UDF would be easier, if it can be done.
Thanks.
View 2 Replies
View Related
Dec 7, 2007
Hi,
I'm trying to write an aggregat in c#.It's no problem for sum or multiplication aggregates.
But when I try to use an array to stor the values in it, to compare them with each other,
I get the Message:
Fehler 1 Type "AggregatClr.AggregatClr" is marked for native serialization, but field "Werte" of type "AggregatClr.AggregatClr" is of type "mscorlib.System.Array" which is a non-value type. Native serialization types can only have fields of blittable types. If you wish to have a field of any other type, consider using different kind of serialization format, such as User Defined Serialization. AggregatClr
And when I try it with User Defined instead of Native:
Fehler 1 Type "AggregatClr.AggregatClr" is marked for user-defined serialization, but does not implement the "System.Data.Microsoft.SqlServer.Server.IBinarySerialize" interface. AggregatClr
What does that means? How can I do that??
Is it possible to use an Array in a User Defined Aggregat??
Thanks
Pam
View 1 Replies
View Related
May 3, 2006
I wrote a UDF to get a rolling average based upon a date passed to the UDF. The error I get is:
Server: Msg 102, Level 15, State 1, Procedure fn_RollAverage, Line 26
Incorrect syntax near ')'.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION fn_RollAverage(@CURVE_DATE AS SMALLDATETIME)
RETURNS @TBLRESULT TABLE
(US0001M_Index AS FLOAT,
US0003M_INDEX AS FLOAT,
US0006M_INDEX AS FLOAT,
US0012M_INDEX AS FLOAT,
usswap2_index AS FLOAT,
usswap3_index AS FLOAT,
usswap4_index AS FLOAT,
usswap5_index AS FLOAT,
usswap6_index AS FLOAT,
usswap7_index AS FLOAT,
usswap8_index AS FLOAT,
usswap9_index AS FLOAT,
usswap10_index AS FLOAT,
usswap11_index AS FLOAT,
usswap12_index AS FLOAT,
usswap13_index AS FLOAT,
usswap14_index AS FLOAT,
usswap15_index AS FLOAT,
usswap20_index AS FLOAT,
usswap25_index AS FLOAT,
usswap30_index AS FLOAT)
AS BEGIN
DECLARE @BEG_DATE AS SMALLDATETIME
SET @BEG_DATE = (@CURVE_DATE - 30)
INSERT @TBLRESULT(
US0001M_Index,
US0003M_INDEX,
US0006M_INDEX,
US0012M_INDEX,
usswap2_index,
usswap3_index,
usswap4_index,
usswap5_index,
usswap6_index,
usswap7_index,
usswap8_index,
usswap9_index,
usswap10_index,
usswap11_index,
usswap12_index,
usswap13_index,
usswap14_index,
usswap15_index,
usswap20_index,
usswap25_index,
usswap30_index)
SELECT
AVG(US0001M_Index) AS US0001M_Index,
AVG(US0003M_INDEX) AS US0003M_INDEX,
AVG(US0006M_INDEX) AS US0006M_INDEX,
AVG(US0012M_INDEX) AS US0012M_INDEX,
AVG(usswap2_index) AS usswap2_index,
AVG(usswap3_index) AS usswap3_index,
AVG(usswap4_index) AS usswap4_index,
AVG(usswap5_index) AS usswap5_index,
AVG(usswap6_index) AS usswap6_index,
AVG(usswap7_index) AS usswap7_index,
AVG(usswap8_index) AS usswap8_index,
AVG(usswap9_index) AS usswap9_index,
AVG(usswap10_index) AS usswap10_index,
AVG(usswap11_index) AS usswap11_index,
AVG(usswap12_index) AS usswap12_index,
AVG(usswap13_index) AS usswap13_index,
AVG(usswap14_index) AS usswap14_index,
AVG(usswap15_index) AS usswap15_index,
AVG(usswap20_index) AS usswap20_index,
AVG(usswap25_index) AS usswap25_index,
AVG(usswap30_index) AS usswap30_index
FROM dbo.LiborSwap
WHERE CURVE_DATE BETWEEN @BEG_DATE AND @CURVE_DATE
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 4 Replies
View Related
Oct 4, 2006
I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help!
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
View 4 Replies
View Related
Feb 18, 2008
Hi all, I've tried all types of formats use this function and coming up with errors
the function works fine in SQL so not sure of the proper format to call a function with variaibles
have tried
EXEC EquivalentCase = dbo.udfEquivalentCase(@ConversionValue1= ?,@ConversionValue2 = ?, @CasesSold = ?, @CasesSold=?, @OwnershipCode = ?, @BusSegCode = ? )
and
dbo.udfEquivalentCase(?,?,?,?,?)
and
select
dbo.udfEquivalentCase(?,?,?,?,?)
is there somewhere that provides proper syntax for executing a user defined function from a OLE DB command?
Thanks
View 6 Replies
View Related
Feb 5, 2007
SQL Server User defined functions can be a powerful tool, but they can also create a substantial performance penalty in a query where they are called a large number of times. Sometimes it is something that must be accepted to get the job done, but there is often an alternative of putting the code from the function “in-line� in a SQL query. That also has a penalty in development time, so judgment about which way to go is called for.
I did some testing on three different methods of converting combinations of integer values of Year, Month, Day, Hour, Minute, and Second to Datetime values and compared the runtime of each. In the first method, I did the conversion in-line in the query. In the second method, I used a UDF to do the conversion using the same algorithm as the in-line query. In the third method, I used a UDF that called two more UDFs to do the conversion.
To perform the test, I loaded a table with 3,999,969 randomly generated date/times, along with the matching year, month, day, hour, minute, and seconds, in the range of 1753-01-01 to 9999-12-31. I re-indexed the table with fill factor of 100 to make the physical size as small as possible.
For the actual test, I ran queries that converted the year, month, day, hour, minute, and second on each row to a datatime, and compared it to the datetime from that row. I ran the query using the in-line conversion, single UDF (DateTime1), and with the UDF (DateTime2) that called two more UDFs (Date and Time). I ran the tests several times, and saw only minor variations in run time. The single UDF took over 8 times as long to run as the in-line conversion. The test with the UDF that called other UDFs took over 36 times as long to run as the in-line conversion, and took over 4 times as long to run as the single UDF.
These results show that there can be a substantial performance penalty for using a UDF in place of in-line code, and that UDFs that call other UDFs can also have a substantial performance penalty compared to a UDF that does not call other UDFs.
Code to load table with test data. The functions used in the script to load the test data can be found on these links:
Random Datetime Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499
Number Table Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
create table T_DATE (
[DATE] datetime not null primary key clustered ,
[YEAR]smallint not null ,
[MONTH]tinyint not null ,
[DAY]tinyint not null ,
[HOUR]tinyint not null ,
[MINUTE]tinyint not null ,
[SECOND]tinyint not null
)
insert into T_DATE
select distinct top 100 percent
[DATE] = dateadd(ms,-datepart(ms,a.[DATE]),a.[DATE]),
[YEAR]= year(a.[DATE]),
[MONTH]= month(a.[DATE]),
[DAY]= day(a.[DATE]),
[HOUR]= datepart(hour,a.[DATE]),
[MINUTE]= datepart(minute,a.[DATE]),
[SECOND]= datepart(second,a.[DATE])
from
(
selecttop 100 percent
[DATE] =
[dbo].[F_RANDOM_DATETIME]( '17530101', '99991231',newid() )
from
f_table_number_range(1,4000000) aa
order by
1
) a
order by
a.[DATE]
dbcc dbreindex(T_DATE,'',100)
exec sp_spaceused 'T_DATE','true'
select count(*) from T_DATE
Code to create functions used in the test. These functions are based on functions that Jeff posted in his blog on this link, modified with some suggestions of mine:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
create function DateTime1
(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
returndateadd(month,((@Year-1900)*12)+@Month-1,
dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,@Day-1))
end
go
create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go
create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
go
create function DateTime2
(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
go
Test code:
set nocount on
go
select [T_DATE Rowcount] = count(*) from T_DATE
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> 0+a.[DATE]
select [MS No Action] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <>
dateadd(month,((a.YEAR-1900)*12)+a.MONTH-1,
dateadd(ss,(a.HOUR*3600)+(a.MINUTE*60)+a.SECOND,a.DAY-1))
select [MS No Function] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> dbo.DateTime1(a.YEAR,a.MONTH,a.DAY,a.HOUR,a.MINUTE,a.SECOND)
select [MS DateTime1] = datediff(ms,0,getdate()-@st)
go
declare @count int
declare @st datetime
select @st = getdate()
select
@count = count(*)
from
T_DATE a
where
a.[DATE] <> dbo.DateTime2(a.YEAR,a.MONTH,a.DAY,a.HOUR,a.MINUTE,a.SECOND)
select [MS DateTime2] = datediff(ms,0,getdate()-@st)
go
Sample test results:
T_DATE Rowcount
---------------
3999969
MS No Action
------------
1773
MS No Function
--------------
9923
MS DateTime1
------------
82213
MS DateTime2
------------
357683
CODO ERGO SUM
View 15 Replies
View Related
Oct 14, 2007
Hi, I have a stored procedure that calls a scalar function. It works fine on my development database, but on my production database, I get this error when trying to execute the procedure: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.MyFunction", or the name is ambiguous. However, I am able to execute the function outside of the procedure like this: select dbo.MyFunction(0, 0) Anyone have idea? I can't figure it out.
View 6 Replies
View Related
