function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

function

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-03-15 : 07:31:48

Hi,
Issue is, I want to have this function inside a select query i.e.

select
field1, field2, field3, [dbo].[fnTest] (field1)
from tblMain

The error I get is:
The multi-part identifier "dbo.fnTest" could not be bound.

Any thoughts please?

Do you see what I am doing wrong please?

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>

--select [dbo].[fnTest] 'bruno'
-- =============================================
create FUNCTION dbo.fnTest
(
@Code char(4)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Names varchar(1000)

set @Names = (select [Name] from tblMain where code = @Code)

RETURN @Names
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-15 : 08:37:49

CREATE FUNCTION must be the only statement in the batch.
If you have a SELECT statement before, as you do above, you have to put a GO between the two statements.

Also, make the function inline for better performance


CREATE FUNCTION	dbo.fnTest
(
	@Code CHAR(4)
)
RETURNS VARCHAR(1000)
AS
BEGIN
	RETURN	(
			SELECT	[Name]
			FROM	dbo.tblMain
			WHERE	Code = @Code
		)
END

N 56°04'39.26"
E 12°55'05.63"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-03-15 : 11:35:50

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:40:47

I hope you dont have multiple records with same code value in tblMain. If exists, it will return only one of the Name value through the variable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources