Adding Support For User Defined Functions To The Ssis Expression Language
Jul 19, 2006good idea or bad idea?
View 2 Replies
ADVERTISEMENT
User-defined Functions - Help!
Sep 3, 2004Hi,
I have a search returning many 'car' records (regno, mileage, color, etc)
In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.
Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.
I have written a function as such (with some help!):
ALTER Function getAccs(@regNo varChar(20))
RETURNS varChar
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END
I was hoping that I could simply use this in the 'car' SELECT statement.
ie:
SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList
FROM tableBla etc
I'm not even sure if the function works - How can I test in SQL analyzer?
any help much appreciated,
Pete
User Defined Functions In SQL 7.0
Oct 11, 2000Hi,
I know that we cannot have User Defined Functions in SQL 7.0, but is there a work around. I am trying to standardize the date according to the time zone using a function and use it as part of a select statement.
I cannot believe that is not possible in SQL 7.0, can somebody tell me what is the work around, I am running against time. I really appreciate any help on this one.
Satish.
User Defined Functions And OLE
Aug 18, 2006Hi everyone
I am implementing an encrypted data system whereby captured data is encrypted using MS CAPICOM when written to DB and decrypted when displayed. It works well. However, each time you write the data back on update the encryption is different from the previous time.
I have a search screen that allows users to enter text data and submit it looking for matches. Of course, the user is entering unencrypted text and the DB data is encrypted.
This means that you can't encrypt the input data before you try to match because the encryption alogorithm used on the input data will not match that which was used to encrypt the DB data.
Are you with me so far?
So, you have to compare the uncencrypted input data with DECRYPTED DB data - in other words, decrypt the DB data on the fly within the where clause of your query.
I have accomplished this by writing a UDF that instantiates an instance of the CAPICOM encryption module and calling the UDF when applying the query
eg where udf(columnname1) = 'inputtext' or udf(columnname1) = 'inputtext'.
It works, I get the results that I want.
But, alas, the performance has taken a search severe hit.
Has anyone else ventured down this road?
Is there a better way of doing this?
Thanks
Ray
User Defined Functions
Jan 27, 2004hi all,
I made myself a user defined function, it works great, but is there a way to make it available to all databases?
cheers,
alex
User Defined Functions
Feb 19, 2004When I define a UDF in SQL 2000 I can't execute it without qualifiction. Here's the code:
CREATE FUNCTION GetLastShipDate(@DO_No CHAR(10))
RETURNS CHAR(10)
-- DROP FUNCTION GetLastShipDate
BEGIN
DECLARE @v_last_ship_date CHAR(10)
SELECT @v_last_ship_date = COALESCE(CONVERT(CHAR(10), MAX(Asn.Asn_Ship_DateTime), 110), '')
FROM Asn,Asn_Do
WHERE Asn_Do.Asn_Number=Asn.Asn_Number
AND Asn_Do.DO_Number = @DO_No
AND Asn.ASN_STATUS in ('SENT','RESENT')
RETURN @v_last_ship_date
END
So I have to execute this with:
SELECT dbo.GetLastShipDate('T010215004')
I want to execute it with
SELECT GetLastShipDate('T010215004')
What am I doing wrong?
User Defined Functions
Apr 15, 2007Hi,
Iam new to sql server. I want to create a user defined function to get date in a year(starting day of a week-i.e sunday date) when we give week number as argument. can any body help on this issue?
User-Defined Functions In DTS
Jul 23, 2005Is there a way to call a SQL user-defined function in a DTS, perhapsin an ActiveX transformation? I want the destination table to have avalue that was modified by a pre-defined function.-Noelle
View 1 Replies View RelatedUser Defined Functions
Jul 6, 2006Hi everyone,
Today while using user defined functions, I noticed that we must write dbo. before the function name which I did not conceive why it is used like that. Why are there an exception for functions ?
Why do we not have to use the same thing while using SP or not user defined functions ?
Thanks
User Defined Functions
Nov 15, 2006declare @statusID varchar(100)
set @statusID = '1, 2'
--this works
select id from SplitWords(@statusID, ',')
--this doesn't
declare @sql nvarchar(2000)
set @sql = 'select * from Mast where ' + 'phmStatusID in ( select id from ' + 'SplitWords(@statusID, '', '')' + ') '
exec sp_executesql @sql
is what I'm trying to do not allowed? if so, whats the right way to do this?
any help appriciated. thanks.
Help With User Defined Functions
Jul 18, 2007Dear all,
I was given a project to transfer our database into sql server database.
In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .
Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \let int4 be a user definded function.
If i can€™t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?
Thanks in advance,
Best regards,
Hellen
CLR User-defined Aggregate Support Java/J#?
May 18, 2006I want to write a Java User-defined aggregate (UDA). Shall I use J#?
I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:
Msg 6265, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.
Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
From the warning, I can tell the J# UDA is not tested.
Can someone confirm whether J# UDA is supported or not?
Thanks!
-------------------------
Here is my code:
ALTER DATABASE MEDIO set TRUSTWORTHY ON
CREATE ASSEMBLY MyAgg FROM 'C:codeconsolePriceUDAobjDebugPriceUDA.dll' WITH PERMISSION_SET = unsafe
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate
GO
---------------------------------------
import System.*;
import System.Data.*;
import Microsoft.SqlServer.Server.*;
import System.Data.SqlTypes.*;
import System.IO.*;
import System.Text.*;
/** @attribute Serializable() */
/** @attribute 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 implements 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.get_IsNull())
{
return;
}
this.intermediateResult.Append(value.get_Value()).Append(',');
}
/// <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.get_Length() > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.get_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());
}
}
Learning About User Defined Functions
May 26, 2006I am getting some trial by fire here as I need to update some user defined functions in an application a co-worker built. He has since left and I've been asked to take over this project since I had a VB 6.0 and regular ASP class 6 years ago ... obviously quite a transition! I have started to figure out a bit but I can't find much on creating my own User Defined Functions and/or the best ways to test ones that I need to edit. My first one came back with this after I added it to SQL Server 2005 through Enterprise Manager... Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.fn_User_Data'.Thanks for any tips,Andrew
View 1 Replies View RelatedUser Defined Functions In SQL Server 7.0
Nov 4, 2004hai,
I have 3 fields in my table say (F1, F2, F3). I want to get the max value out of the three fields for each row. I can create a user-defined function which accepts 3 arguments and then return the max value if i am using SQL Server 2000. But now i am using only SQL Server 7.0 (it does not support user-defined functions :confused: )
So any one could kindly let me know how could i do it in SQL Server 7.0
Thnks in advance
User Defined Functions And EXEC
Mar 5, 2008I'm using MS SQL Server 2005.
I want to simulate a table, using a Multi-statement Table-Value User-Defined Function, but I need the function build the SQL statement from scratch each time so I can dynamically define values like the table it references. The only way I know how to run the query after it has been defined in this manner is to run an EXEC command. However I'm getting an error basically saying that the EXEC command is off limits in a User Defined Function.
The Exsact Error is:
>[Error] Script lines: 1-108 ------------------------
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Is there some way to get around this limitation of User Defined Functions. Or perhaps a way to simulate the functionality of Multi-statement Table-Value User-Defined Functions in a Store Procedure, specifically the ability to run where statements, or transform the data on the fly without re-writing the Stored Procedure every time.
The code I’m trying to run is below.
(Note: The code works as a stored procedure, so I'm sure that the core of the statment is correct)
CREATE FUNCTION [dbo].[TrendLine]
(
@Summary as smallint,
@Start as datetime,
@End as datetime,
@Table as varchar(100),
@X as varchar(100),
@Count as varchar(100),
@Duration as varchar(100)
)
RETURNS
@TrendLineTable table (
DATE_DAY datetime
, EQ_REGION varchar(25)
, EQ_MARKET_CLUSTER varchar(30)
, Y float
, X int
, DURATION float
, FORMULA varchar(100)
, a float
, b float
, EX int
, EY float
, EX2 int
, EXY float
, N int
)
AS
BEGIN
DECLARE @SQL as varchar(3000)
, @CountText as varchar(150)
, @StartText as varchar(50)
, @EndText as varchar(50)
SET @StartText = 'cast( ' + char(39) + cast( @Start as varchar(20) ) + char(39) + ' as datetime ) '
SET @EndText ='cast( ' + char(39) + cast( @End as varchar(20) ) + char(39) + ' as datetime ) '
IF @Summary = 1
BEGIN
SET @CountText = 'sum'
END
ELSE
BEGIN
SET @CountText = 'count'
END
SET @SQL = 'INSERT INTO @TrendLineTable
DECLARE TrendlineC cursor for
SELECT a.DATE_DAY
, s2.EQ_REGION
, s2.EQ_MARKET_CLUSTER
, ( ( EY - ( b * EX ) ) / N ) + ( b * X ) AS Y
, X
, Y AS DURATION
, cast( b as varchar(100) ) + ' + char(39) + 'x + ' + char(39) + ' + cast( ( EY - ( b * EX ) ) / N as varchar(100) ) AS FORMULA
, ( EY - ( b * EX ) ) / N AS a
, b
, EX
, EY
, EX2
, EXY
, N
FROM (
SELECT EQ_REGION
, EQ_MARKET_CLUSTER
, sum( X ) AS EX
, sum( Y ) AS EY
, sum( X2 ) AS EX2
, sum( XY ) AS EXY
, count( X ) AS N
, ( ( count( X ) * sum( XY ) ) - ( sum( X ) * sum( Y ) ) ) / ( ( count( X ) * sum( X2 ) ) - POWER( sum( X ), 2 ) ) AS b
FROM (
SELECT ' + @X + ' AS DATE_DAY
, EQ_REGION
, EQ_MARKET_CLUSTER
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
, cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
, POWER( row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ), 2) X2
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) * cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + ' ( ' + @Count + ' ) AS XY
FROM ' + @Table + '
WHERE ' + @X + ' >= ' + @StartText + '
AND ' + @X + ' < ' + @EndText + '
GROUP BY ' + @X + '
, EQ_REGION
, EQ_MARKET_CLUSTER
) s1
GROUP BY EQ_REGION
, EQ_MARKET_CLUSTER
) s2
INNER JOIN (
SELECT ' + @X + ' AS DATE_DAY
, EQ_MARKET_CLUSTER
, row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
, cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
FROM ' + @Table + '
WHERE ' + @X + ' >= ' + @StartText + '
AND ' + @X + ' < ' + @EndText + '
GROUP BY ' + @X + '
, EQ_REGION
, EQ_MARKET_CLUSTER
) a ON s2.EQ_MARKET_CLUSTER=a.EQ_MARKET_CLUSTER'
EXEC ( @SQL )
RETURN
END
User Defined Functions In SQL Server 7.0
Jul 20, 2005Hi all!!Does anybody know how I can create a function in SQL 7.0?? I have tocreate functions that return a value that can be used in a selectstatement. I think SQL Server version 7.0 doesn't support CREATEFUNCTION, does it?Ex:Select MyFunction(Parameter)From MyTableThanks a lot,
View 2 Replies View RelatedCalling User-defined Functions In Another DB
Jun 28, 2006I have a number of databases that require a set of common functions. I'd like to place all those functions in a central DB. I'm having trouble calling them using the syntax FunctionDB.GetParamLength() for example, which works within the FunctionDB database.
Any ideas/suggestions? I really don't want to maintain seperate copies of the functions across 5+ databases.
User Defined Functions To Stored Procedures
Jun 1, 2004Hello all:
Running into a brain problem here. I remeber reading an article a while back (2002?) on either Visual Studio Magazine or MSDN Magazine where there was a way to generate Stored Procedures from User Defined Functions. I need this information in order to do my job as it is also a way to cut down on time for this project I am trying to finish. Does anyone have the code or remeber what I am talking about. I just finished Kathleen Dollards article again on using XSLT to generate code but would really like to use the User Defined Functions.
I searched for the article on line but came up dry. Searched through all my magazines but could not find the article. Any help would be greatly appreciated. Bit of topic I guess but still relevant to the board.
Thanks
User-Defined String Functions Transact-SQL
May 3, 2005Ladies and Gentlemen,
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within
another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within
another character string
OCCURS() Returns the number of times a character expression occurs within another character expression
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression ...
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards
[OT] User-Defined String Functions Transact-SQL
Nov 17, 2005Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions Transact-SQL:
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
More than 6000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards.
Local Variables In User Defined Functions
Mar 24, 2004I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?
View 4 Replies View RelatedHow To Avoid Using Scalar User Defined Functions
Sep 26, 2015Following the horrendous performance of Scalar UDFs demonstrated in this thread URL....(Scalar UDF 10x slower than writing the code, long hand, in the SELECT) I now have the worry that their widespread usage in my code needs to be refactored. I guess pretty much any single-line non-table-valued scalar-function would do
CREATE FUNCTION dbo.FNSafeString
(
@strValuevarchar(8000),-- STRING Value to convert to SAFE string
@intModeint = 0-- 0=NULL as '[NULL]', 1=NULL as ''
)
RETURNS varchar(8000)
WITH SCHEMABINDING
AS
BEGIN
RETURN COALESCE(@strValue,
CASE WHEN @intMode = 0
THEN '[NULL]'
ELSE ''
END
)
END
Its a little bit too big to really want to have to write it in each place where I need to use it, and its just big enough that it is worth centralising, and it might just be that I could code it more efficiently / different / handle more "edge cases" in the future.I do have even more straightforward UDFs - just to avoid typing something "Lengthy"
CREATE FUNCTION dbo.FNDateMidnight
(
@dtDateTimedatetime-- Date/Time Value to adjust
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
RETURNDATEADD(Day, DATEDIFF(Day, 0, @dtDateTime)+1, 0)
END
Perhaps I should consider changing them to inline table valued functions, or CROSS APPLY them? but I can't think of a way to centralised them and re-use the code WITHOUT a function, can I?
Stored Procedure Vs User Defined Functions
Apr 4, 2008Hi All,
My question is :
Why we are using udf inside stored procedures ?
Will it make any performance faster for the stored procedure to execute ?
awaiting your reply.
Thanks
Renjith
User-Defined String Functions Transact-SQL (New)
May 27, 2005Ladies and Gentlemen,
I would like to offer you the following string functions Transact-SQL
GETWORDCOUNT() Counts the words in a string
GETWORDNUM() Returns a specified word from a string
AT() Returns the beginning numeric position of the first occurrence of a character expression within another character expression, counting from the leftmost character
RAT() Returns the numeric position of the last (rightmost) occurrence of a character string within another character string
CHRTRAN() Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression
STRFILTER() Removes all characters from a string except those specified
OCCURS() Returns the number of times a character expression occurs within another character expression (include overlaps)
PADL() Returns a string from an expression, padded with spaces or characters to a specified length on the left side
PADR() Returns a string from an expression, padded with spaces or characters to a specified length on the right side
PADC() Returns a string from an expression, padded with spaces or characters to a specified length on the both sides
PROPER() Returns from a character expression a string capitalized as appropriate for proper names
RCHARINDEX() Is similar to a built-in function Transact-SQL charindex but the search of which is on the right
ARABTOROMAN() Returns the character Roman number equivalent of a specified numeric expression (from 1 to 3999)
ROMANTOARAB() Returns the number equivalent of a specified character Roman number expression (from I to MMMCMXCIX)
AT, PADL, PADR, CHRTRAN, PROPER are similar to functions Oracle PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP
More than 2000 people have already downloaded my functions. I hope you will find it useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards.
User-Defined String Functions Transact-SQL
Jun 3, 2005Ya know...I don't think I would Ever be able to build those functions if I needed them.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50370
You must be a very clever developer
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Computed Fields And User-defined Functions
Apr 13, 2006Hi everyone,
I would like to get some clarification about what these two concepts are.
This is what I think:
lets say that you want the user to choose two columns to add together and then that produces a calculated result. This would go in a computed field???
And user-defined functions are functions whereby users have for example to which which two columns they were going to allow to be processed for the calculation.
Is this correct???
Cm
Stored Procedure And User-Defined Functions
Sep 25, 2006lokesh writes "1) What are the differences between "Stored Procedure" and "User-Defined Functions"?
2) Places where we use/don't use Stored Procedure/User-Defined Functions."
Passing Values In User Defined Functions
Apr 25, 2007i want to pass a value from one user defined function to another how do i do it.E.g
My first function calulate a value which is to be used by another function for calculation
my 2nd function is given below
Create Function Avg_WLPD_CFS(@Res float,@TotDay int)
Returns float
As
Begin
Return(@Res/@TotDay)
end
the value @Res is calculated from first function, how do i pass this value to the above function.
User Defined Functions && Default Parameters
Jul 23, 2005Hi,I am trying to call a user defined function(UDF) from a stored proc,using a default parameter in the called UDF (e.g. @bid_price_type int= 0 ). However the calling stored proc complains that the UDF isexpecting a 2nd parameter when I only provide the @test parametervalue. Are default parameter values supported in UDF's?I wld really appreciate anybody's help. Thankyou in advance.UDF code:CREATE FUNCTION get_bid_price (@test int, @bid_price_type int = 0)RETURNS decimal(18, 4) ASBEGINdeclare @x decimal(18, 4)if (@bid_price_type = 0)beginselect @x = fieldNamefromtableNameendelsebeginselect @x = fieldName2fromtableName2endreturn @xEND' Calling Stored Proc codeCREATE PROCEDURE testASdeclare @x decimal(18, 4)set @x = dbo.get_bid_price(1)select @xGOthanks,Vic Y
View 2 Replies View RelatedSQL-DMO And C#: Retrieve The List Of User-Defined Functions
Apr 9, 2006http://www.csharphelp.com/archives2/archive342.htmlI am using the sample code from this link but I amunable to figure out how to retrieve the list ofthe User-Defined Functions. I am able to get thecount of the user defined functions correctly using:db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj _UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Cou ntbut I am unable to get to enumerate the function names.Then I tried to see if I can achieve what I want usingSQLObjectList but I was unsuccessful.Does someone know how I can do this using C#?Thank youThis is the full code I have:private void linkLabel5_LinkClicked(object sender,LinkLabelLinkClickedEventArgs e){this.Cursor = Cursors.WaitCursor;SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();srv.Connect(this.cboServers.SelectedItem.ToString( ), this.txtUser.Text,this.txtPassword.Text);for (int i = 0; i < srv.Databases.Count; i++){if (srv.Databases.Item(i + 1, "dbo").Name ==this.cboDatabase.SelectedItem.ToString()){SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo");this.lstObjects.Items.Clear();SQLDMO.SQLObjectList sqludf;sqludf =db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj _UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name);for (int j = 0; j < sqludf.Count; j++){//this.lstObjects.Items.Add(db.ListObjects(SQLDMO.SQ LDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Ite m(j + 1, "dbo").Name);}this.Cursor = Cursors.Default;return;}}this.Cursor = Cursors.Default;}
View 4 Replies View RelatedCan We Use User Defined Functions In Report Builder
Apr 11, 2006In a typical Database, there are tables, views, stored Procedures, user Defined Functions.
We could use tables and views in designing the Report Model.
We cannot use Stored Procedures, I tried that.
Please could somebody tell me if we could use User Defined Functions?
What if a view is already using a user defined function, is the function automatically includid in the Model Design?
Thank you,
Stored Procedures And User Defined Functions
Feb 4, 2006What are the pros and cons of each?
One advantage that I can see withh UDFs is that they are a bit a Views with parameters. You can perform joins on UDF columns (which you cannot do with a Stored Proc). You can do the same with Views but UDFs have the advantage that you restrict the number of rows with a parameterised WHERE (or HAVING) clause.
User-defined Functions - Best Practice Suggestions.
Jun 20, 2006I've got a couple guys on my team who seem to think user defined functions are a "risky" feature in SQL 2000. I've been using them for years without problems. Yeah, this one of those loaded prove me right or flame me questions ... whatever. I promise not to consider posts on MSDN forums authoritative answers to this practice question. I'm just trying to see where people stand. What I'm interested in knowing is:
Do you consider UDF's in SQL advanced, hard to figure out, hard to get right or risky?
Any cases where they've caused you more problems than other MS SQL features?
Random hints on best-practice for UDF's - where do they spead things up, slow things down?
Reasons that they are better than sliced bread / cheese spread?