asked on

How to created User Functions in T-SQL to include in main select statement, if possible?

Hi,

I need to use a subselect in a select statement, ie

select ID, (select name from.... where ID1 = @ID1 and ID2 = @ID2) as Name from .....

Now this subselect has got more complicated so rather recopy this sql all over the place I was wondering whether it was possible to just create a User Function/Stored Function/Stored Procedure instead and if possible how to do it. So instead I would hope to:

select ID, (GetName(@ID1,@ID2) as Name from ...

So my question is really about is GetName possible and how to do it?

Thanks,

Sam..

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SamJolly

ASKER

Thanks for this. COuld I trouble you for some more detail. My code iverview so far is :

create function dbo.GetDefinitionName(@FkDefinitionID UNIQUEIDENTIFIER, @ClientID UNIQUEIDENTIFIER) returns varchar(50)
as return
(
SELECT ISNULL
(
(
SELECT Name
FROM .... etc
),
NULL
)
)

I am getting "Incorrect syntax near 'RETURN'." Something stupid I am sure.

Thanks again.

Sam

SamJolly

ASKER

Nearest I have got and working I think is:

create function dbo.GetDefinitionName(@FkDefinitionID UNIQUEIDENTIFIER, @ClientID UNIQUEIDENTIFIER) returns varchar(50)
as 
BEGIN
DECLARE @DefinitionName varchar(50)

SET @DefinitionName =
(
SELECT ISNULL
(
( 
SELECT Name 
FROM ....etc...),
NULL
) 
) 

RETURN @DefinitionName

END

Open in new window

Is this what you mean?

Guy Hengel [angelIII / a3]

yes, that is fine. what I posted was not double-checked, and indeed would be a table-valued function, you want a single value to be returned.

SamJolly

ASKER

thanks. Much appreciated.