need help with a function
-
Hi friends
i need ur advise on this function. it return names from a specified table. i created this as a function bcoz i need to use it sql statements.
my script is
ALTER FUNCTION Initials
(
@pTableName varchar(100), -- pass table name. the table name must contain firstnames and surname fields
@pTableId int -- the particular record u r after
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @Result varchar(max),@sql nvarchar(max),@ParmDefinition nvarchar(500),@re int
SELECT @sql = N'SELECT @ResultOut = LEFT('+@pTableName+'.firstnames,1) FROM '
+ @pTableName + ' WHERE ' + @pTableName + 'ID =@id'
SELECT @ParmDefinition = N'@id int,@ResultOut varchar(max)'
EXECUTE sp_executesql @sql,@ParmDefinition,@id = @pTableId,@ResultOut = @Result OUTPUT
RETURN @Result
END
GO
it compiles fine but gives following error
"Only functions and extended stored procedures can be executed from within a function."
i know i can resolve this by creating it a stored procedure but i need it as function for different reporting reasons. is there any other way i can achieve what i want.
Thanks for ur help
Cheers
-
Sorry, no dynamic SQL in UDF.
But how many tables with personal details you have in your database?
If you did right design it must be only one.
If your design in not so good it may be 2 or 3.
So, create UDF for each of them and call one of these UDFs dependin on which initials you need to get. Not a big deal.
_____________
Code for TallyGenerator - sergiywe have tables like doctors, organisations , patients etc., which all contain personal details.am looking generic function (which we need to use in reporting ) to get initials of a patient/doctor etc., and want to use in a sql statement.looks like i need to create separate function for each of these tables.BTW forgot to say ,am using sql server 2005, is there a work around?
-
Doctor is a person playing role of doctor.
Patient is a person playing role of patient.
Nurse is a person playing role of nurse.
Etc.
Organisations actually don't have first or last names. Your function won't work for them.
What you really need is single table "Person" and PersonId in tables "Doctor", "Patient", etc.
Otherwise you need have 2 or more personal records for the same person if a doctor one day have become a patient (they got sick as well, agree?). And then change of address for John Smith "doctor" will not change address for John Smith "patient". And you don't have any way to join those 2 personal records and tell this is the same person.
_____________
Code for TallyGenerator -
Serqiy is completely right, as far as design is concerned, but if you can't change design anymore, there's still another solution:
Since you will not have an infinite number of tables storing personal information, do the following:
ALTER FUNCTION Initials
(@pTableName varchar(100),
@pTableId int)
RETURNS varchar(max)
AS
BEGIN
DECLARE @Result varchar(max)
IF @pTableName = 'tableDoctor'
Begin
SET @Result = (SELECT firstname FROM tableDoctor WHERE ID = @id)
RETURN @Result
END
IF @pTableName = 'tablePatient'
Begin
SET @Result = (SELECT firstname FROM tablePatient WHERE ID = @id)
RETURN @Result
End
End
... etc.
I think, the idea is clear and there's still the advantage, that you have encapsulated such retrieval in ONE function, which is the only place, you have to keepin mind, whenever your table definitions change.
Moreover, key fields in different tables might not always be the same. With the above design, the SQL-Statements you use to retrieve information can vary accordingly, e.g. "SELECT organisationname FROM tableOrganisation WHERE OrganisationID = @id) and so on.
_/_/_/ paramind _/_/_/ -
SORRY,
YOU CAN'T USE PROCEDURES,UPDATE,INSERT,DELETE STATEMENTS IN A FUNCTION.
REGARDS
AMIT
- paramindThats a good thought. i'll use that.Sergiy.I agree with you but we cant change database structure for different reasons at this point.i needed this in a functions as we need that functionality in sql statements.
- paramindThats a good thought. i'll use that.Sergiy.I agree with you but we cant change database structure for different reasons at this point.i needed this in a functions as we need that functionality in sql statements.
-
Actually, the right way is to do something like this:
ALTER FUNCTION dbo.Initials
(@FirstName varchar(200),
@MiddleName varchar(200),
@LastNamevarchar(200) )
......
and call this function in select from whatever table you need:
SELECT dbo.Initials (FirstName, NULL, LastName)
FROM dbo.Doctor
WHERE ....
This will work on tables, views, subqueries, etc.
Try to avoid table queries inside UDF. It's really dangerous for performance.
If you use UDF from paramind's post in query like this:
SELECT ...., dbo.Initials ('TableName', TableId)
FROM <TableName>
you actually create hidden cursor and slow down your query dramatically.
_____________
Code for TallyGenerator - Thanks Sergiy.Thats wonderful, i get the idea. Thanks for that.
-
In most cases people really need not what they ask for.
_____________
Code for TallyGenerator -
>SELECT ...., dbo.Initials ('TableName', TableId)<
That's what was asked for. If you prefer to replace it with a Sub-Select, this is simply different in terms of what you want - encapsulate or speed up - but anyway preferable to dynamic sql
Why not
CREATE VIEW viewInitials
AS
SELECT
FirstName AS firstName,
LastName AS lastName,
'Doctor' AS origin
FROM dbo.Doctor
UNION SELECT
FirstName,
LastName,
'Patient' AS origin
FROM dbo.Patient
UNION SELECT
OrganisationName,
'',
'Organisation' AS origin
FROM dbo.Organisation
_/_/_/ paramind _/_/_/ -
What's your local time?
_/_/_/ paramind _/_/_/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply