SQL Server 2014 :: User Defined Function - Using Table Name As Variable
Aug 9, 2014
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100)
RETURNS int AS
BEGIN
DECLARE @emp_count AS int
declare @declaration varchar(100)
[Code] ....
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1, Procedure count_rows, Line 10
Incorrect syntax near '@declaration'.
Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
View 9 Replies
ADVERTISEMENT
Oct 30, 2003
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 1 Replies
View Related
Jul 20, 2005
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies
View Related
May 20, 2008
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 7 Replies
View Related
Dec 24, 2007
Dear all
I wants to run sql server user defined function when linked two server.
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
Regards
Jerminxxx
View 7 Replies
View Related
May 25, 2006
I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000
Call to the following function returns an error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'srv2000'.
select [srv2000].db_test.dbo.F_TEST()
View 4 Replies
View Related
Apr 26, 2006
This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible?
I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF?
I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible.
Thanks in advance,
Sean
View 4 Replies
View Related
Jan 31, 2014
I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.
Function:
-----------------------------------
ALTER FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),
[Code].....
Not sure what I am missing in the above view why it doesn't split the string.
View 8 Replies
View Related
Feb 19, 2008
Is it possible to define your own function? If so could you give me an example.
Keep in mind that I said in SQL Server 2000. I want no CLR SQL Server 2005 solutions.
Cheers,
David
View 1 Replies
View Related
Sep 12, 2006
EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.
I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?
View 4 Replies
View Related
Oct 3, 2006
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 3 Replies
View Related
Feb 23, 2015
I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.
I have tried to create function, but the error I am facing is select statement can not return data.
CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
[code]....
View 9 Replies
View Related
Aug 1, 2005
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies
View Related
Dec 11, 2007
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 1 Replies
View Related
Mar 22, 2006
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
View 1 Replies
View Related
Mar 18, 2008
Hello
Is there a way that I can create a user defined variable in my database? I have a fairly large chunk of xml that I would like to store in the database that can be updated by clients ( in rare cases ). I could use other mechinisms to allow access to it, but all other application related information is stored nicely in this one place, I hate to vary for this single piece.
Thank you for your time and thoughts.
Patrick
View 14 Replies
View Related
Jan 10, 2007
I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE FUNCTION GetStartDate (@startdate datetime, @days int) RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules. If 4 business days means you count starting tomorrow, set counter to 0. If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date. we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
--If the day is between 2 and 6 (meaning it's a week --day and the day is not in the holiday table, we'll --increment the counter IF (DATEPART(dw, @enddate) between 2 and 6) AND (@enddate not in ( select HolidayDate from tFederalHoliday where [HolidayYear] = datepart(yyyy,@enddate) ) ) BEGIN set @counter = @counter + 1 END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------
View 1 Replies
View Related
Aug 17, 2005
Hi everyone,
I am tring to pass acomma delimited string to a function and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this. How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks
View 1 Replies
View Related
Dec 14, 1999
How can I create a user defined function in SQL and call it inline from a SQL statement? I need the ability to execute a statement such as:
select myFunc(x,y) from table
I need myFunc(x,y) to return a value, not a recordset and use that value in the select statement. Any and all ideas would be appreciated.
View 1 Replies
View Related
Aug 2, 1999
Dear friends,
I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.
Thanks in advance!
Kai
View 2 Replies
View Related
Jun 23, 2004
I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?
CREATE FUNCTION fnShipments ()
RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
AS
BEGIN
INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth =
(SELECT MIN(EffectiveMonth)
FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
(SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth Is Null )
) AS Territory
FROM Shipments
ORDER BY Shipments.AccountID, Shipments.DateInvoice;
RETURN
END
View 1 Replies
View Related
Nov 10, 2004
I need a UDF that will do the following:
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF
I would also like now to be my getdate(), but it seems like you can't use it in UDF
View 1 Replies
View Related
Mar 20, 2004
I create the following UDF just for learning purposes and I cannot seem to get it to return a value.
Stored Procedure
REATE PROCEDURE usp_GetXSection
@fWidth float,
@fHeight float,
@fResult float OUTPUT
AS
BEGIN
set @fResult = [dbo].[XSECTION](@fWidth, @fHeight)
END
GO
Ane the following UDF
CREATE FUNCTION XSECTION
(@Width float, @Height float)
RETURNS float
AS
BEGIN
RETURN (@Width * @Height)
END
I call it using ADO in a c++ application, but all returns empty
Any ideas? C++ code to follow.
Mike B
void CFormTemplateSetup::OnCalculate()
{
_CommandPtr pCmd(__uuidof(Command));
_ParameterPtr pWidth(__uuidof(Parameter));
_ParameterPtr pHeight(__uuidof(Parameter));
_ParameterPtr pResult(__uuidof(Parameter));
try
{
pCmd->ActiveConnection = GetDocument()->GetConnection();
pCmd->CommandText = _T("usp_GetXSection");
pCmd->CommandType = adCmdStoredProc;
_variant_t vWidth, vHeight;
vWidth.vt = VT_R4;
vHeight.vt = VT_R4;
vWidth.fltVal = 10;
vHeight.fltVal = 10;
pWidth = pCmd->CreateParameter(_T("@fWidth"), adInteger, adParamInput, sizeof(int), vWidth);
pHeight = pCmd->CreateParameter(_T("@fHeight"), adInteger, adParamInput, sizeof(int), vHeight);
pResult = pCmd->CreateParameter(_T("@fResult"), adDouble, adParamOutput, sizeof(float), vtMissing);
pCmd->Parameters->Append(pWidth);
pCmd->Parameters->Append(pHeight);
pCmd->Parameters->Append(pResult);
_variant_t vResult = pResult->Value;
float fArea = pResult->Value.fltVal;
CString csArea;
csArea.Format("Area = %d", fArea);
::AfxMessageBox(csArea);
}
catch(_com_error& e)
{
::AfxMessageBox(e.Description());
}
}
View 1 Replies
View Related
Apr 17, 2007
Hi,
Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.
View 13 Replies
View Related
Apr 6, 2006
I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.
To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.
The access function is as follows:
Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long
If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If
End Function
The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.
I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.
Any direction would be greatly appreciated!
View 6 Replies
View Related
Aug 28, 2007
hai,
the problem is - I have created a userdefined function using SQL 2000
create function getfulldate (@date varchar(10))returns datetimeasbegindeclare @getfulldate datetime set @getfulldate = dateadd (mi,55,@date)
return @getfulldateend
and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp
This works fine and what I need was, I need to invoke the user-defined function like
select *, getfulldate('2006-05-03') from emp that is, without using "dbo".
If I call in that manner, it gives error as - 'getfulldate' is not a recognized function name.
So, here what is the purpose of dbo and can I call in my desired manner as mentioned above.
anyone guide me, thanks!
View 3 Replies
View Related
Aug 4, 2005
Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
View 2 Replies
View Related
Feb 28, 2006
I have a sproc that passes an ID to a user defined function. I just realized that the udf can only return scalar type value. I need it to return a varchar(10). How can i do this with similiar functionality. Any ideas?
View 1 Replies
View Related
Jun 9, 2004
Hi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.
View 11 Replies
View Related
Aug 13, 2004
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @currYYMM VARCHAR(20)
SET @currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
...............
.....................
View 2 Replies
View Related
Nov 1, 2006
Hi,
While i execute the followgin code. it gives error. Please resolve it as while calling the function i want to use getdate() or any other dynamic variable or any select such as
select title from SalesByStore( select admission_Dte from addmission )
It gives error.
--------------------------------------------------------------------------
use pubs
go
createFUNCTION SalesByStore (@storeid datetime)
RETURNS TABLE
AS
RETURN (SELECT top 5 title, qty
FROM sales s, titles t
)
go
select title from SalesByStore(getdate())
View 1 Replies
View Related
Mar 3, 2004
Hi,
I posted a question sometime back about any one has written a function which converts a given date to a a new timezone.
As it turns out I have actually written the function and it is functionally sound.
the issue I am not facing is the performace.
The Algorithm is pretty simple.
step 1
I have create a table called TZ1 which has the TImezoneCODE (PST,EST..), daylightsavingsstart (DATETIME on which the daylight savings start), daylightsavingsend ( end of DLS) and offset (EG -8 for PST, -5 for EST etc)
step 2
I check the timezone and pick out corresponding start and enddate for daylight savings.
Step 3
Depending on where the date lies I create a new date by adding offset to the original datetime (+1) for the new date and then return this new datetime.
As simple as this looks the response time is 16 times than that of a select statement without the function.
Do you see something that I can tune to get a better response time.
Heres my code
================================================== ================================================== ================================================== ================================================== ==================================
CREATE FUNCTION TC3
-- drop function tc3
(@dt datetime, @Src varchar(10), @Dest varchar(10))
Returns datetime
begin
declare @v_src varchar(10)
Declare @v_dest varchar(10)
declare @V_dt datetime
declare @v_newdt datetime
declare @v_year int
declare @v_offset float
declare @v_sd datetime
declare @v_ed datetime
-- select * into TZ1 from TZ1
-- create table #tz1 (fn int)
select @v_year = datepart (year, @dt)
if (@v_year between 2000 and 2004)
begin
-- -------------------
-- PST BEgin
-- -------------------
if (@dest = 'PST')
begin
-- select * from TZ1
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
-- AND @DT between daylightsavingsstart and daylightsavingsend
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=pst end
-- -------------------
-- PST END
-- -------------------
-- -------------------
-- EST BEGIN
-- -------------------
ELSE
if (@dest = 'EST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Est end
-- -------------------
-- EST END
-- -------------------
-- -------------------
-- HST BEgin
-- select * from timezones
-- -------------------
else
if (@dest = 'HST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int) , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Ast end
-- -------------------
-- HST END
-- -------------------
-- -------------------
-- MST BEgin
-- select * from timezones
-- -------------------
else
if (@dest = 'MST')
begin
select @V_year = datepart (year, @dt)
select @v_offset = offset,
@v_sd = daylightsavingsstart,
@v_ed = daylightsavingsend
from TZ1
where
@V_YEAR = YEAR
and timezonecode = @dest
if (@dt between @v_sd and @v_ed )
begin
-- ( select @v_newdt = dateadd (hh, -9, @dt) )
select @v_newdt = dateadd (hh, CAST( @v_offset as int)-1 , @dt)
end
else
begin
-- ( select @v_newdt = dateadd (hh, -8, @dt) )
select @v_newdt = dateadd (hh, @v_offset, @dt)
end -- if dt between daylight savings
return @v_newdt
end -- @dest=Mst end
-- -------------------
-- MST END
-- -------------------
else
return '19990909'
end -- year end
return @v_newdt
end
================================================== ================================================== ================================================== =============================================
View 1 Replies
View Related
Jul 12, 2002
Dave writes "Anybody knows of a good resource for "generic" SQL Server User Defined Functions ?"
View 1 Replies
View Related
