User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD

Calculate Average Balance
hi experts,

i created a report using cube and excel. The report has 2 columns i.e. Date on the left and Term on the right. It has a measure which is Rate.
The report requires a row which store the average balance.

average balance = total daily rate / no. of days in the month

This average balance need to be displayed underneath the Grand Total in excel.
How can i display this row in the excel?

Another problem i'm facing is if the layout is not so restricted, i have problem calculating the average balance. i'm not sure how the mdx statement should be written.

i tried this statement but it gives me the wrong value:
avg([Date Tx].[month].Members,[Measures].[Mobile Rate]).
i tried using CurrentMember but i have syntax error.

please help. Thanks

View Replies !

---

Local Variables In User Defined Functions
I'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 Replies !

---

How To Calculate Average Row Size Of A Record.
 I want to calculate average row size of a record. By based on this i want to add some more columns into an existing table. Here is my table structureCREATE TABLE patient_procedure(    proc_id int IDENTITY(1,1) CONSTRAINT proc_id_pri_key PRIMARY KEY,    patient_id int NULL,    surgeon_name varchar(40) NOT NULL,    proc_name varchar(20) ,    part_name varchar(30),    wth_contrast int ,    wthout_contrast int ,    wth_wthout_contrast int,    xray_part varchar(60),    arth_area varchar(30),    others varchar(30) ,    cpt varchar(20) ,    procedure_date smalldatetime NOT NULL,    mraloperrun varchar(20),CONSTRAINT patientid_foreign_key FOREIGN KEY(patient_id)    REFERENCES dbo.patient_information (Patient_id)) Now i got a requirement that i have to add two more procedures with different columns.The columns overall size is 195 bytes.I can place those two procedures as seperate tables. I dont want to do that becuase of front end requirements.Here the problem is when the user enters these two procedures information remaining fields will store the  null value. I know that when we store the null values into corresponding columns min of 1 byte will be occupied. Please suggest me that shall i include these columns into the above table. If i add these columns is performance will be decreased or not. Waiting for valuable suggestions. 

View Replies !

---

Calculate Average Analysis Servives
Hi this might be pretty simple to you guys out there but i am having issues doing it.

Please help!!!!!!!!!!

Calculate Average of a measure called DIST irrespective of any dimension the page field.

Ie. to say it should calculate the average for any doension i bring on the rwo field.

Looking forward to your help.


you can mail me at hem_k01@yahoo.com

View Replies !

---

Calculate Average Growth Rate
I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.

Now, I'm trying to figure out what the fastest growers are. Here's the table ddl

CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO


What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.

I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.

what am I missing here?

View Replies !

---

Calculate A Variable Average In SQL Server?
ok so i have this table:


Code:


Products_Sold
priceSold - money
itemsSold - int


An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81

to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)

But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?

in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100

but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200

and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300

but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.

I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye

View Replies !

---

How Do I Calculate Average Leadtime In Sqlserver...
Hi,
How do I Calculate Average Leadtime...
I have a Table named "iCalls_Calls" which has 2 Columns (start_Date and Closed_Date).I need to calculate average leadtime based on the columns from this table . I have a query and i need to add this ( calculate average leadtime) to this query.


Code:

SELECT B.USER_DIVISION,B.USER_DEPARTMENT,COUNT(*) FROM iCalls_Calls A INNER JOIN iCalls_Users B on A.REQUESTOR = B.USER_ID
GROUP BY USER_DIVISION,USER_DEPARTMENT



Can anyone send me the correct query to calculate the average time ?
Thanks..

View Replies !

---

How Do I Calculate The Average Variable Length For A Varchar?
im trying to learn how to calculate table size.
i understand some of it, but im stuck at calculating the varchars

Ex. i have 2 varchar columns
- varchar(50)
- varchar(100)

i'm suppose to find the average length for them?

i'm suppose to use that to add up to my ROW SIZE

and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array

please help me asap before 2morrow night.
Thanks!
i have a test

View Replies !

---

Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second.

What type of average are you seeing on your production machines? What is a normal average?

I have tried researching for suggested values on these two counters, but I haven't turned up any information.

Thanks,

MV

View Replies !

---

How To Get Average Value Of Time Difference?
My table has two datetime columns (TheatreArivalDate and TheatreDepartDate). Can I get an average value of the time differences of them? Thanks

View Replies !

---

Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View Replies !

---

User-defined Functions - Help!
Hi,

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

View Replies !

---

User-Defined Functions In DTS
Is 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 Replies !

---

User Defined Functions And OLE
Hi 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

View Replies !

---

User Defined Functions
hi 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

View Replies !

---

User Defined Functions
When 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?

View Replies !

---

User-Defined Functions
Is there any way to create procedures that will act like functions so that they can be invoked within other SQL statements? All the documentation I have been able to find suggests coding a subquery to replace what a function would do. This is inefficient, cumbersome and creates a maintenance nightmare.
Perhaps there are third party tools? Workarounds? HELP!!!

View Replies !

---

User Defined Functions
Hi
I built an SQL2K database using computed columns that call user defined functions. These do either calculation based on a some fields in the table or a lookup on another table (the fields are arguments to the function)

Now I learn we are moving back to SQL7.0 for support reasons.

What is the best way to implement the above scenario in SQL7
(which means, minus the functions)

thanks
Liju

View Replies !

---

User Defined Functions In SQL 7.0
Hi,

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.

View Replies !

---

User Defined Functions In SQL 2k
I expected that a parameter in the new udf's could be used dynamically. However, I am finding out that mabey they can't.
For instance:
Select * from udf_GetElection(2001,1234,1,'')
will return the election of person 1234 made in the year 2001 for plan1.

however if i only want one value from the table and want to include the function as a return field in a select, select errors out.

ie:
select top 10 ee.num,lname,fname,ssn, (SELECT optionid FROM UDF_GETELECTION(2001,7126,1,'')) as medoc
from employee ee

gives me:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ee'.

Yet a nested select which is what the udf really is works like this:

select top 10 num,lname,fname,ssn, (SELECT optionid FROM election where planyear =2001 and num = ee.num and planid =1 and electstatus = 'C') as medoc
from employee ee

returns valid data

Is there something I don't understand?

View Replies !

---

User Defined Functions
Hello,

Please help with UDF...

I have this UDF "fnEmpBirthDay", code is below:
CREATE function fnEmpBirthDay (@aFrom integer, @aTo integer)
returns table
as
Return
(
select * from vwEmpBirthDay
where Cast(substring(Convert(char(8),[emp birth date],112),5,4) as int) between @aFrom and @aTo
)

However, when i compare two queries like below:

1. select * from fnEmpBirthDay(101, 1031)

2. select * from vwEmpBirthDay
where Cast(substring(Convert(char(8),[emp birth date],112),5,4) as int) between 101 and 1031

They dont have same result. Why is it like that? is there something wrong with my query or something refresh...

However, When I edit the UDF fnEmpBirthDay and save it without any changes. Then two queries have the same results...

Please advise why is it happening like that?

Thanks in advance...

View Replies !

---

User Defined Functions
declare @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.

View Replies !

---

User Defined Functions
Hi 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

View Replies !

---

Help With User Defined Functions
Dear 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

View Replies !

---

User Defined Functions
Hi,
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?

View Replies !

---

Learning About User Defined Functions
I 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 Replies !

---

User Defined Functions In SQL Server 7.0
Hi 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 Replies !

---

User Defined Functions In SQL Server 7.0
hai,

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

View Replies !

---

DTS And User/System Defined Functions
Hi there,
I have written several User Defined Functions in a SQL2K db. I want to use these functions inside SQL statements in a DTS package (for instance, in a Data Driven Query). When I try to use the function, I get an error that the function is not recognized.
I have also converted a couple of these UDF's to System Functions (changing the owner to system_function_schema via sp_changeobjectowner, and making sure that the function name is preceded with 'fn_') This works fine in Query Analyzer, but gives the same message in the package. I have looked all over MSDN, and found nothing that explicitly says you cannot use functions in packages. I am more puzled by the fact that I cannot use it even when I make it a system function in the master database, which should allow me to call it from anywhere ...
What am I missing? Or is this just not possible...

View Replies !

---

User-defined Aggregate Functions
Hello,

I am maintaining a database that contains a list of people. The corresponding table has the following structure : (first name ; last name ; address). Given a last name and an address, there can be more than one first name.

Now, I would like to mail letters without redundancy : I wish to send only one letter for people living at the same place. Actually, I would like to create a table that associates to each couple (last name ; address) a unique row that consists of the concatenation of the first names of guys corresponding to that couple.

For example, if table is the following :

FIRST NAME | LAST NAME | ADDRESS
================================
Phil | Stevens | XXX
Cathy | Stevens | XXX
Kevin | Stevens | XXX

I would like to get the following table :

FIRST NAMES | LAST NAMES | ADDRESS
============================================
Phil, Cathy and Kevin | Stevens | XXX

Could anyone help me ?

Many thanks in advance,
TIJod.

View Replies !

---

Calling User-defined Functions In Another DB
I 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.

View Replies !

---

Documenting User-defined Functions
Okay, this does pretty much the same thing as the "Documenting stored procedures" post, except that it goes after user-defined functions. Enjoy!

/****************************************************************************************************/
DECLARE @Proc_ID INT
DECLARE @ProcFetch INT
DECLARE @SQLCount INT
DECLARE @SQLPiece VARCHAR (8000)
DECLARE @SQL VARCHAR (8000)
DECLARE @SQLFetch INT
DECLARE @ObjName VARCHAR (128)
DECLARE @SpacePos INT
DECLARE @DotPos INT
DECLARE @ProcName VARCHAR (128)
DECLARE @MaxSQL INT

SET NOCOUNT ON

CREATE TABLE #Func (
ID INT IDENTITY (1, 1),
Name VARCHAR (128)
)

CREATE TABLE #Param (
Proc_ID INT,
Name VARCHAR (128),
Type VARCHAR (32),
Length INT,
Required VARCHAR (3),
Direction VARCHAR (12),
Sort INT
)

CREATE TABLE #SQL (
Proc_ID INT,
SQL VARCHAR (8000),
SQLOrder TINYINT
)

CREATE TABLE #Children (
Proc_ID INT,
Name VARCHAR (128),
ObjType VARCHAR (128),
ObjSort TINYINT
)

INSERT #Func (Name)
SELECT name
FROM sysobjects
WHERE xtype IN ('AF', 'FN', 'IF', 'TF')
ORDER BY name

INSERT #Param (Proc_ID, Name, Type, Length, Required, Direction, Sort)
SELECT
p.ID,
CASE
WHEN ((c.colorder = 1) AND (c.number = 0) AND (LEFT (c.name, 1) <> '@')) THEN ''
WHEN ((c.colorder = 0) AND (c.number = 0)) THEN ''
ELSE c.name END AS ColName,
CASE
WHEN ((c.colorder = 1) AND (c.number = 0) AND (LEFT (c.name, 1) <> '@')) THEN '<Result set - [' + c.name + ']>'
ELSE t.name END AS Type,
CASE
WHEN ((c.colorder = 1) AND (c.number = 0) AND (LEFT (c.name, 1) <> '@')) THEN NULL
ELSE c.Length END AS Length,
CASE c.colorder WHEN 0 THEN '' ELSE 'Yes' END AS Required,
CASE c.colorder WHEN 0 THEN '' ELSE 'Input' END AS Direction,
c.colorder
FROM sysobjects o
INNER JOIN #Func p ON o.name = p.Name
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.xtype IN ('AF', 'FN', 'IF', 'TF')
ORDER BY
p.ID,
c.colorder

INSERT #SQL (Proc_ID, SQL, SQLOrder)
SELECT
p.ID AS Proc_ID,
m.text AS SQL,
m.colid AS SQLOrder
FROM sysobjects o
INNER JOIN #Func p ON o.name = p.Name
INNER JOIN syscomments m ON m.id = o.id
WHERE o.xtype IN ('AF', 'FN', 'IF', 'TF')
ORDER BY
p.ID,
m.colid

DECLARE curProc CURSOR FOR
SELECT
p.ID AS Proc_ID,
p.Name AS ProcName,
MAX (s.SQLOrder) AS MaxSQL
FROM #Func p
INNER JOIN #SQL s ON p.ID = s.Proc_ID
GROUP BY p.ID, p.Name
ORDER BY p.Name
OPEN curProc
FETCH NEXT FROM curProc INTO @Proc_ID, @ProcName, @MaxSQL
SET @ProcFetch = @@FETCH_STATUS
WHILE @ProcFetch = 0
BEGIN
SET @SQLCount = 0
SET @SQLPiece = ''

DECLARE curSQL CURSOR FOR
SELECT SQL
FROM #SQL
WHERE Proc_ID = @Proc_ID
ORDER BY SQLOrder
OPEN curSQL
FETCH NEXT FROM curSQL INTO @SQL
SET @SQLFetch = @@FETCH_STATUS
WHILE @SQLFetch = 0
BEGIN
SET @SQLCount = @SQLCount + 1

SET @DotPos = CHARINDEX ('--', @SQL)
WHILE @DotPos <> 0
BEGIN
SET @SpacePos = @DotPos
WHILE ASCII (SUBSTRING (@SQL, @SpacePos, 1)) > 31
BEGIN
SET @SpacePos = @SpacePos + 1
END

WHILE ASCII (SUBSTRING (@SQL, @SpacePos, 1)) < 32
BEGIN
SET @SpacePos = @SpacePos + 1
END

SET @SQL = LEFT (@SQL, @DotPos - 1) + LTRIM (SUBSTRING (@SQL, @SpacePos, 4000))

SET @DotPos = CHARINDEX ('--', @SQL)
END

SET @DotPos = CHARINDEX ('/*', @SQL)
WHILE @DotPos <> 0
BEGIN
SET @SpacePos = CHARINDEX ('*/', @SQL) + 2
SET @SQL = LEFT (@SQL, @DotPos - 1) + LTRIM (SUBSTRING (@SQL, @SpacePos, 8000))

SET @DotPos = CHARINDEX ('/*', @SQL)
END

SET @SQL = REPLACE (@SQL, CHAR (13), ' ')
SET @SQL = REPLACE (@SQL, CHAR (10), ' ')
SET @SQL = REPLACE (@SQL, CHAR (9), ' ')

SET @DotPos = CHARINDEX (' ', @SQL)
WHILE @DotPos <> 0
BEGIN
SET @SQL = REPLACE (@SQL, ' ', ' ')
SET @DotPos = CHARINDEX (' ', @SQL)
END

WHILE @SQL <> ''
BEGIN
SET @SpacePos = CHARINDEX (' ', @SQL)
SET @ObjName = ''

IF @SpacePos = 0
BEGIN
SET @SQLPiece = @SQLPiece + @SQL
SET @SQL = ''
IF @SQLCount < @MaxSQL
GOTO EndSQL
END
ELSE
BEGIN
SET @SQLPiece = @SQLPiece + RTRIM (LEFT (@SQL, @SpacePos))
SET @SQL = LTRIM (SUBSTRING (@SQL, @SpacePos, 8000))
END

SET @DotPos = CHARINDEX ('.', @SQLPiece)
IF @DotPos <> 0
BEGIN
SET @SpacePos = LEN (@SQLPiece)
WHILE SUBSTRING (@SQLPiece, @SpacePos, 1) <> '.'
BEGIN
SET @SpacePos = @SpacePos - 1
END

SET @SQLPiece = SUBSTRING (@SQLPiece, @SpacePos + 1, 8000)
END

SET @ObjName = @SQLPiece
SET @SQLPiece = ''

IF @ObjName <> ''
INSERT #Children (Proc_ID, Name, ObjType, ObjSort)
SELECT
@Proc_ID,
o.name,
CASE o.xtype
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'Check constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'Foreign key'
WHEN 'PK' THEN 'Primary key'
WHEN 'P' THEN 'SQL Stored procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored procedure'
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter procedure'
WHEN 'S' THEN 'System table'
WHEN 'SN' THEN 'Synonym'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TR' THEN 'SQL DML trigger '
WHEN 'IF' THEN 'SQL inline table-valued function'
WHEN 'TF' THEN 'SQL table-valued-function'
WHEN 'U' THEN 'Table'
WHEN 'UQ' THEN 'Unique constraint'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
WHEN 'IT' THEN 'Internal table'
END AS ObjType,
CASE
WHEN o.xtype IN ('S', 'U', 'IT') THEN 1
WHEN o.xtype IN ('UQ', 'PK', 'C', 'D', 'R') THEN 2
WHEN o.xtype = 'F' THEN 3
WHEN o.xtype = 'V' THEN 4
WHEN o.xtype IN ('X', 'P', 'PC') THEN 5
WHEN o.xtype IN ('FN', 'FS', 'FT', 'AF', 'IF', 'TF') THEN 6
WHEN o.xtype IN ('RF', 'SN', 'SQ', 'TA', 'TR') THEN 7
END AS ObjSort
FROM sysobjects o
WHERE o.name = @ObjName
AND o.name <> @ProcName
END
EndSQL:
FETCH NEXT FROM curSQL INTO @SQL
SET @SQLFetch = @@FETCH_STATUS
END
CLOSE curSQL
DEALLOCATE curSQL

FETCH NEXT FROM curProc INTO @Proc_ID, @ProcName, @MaxSQL
SET @ProcFetch = @@FETCH_STATUS
END
CLOSE curProc
DEALLOCATE curProc

SELECT DISTINCT
p.Name AS [Function],
'Dependent object' AS Type,
c.Name AS Name,
c.ObjType AS [Object type],
NULL AS Length,
'' AS Required,
'' AS Direction,
NULL AS ParamSort,
c.ObjSort,
2 AS Sort
FROM #Func p
INNER JOIN #Children c ON p.ID = c.Proc_ID

UNION ALL

SELECT
p.Name AS [Function],
CASE m.Name WHEN '' THEN '<Return value>' ELSE m.Type END AS Type,
m.Name AS ParamName,
m.Type AS ParamType,
m.Length AS Length,
m.Required,
m.Direction,
m.Sort AS ParamSort,
NULL AS ObjSort,
1 AS Sort
FROM #Func p
INNER JOIN #Param m ON p.ID = m.Proc_ID

ORDER BY
[Function],
Sort,
ParamSort,
ObjSort,
Type

DROP TABLE #Func
DROP TABLE #Param
DROP TABLE #Children
DROP TABLE #SQL

SET NOCOUNT OFF
/****************************************************************************************************/


I geek, therefore I am

View Replies !

---

User Defined Functions And EXEC
I'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

View Replies !

---

User Defined Functions To Stored Procedures
Hello 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

View Replies !

---

User Defined Functions && Default Parameters
Hi,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 Replies !

---

User-Defined String Functions Transact-SQL
Ladies and Gentlemen,I would like to offer you the following string functions Transact-SQLGETWORDCOUNT() Counts the words in a stringGETWORDNUM() Returns a specified word from a stringAT() Returns the beginning numeric position of the first occurrence of acharacter expression withinanother character expression, counting from the leftmost characterRAT() Returns the numeric position of the last (rightmost) occurrence of acharacter string withinanother character stringOCCURS() Returns the number of times a character expression occurs withinanother character expressionPADL() Returns a string from an expression, padded with spaces orcharacters to a specified length on the left sidePADR() Returns a string from an expression, padded with spaces orcharacters to a specified length on the right sidePADC() Returns a string from an expression, padded with spaces orcharacters to a specified length on the both sidesPROPER() Returns from a character expression a string capitalized asappropriate for proper namesRCHARINDEX() Is similar to a built-in function Transact-SQL charindex butthe search of which is on the rightARABTOROMAN() Returns the character Roman number equivalent of a specifiednumeric expressionROMANTOARAB() Returns the number equivalent of a specified character Romannumber expression ...For more information about string UDFs Transact-SQL please visit thehttp://www.universalthread.com/wcon...e~2,54,33,27115Please, download the filehttp://www.universalthread.com/wcon...treme~2,2,27115With the best regards

View Replies !

---

SQL-DMO And C#: Retrieve The List Of User-Defined Functions
http://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 Replies !

---

[OT] User-Defined String Functions Transact-SQL
Ladies 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.

View Replies !

---

User-Defined String Functions Transact-SQL
Ladies 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

View Replies !

---

[OT] User-Defined String Functions Transact-SQL
Ladies 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.

Plus, there are versions for SYBASE ASA, DB2.

More than 7000 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.

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 Functions - Best Practice Suggestions.
I'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?

 

View Replies !

---

Can We Use User Defined Functions In Report Builder
In 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,

 

View Replies !

---

Stored Procedures And User Defined Functions
What 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.

View Replies !

---

Stored Procedure Vs User Defined Functions
Hi 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

View Replies !

---

Passing Values In User Defined Functions
i 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.

View Replies !

---

Stored Procedure And User-Defined Functions
lokesh 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."

View Replies !

---

Computed Fields And User-defined Functions
Hi 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

View Replies !

---

User-Defined String Functions Transact-SQL
Ya 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

View Replies !

---

User-Defined String Functions SQLCLR
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)

Ladies and Gentlemen,

I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) with source code:

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.

Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.

More than 8000 people have already downloaded my functions. I hope you will find them useful as well.

For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527

Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527

The accuracy criteria of user-defined functions
GETWORDNUM() , GETWORDCOUNT(), GETALLWORDS(), GETALLWORDS2() working with strings:

1) Both character parameters are empty - the function returns nothing - either 0 strings, or 0.
2) The first parameter is not empty, the second is empty - the function returns the table from one string or 1. The first parameter is in this case the required word.
3) The function works correctly with strings starting and/or finishing with one or several delimiters.
4) The function works correctly on strings consisting only of delimiters - a result in this case is 0 strings, or 0 words.
5) The function works correctly irrespective of the character sets in the string or delimiters, including /, ,?, ^, %, -, ' etc., that is, any character that have special function.
No exceptions, if the parameters of an incorrect type are transmitted, there is a standard error message.
Above mentioned and other functions completely correspond to these criteria.

With the best regards,
Igor Nikiforov

View Replies !

---