Dear all,After reading the Book Online, I am still confued by when to use store proc or user-defined function. The most obvious different for me is that 1. UDF can return a table eg select * from dbo.UDF(a, b , c) In real word application, what factor should i consider?Also, any debug tools in sql server ?Ad_dee
Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help.
Code:
CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2)) RETURNS VARCHAR(2) AS BEGIN IF (LEN(@sValue) < 2) SET @sValue = '0' + @sValue
Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process.
This is code in Form.asp
'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- >
If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient --------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
I seem to be getting tasks that I am not familiar with these days. I am a guy that has coded it all in the asp page or in the code behind in .NET. This problem is outlined below and I need a help / advice on doing this. I had the flow of the 3 parts to it expanded below. A call is made to a Stored Procedure, The SP then calls a user defined function that runs SQL, this returns a 1 or 0 to the SP which then returns the value back to the call on the asp page. This is a lot I know but it is the way the lead guy wants it done. Any help so I can keep most of the hair I have left is appreciated :-)
Short list of process flow:
1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER
2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient
3. ab_HasAccessToClient runs SQL command on db and sends return bit back to rx_sp_HasAccessToClient
4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp page
5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.
<FLOW WITH CODE AND FUNCTIONS :>
This is not the correct syntax but is showing what I understand sort of how this is to be done so far.
This panel loads up the Vendors and id's when the user clicks on the link "view detailed list of vendors associated with this client". This is the beginning of the process.
This is code in Form.asp
'PANEL ONE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX---- >
If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check Postback Type
'We need to load up vendors associated with the current client.
'--------- CHECK ACCESS HERE via function ab_HasAccessToClient --------
'If the call returns 1, then the employee has access.
'Otherwise, just write out "Access to this client is denied."
'CALL SP - Not sure what parameters need to go with it or its syntax
I am trying to add a simple case statement to a stored procedure oruser defined function. However when I try and save thefunction/procedure I get 2 syntax errors. Running the query in queryanalyser works fine and a result is given with no syntax errors. Ibelieve its something to do with the spaces in the field names. Not mychoice as its an existing system I have to work around. Any helpgreatly appreciatedSQL QueryDECLARE @pfid VARCHAR(100)SET @pfid = '000101'SELECTCaseWHEN GetDate()BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfidUser Defined Function (Errors Line 11 & 15)CREATE FUNCTION dbo.get_Margin(@pfid VARCHAR(100), @dtNow DATETIME)RETURNS DECIMAL ASBEGINDECLARE @Return as DECIMALSET @Return = (SELECTCaseWHEN @dtNowBETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfid)RETURN @ReturnEND
I have many stored procs in my database and I can call them just bytheir name uspMyProc with success always. However, I just created auser function ufnMyFunction as the same user that I created my procsbut when I call ufnMyFunction it fails unless I preface it with dbo. .How come the stored proc does not require this but the stored functiondoes?TFD
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
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 ---------------------------------------------------------------------------------------------
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
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.
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
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
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.
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.
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!
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
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?
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.
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 ............... .....................
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
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.
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 -- -------------------
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!
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>
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.