MS SQL Server :: User Defined Function: Convert String Value Of Table To Table Object

Handling Large Queries In A Table Valued User Defined Function
Hello,

We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .

What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.

Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:

Sec / Actual Rows Returned
0        0
10 237
20 447
30      481
40      585
50      655
60      725
70 793
80 860
90      940
100    1013
110    1081
120    1115
130    1151
140    1217
150    1250
160    1325
170    1325
180    1430
190    1467
200    1502
210    1539
220    1574
230    1610
240    1645
250    1679
260    1715
270    1750
280    1787
290    1822
300    1857
310    1892
320    1923
330    1956
340    1988
350    1988
360    2022
370    2060
380    2094
390    2094
400    2130
410    2160
420    2209
430    2237
440    2237
450    2274
460    2274
470    2308
480    2342
490    2380
500    2380
510    2418
520    2418
530    2451
540    2480
550    2493
560    2531
570    2566

It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).

The minute breakdown during my trial is as follows:
1 = 655   (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)

As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.

Allow me to explain the code in further detail:

[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{

ArrayList listItems = new ArrayList();

SPToSQLService service = new SPToSQLService();

[...]

DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service

//Load the DS to the ArrayList

return listItems;
}

public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{

ArrayList item = (ArrayList) obj;

col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}

As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.

Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.

Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.

Thanks,

Dragan Radovic

View Replies !

SQL Server User Defined Function Treating String As Integer
I have a User Defined Function in my SQL Server 2000 database which takes a string and adds an integer quantity to it. The function basically takes the string (string because of first character and spaces) and adds an integer to it creating a new number (starting number and ending number concept).If I pass in a string that has a letter for the first character, it works fine. However, if the first character is an integer it trims out all 0s and whitespace and ruins the necessary formatting. Note, the formatting is always the same - x xxx xxxx xx. Any help or ideas would be appreciated.Example:D 499 8900 01 plus a Quantity of 10 returns D 499 89000 11 which is perfect.However,0 076 0000 03 plus a Quantity of 1 returns 764 764 (it should be 0 076 0000 04)------------------------------------CREATE FUNCTION [dbo].[NEW_End_NR2]( @OldStr as char(20), @Quantity int )RETURNS @NewNR_Tbl TABLE (New_EndNr char(20) primary key)AS BEGIN DECLARE @NewStr char(20) DECLARE @addNr integerBEGIN Set @addNr = @Quantity set @NewStr = (select REPLACE(@OldStr,left(@OldStr, 1),'')) set @NewStr = (select REPLACE(@NewStr,' ', '')) + @addNr set @NewStr = (SELECT left(@OldStr, 1) + SPACE(1) + left(@NewStr,3) + SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,3), ''), 4) + SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,7), ''), 3)) BEGIN INSERT INTO @NewNR_Tbl (New_EndNr) VALUES(@NewStr) END END RETURNEND

View Replies !

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View Replies !

Updatable Table-Defined Function Using CASE Or IF?
I am working in an Access 2003 Data Project accessing a SQL Server 2000 database, and I am looking for a simple way to parameterize the queries that I use as the recordsources for my reports and forms. I posted this question here because all questions are about SQL only.

Our accounting software has 3 (one for each currency) identically structured databases (Data_05, Data_06, Data_09) on a local server. All of my additional work is being stored in a new database (NEW_DB) i.e. views, functions, stored procedures, and additional tables. I only need read-only access to data in the accounting databases, but I will be adding several additional tables to my NEW_DB database to track additional entities that the accounting software does not allow.

I don't want to use dynamic SQL and I don't want to create 3 copies of every query (which could then be selected in VB). There are 8-10 tables in each of the 3 databases that I need to access. To select the OEORDHDR table from one of the 3 databases, I would create one of the two below options in my NEW_DB database.

An Inline Table-Defined Function

ALTER FUNCTION .fnOEORDHDR
(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'
RETURNS TABLE
AS
RETURN(
SELECT*
FROMDATA_05.dbo.OEORDHDR_SQL
WHERE'Data_05' = @Company
UNION ALL
SELECT *
FROM DATA_06.dbo.OEORDHDR_SQL
WHERE'DATA_06' = @Company
UNION ALL
SELECT *
FROM DATA_09.dbo.OEORDHDR_SQL
WHERE 'DATA_09' = @Company
)

A Stored Procedure

ALTER PROCEDURE MJC.spOEORDHDR
(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'
AS
IF @Company = 'Data_05'
SELECT *
FROM Data_05.dbo.OEORDHDR_SQL
If @Company = 'Data_06'
SELECT *
FROMData_06.dbo.OEORDHDR_SQL
If @Company = 'Data_09'
SELECT *
FROM Data_09.dbo.OEORDHDR_SQL
EndIf

The both essentially do the same thing, I pass them a parameter 'Data_05', 'Data_06', or 'Data_09', and they return the table from the desired database. Performance-wise (the SQL Query Engine is damned impressive) both of these queries perform equally well as simply running 'Select * From Data_0?.dbo.OEORDHDR_SQL', which is nice.

However, there are a few key issues/problems I need to deal with:
1. Only functions (not stored procedures) can be used in place of a table in the FROM clause in other queries. The whole reason I am parameterizing the tables is so I can parameterize the views and functions that I create using these tables, so this is a requirement.
2. UNION queries are not updatable (I don't have access to alter the database structure of the accounting databases to meet the requirements of creating a partitioned view).

For reporting on the accounting data, the first option,the function, works perfectly because I can then create views and functinos off of it and I don't need it to be updatable, case closed (although any suggestions are always appreciated).

However, I also mentioned that I will be adding tables to my NEW_DB that I would also like to parametize. For that, I will need the ability to create updatable queries. Is there any way to add IF or CASE logic to a table-valued function, and would it still be updatable? as in this fantasy query of mine that doesn't work:

ALTER FUNCTION .fnOEORDHDR
(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'
RETURNS TABLE
AS
RETURN(
IF @Company = 'Data_05'
SELECT *
FROM Data_05.dbo.OEORDHDR_SQL
If @Company = 'Data_06'
SELECT *
FROMData_06.dbo.OEORDHDR_SQL
If @Company = 'Data_09'
SELECT *
FROM Data_09.dbo.OEORDHDR_SQL
EndIf
)

Any help would be greatly appreciated, I figured I'd spend the time trying to figure this all out ahead of time before I start developing my application so that I don't have to change everything later.

Thank you!

View Replies !

Table Of User Defined Types
Hi!

I have a question about creating a user defined type: I'd like to create a table of employee objects which include objects of the type employee_t. I used this structure before in Oracle 9i and would like to know how it can be done with MS SQL Server 2000 or 2005, preferably with Enteprise Manager/Management Studio. Below is an example.

CREATE TYPE employee_t AS OBJECT (
name VARCHAR(10)
jobDesc VARCHAR(15)
...
)

CREATE TABLE Employee of employee_t

Regards,

Timo

View Replies !

Table Variable And User Defined Types
This is part of the codes of a stored procedure:

-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)

However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.

This user-defined data type is created in a user database only.

I tried to created the user-defined type into BOTH tempdb and master but same error.

How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?

In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)

and cerate udt_PRNum in tempdb only could solve it.

But now i want to use table variable.
It seems NOT work for my situation.

Any idea?

View Replies !

Table-valued User-defined Functions
Is there a way to execute a table-valued user-defined function within SSIS - one that no only returns a "table" of values but also passes in variables to the udf? Example of it in query form:

DECLARE @Var1 INT

SET @Var1=ID FROM dbo.dbtable WHERE Status = 1

SELECT * FROM udf_Foo(@Var1)

Thanks.

View Replies !

User-Defined Func Syntax For Table Return
Hi all!

I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:

SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )

But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):

CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
GO

INSERT INTO tUser VALUES (111, 'Me')

INSERT INTO tAcctGroup VALUES (1, 'NY')
INSERT INTO tAcct VALUES (11, 1, 'New York City')
INSERT INTO tAcct VALUES (12, 1, 'Syracuse')

INSERT INTO tAcctGroup VALUES (2, 'GA')
INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
INSERT INTO tAcct VALUES (22, 2, 'Savannah')
INSERT INTO tAcct VALUES (23, 2, 'Augusta')

INSERT INTO tAcctGroup VALUES (3, 'TX')
INSERT INTO tAcct VALUES (31, 3, 'Dallas')
INSERT INTO tAcct VALUES (32, 3, 'Houston')
INSERT INTO tAcct VALUES (33, 3, 'El Paso')
INSERT INTO tAcct VALUES (34, 3, 'San Antonio')

INSERT INTO tAcctGroup VALUES (4, 'CA')
INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
INSERT INTO tAcct VALUES (42, 4, 'San Francisco')

INSERT INTO tMapUserToGroup VALUES (111,2)
INSERT INTO tMapUserToGroup VALUES (111,4)
GO

CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50))
AS
BEGIN
INSERT INTO @tAcct
SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID
RETURN
END
GO

I know that I can do:
SELECT * FROM TestDB.dbo.ufnGetAcctList(4)

But I want the equivalent of:
SELECT AcctID, AcctName FROM tAcct
WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)

Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be:
AcctID AcctName
-----------------------------
21 Atlanta
22 Savannah
23 Augusta
41 Los Angeles
42 San Francisco

Any thoughts?
Thanks in advance for your help.
Cat

View Replies !

User-defined Data Types In Temporary Table
temporary tables are created in the tempdb database, which at this time does not contain any user-defined data types.

So I get an error when creating a temp table that uses user-defined types.

I just want to confirm that this is my problem and to see if anyone can give advice on duplicating user-defined types to the tempdb programmatically rather than interactively.

View Replies !

How To Use User Defined Variables As Table Names In SSIS
I am a SSIS novice, and need help to do the following:

Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.

I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error

TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------

In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?

One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query

exec( 'select * from ' + @table)

or just

select * from @table

Any suggestions will be appreciated.

View Replies !

How To Get Permissions On A SQL Databse Table For A User Group Defined In AD
Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL : €œSELECT * FROM fn_my_permissions('TableName', 'OBJECT')€?

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

View Replies !

Can We Pass Filed Name And Table Name As Parameter To User Defined Functions
Hi,
Following is the user defined function I want to get maximum value. It gives man an error "@strTableName must declare"

CREATE FUNCTION dbo.GetMaximum
(
@strFieldNamenvarchar(255),
@strTableName nvarchar(255)

)
RETURNS nvarchar(255)
AS
BEGIN

DECLARE @maxID int

SELECT @maxID=(SELECT IsNull(MAX(@strFieldName),0)+1 FROM @strTableName )

RETURN (@maxID)

END

View Replies !

Syntax Error In Accessing An Object (table Value Function)
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
"INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _

View Replies !

Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View Replies !

How To Convert Rtf To Text From A SQL Server Database Table Using A Function?
Hello,
Can anyone tell me, how to convert an rtf string to text string using a function in SQL Server? I got a table in SQL Server database with field datatype text(16), which holds an rtf. I've to extract it as a text using a function.
thank you,

View Replies !

Using IPAddress Object In A User Defined Data Type
Im trying to use a .NET IPAddress in my UDT. i create the assembly, but i get this error when i try to create the type in SQL Server:

Type "IPAddress.IPAddressUDTType" is marked for native serialization, but field "address" of type "IPAddress.IPAddressUDTType" is not valid for native serialization.

From what i understand about the IPAddress object in .NET it is serializable. what am i doing wrong

( note, im just doing this for research purposes and not planing to use the UDT)

View Replies !

Help With User Defined Function
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 Replies !

User Defined Function
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 Replies !

User Defined Function Help!
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 Replies !

User Defined Function
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 Replies !

RE-User Defined Function (2nd)
Can someone help me please, I have this function and it returns hours between 8h00 and 16h00 during the week

The help I need is, I need to return minutes instead of hours and it must not return any minutes in the following holiday table since we are not working
Holiday
2004-08-09 00:00:00.000
2004-09-24 00:00:00.000
2004-12-16 00:00:00.000
2004-12-26 00:00:00.000
2004-12-25 00:00:00.000
2005-01-01 00:00:00.000

CREATE FUNCTION dbo.fn_CalculateHours
(@DateFrom datetime, @DateTo datetime)
RETURNS int
AS
BEGIN
DECLARE @hFrom int, @hTo int
SET @DateFrom = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,1,@DateFrom),120) + ' 08:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,2,@DateFrom),120) + ' 08:00:00'
ELSE @DateFrom
END)
SET @DateTo = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,-2,@DateTo),120) + ' 16:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,-1,@DateTo),120) + ' 16:00:00'
ELSE @DateTo
END)
SELECT @hFrom = DATEPART(hour,@DateFrom)
SELECT @hTo = DATEPART(hour,@DateTo)
RETURN ((DATEDIFF(day,@DateFrom,@DateTo) - (DATEDIFF(week,@DateFrom,@DateTo) * 2) - 1) * 8) +
(CASE WHEN @hFrom < 8 THEN 8
WHEN @hFrom > 16 THEN 0
ELSE 16 - @hFrom
END) +
(CASE WHEN @hTo < 8 THEN 0
WHEN @hTo > 16 THEN 8
ELSE @hTo - 8
END)
END

View Replies !

User Defined Function Help?
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 Replies !

User Defined Function

View Replies !

User Defined Function
I only just been given this job as SQL server 7 "administrator". One of the many problems I am facing is trying to find out a way to write user defined functions - one of the millions of things you can do easily with Oracle. I have heard the rumours that SQL 7 does NOT come with "User Defined Functions" as a features. Please tell me this is not true.

And if it is true, please can anyone tell me a way to get around this?

Many thanks in advance

View Replies !

User Defined Function
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 Replies !

User Defined SQL Function
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 Replies !

XML And User Defined Function
Hi,

Please answer to the following questions:
Q1. Is there any way to get text data type from a user defined function ?

Q2. How can result of a select query with "FOR XML RAW" clause be converted into a single string?

-----------
Background of the above 2 questions:

I want to execute a simple select query which besides selecting different columns also calls a user defined function which should execute a select query and return back the result as XML string to the calling select query.

View Replies !

Need Help With User Defined Function
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 Replies !

User Defined Function
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 Replies !

Object Convert To String....
Hello all,Currently i am having problem with the conversion from object to string. I am using SqlCommand to draw data from DB, and the statement i used is:String ires = myCommand.ExecuteScalar().ToString();and then convert ires to int by int.Parse(ires) I have got no problem during the compilation, but it gives me the exception during the runtime...any one got any ideas? Thanks

View Replies !

Calling The User Defined Function!!!
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 Replies !

User Defined Function Problem
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 Replies !

How To Exec SQL User Defined Function?
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 Replies !

Sp_ExecuteSQL In User Defined Function
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 Replies !

Calling User-defined Function Without 'dbo.' -- Possible?
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 Replies !

User Defined Function Issue
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 Replies !

GETDATE() With A User Defined Function
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 Replies !

Getdate() In User Defined Function
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 Replies !

User Defined Function Obsolete
Hi,

sometimes after modifying a UDF (for example adding a column to the output) the statements refer it raise an error like the function has never been modifying , than in the previous example sql server doesn't find the last added column.

If I reapply the "alter" statement script of the function all works fine.It seams there is a sort of internal cache that need to be updated ...

Nobody have never been founding a similar behavior? :confused:

Thx

View Replies !

User Defined Function Problem
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 Replies !

Need Help Optizing A User Defined Function
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 Replies !

User Defined Aggregat-Function

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 Replies !

CLR User Defined Aggregate Function
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 Replies !

New Column From User Defined Function
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 Replies !

Using A User Defined Function From A OLE DB Command

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 Replies !

Exec In User-defined Function
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 Replies !

Problem With User Defined Function
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 Replies !