need help on a 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 2000 Forums

Transact-SQL (2000)

need help on a function

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-28 : 22:04:32

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
(
-- Add the parameters for the function here
@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 the return variable here
DECLARE @Result varchar(max),@sql nvarchar(max),@ParmDefinition nvarchar(500),@re int
--prepare sql statement here
SELECT @sql = N'SELECT @ResultOut = LEFT('+@pTableName+'.firstnames,1) FROM '
+ @pTableName + ' WHERE ' + @pTableName + 'ID =@id'

--define input and output parameters here
SELECT @ParmDefinition = N'@id int,@ResultOut varchar(max)'
EXECUTE sp_executesql @sql,@ParmDefinition,@id = @pTableId,@ResultOut = @Result OUTPUT
-- Return the result of the function
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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-28 : 23:06:30

>> 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.

If "what i want" is executing dynamic sql from within a function, then no, you can not achieve what you want.

Rather than create a bunch of different tables all with firstnames column, why not put all the rows into a single table. You can add another column to that would represent tablename values.

Be One with the Optimizer
TG

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-01 : 00:06:53

TG
am just wondering
forgot to mention before ,am using sql server 2005, still no work around using function?
Thanks for ur time

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources