Solved

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

Posted on 2011-03-04
5
296 Views
Last Modified: 2012-05-11
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..
0
Comment
Question by:SamJolly
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35035023
yes, that is perfectly possible

create function dbo.GetName(@id1 int, @id2 int) returns varchar(100)
as return ( select name from.... where ID1 = @ID1 and ID2 = @ID2 ) 

Open in new window


and use it:
select ID, dbo.GetName(ID1, ID2) as Name 
from  ..... 

Open in new window


note: the dbo (schema) prefix owner is important

0
 

Author Comment

by:SamJolly
ID: 35035156
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
0
 

Author Comment

by:SamJolly
ID: 35035235
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?

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35035347
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.
0
 

Author Closing Comment

by:SamJolly
ID: 35035465
thanks. Much appreciated.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now