Dynamic query in a function

Question

Post reply

Dynamic query in a function

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Have you tried creating this function in the other database, and just calling it?

i.e.:

alter function db.dbo.fn1

returns int

as

begin

declare @id int

set @id=3

select col1 from dbo.test1 where col1=2

return @id

end

then call it by:

select db.dbo.fn1

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

WayneS (11/23/2010)
Have you tried creating this function in the other database, and just calling it?
i.e.:
alter function db.dbo.fn1
returns int
as
begin
declare @id int
set @id=3
select col1 from dbo.test1 where col1=2
return @id
end
then call it by:
select db.dbo.fn1

Wayne, i tried. But in 2008 , you get:

Msg 166, Level 15, State 1, Line 1

'CREATE/ALTER FUNCTION' does not allow specifying the database name as a prefix to the object name.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Subbu S Old Hand Points: 359 More actions · Answer 3

Hi ,

I too faced tat problem and tis s hw , i solved out.

Open Row_Fetch

Fetch Next From Row_Fetch into @Type, @DispName, @TName, @oc

WHILE @@Fetch_Status=0

BEGIN

SELECT * INTO #insertedval FROM INSERTED

create table #temp (value varchar(100))

declare @heat varchar(100)

select @sql = 'insert into #temp select ' +@OC+ ' from #insertedval'

exec (@sql)

select @new_val = (select top 1 * from #temp)

drop table #temp