scaler- valued 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 2005 Forums

Transact-SQL (2005)

scaler- valued function

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-26 : 09:26:57

i have given two functions - table valued and scaler valued

when select * from table_func('')
i m getting results..

but in select * from scalar_func('')
it gives error - invalid object name,
while i have both scripts open in same database but still from one func it gives results and from another it gives error..

so can anyone tell me plz. what should i do to see results from scalar func

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 09:30:49

You need to qualify the owner

select * from dbo.scalar_func('')

Madhivanan

Failing to plan is Planning to fail

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 09:33:06

This might help:

select * from dbo.Function ('')

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-26 : 09:36:30

Thanks for reply..i tried both ways but still getting same error..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 09:38:03

How does your scalar- function look like? Can you give a layout?

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-26 : 09:41:43

create function [dbo].[get_MailingId_] (@list_ varchar(60) = 'hin' ,
@Name_ varchar(60) = 'Tools_and_ideas_HBPMo06'
)
returns int as
begin
declare @mid int
if (select count(*)
from subsets_
where list_ = @list_
and name_ = replace(substring(@Name_,1,60),' ','_')
) > 1
set @mid = -999999
else set @mid =
(
select max(x.MessageID_) as Message_ID -- x.MessageId_ as [OutMail_.MessageID],
--x.*, y.*
from
( select *
from outmail_
) x,
(
select subsetid_, name_ as title_
from subsets_
where list_ = @list_
and name_ = replace(substring(@Name_,1,60),' ','_')
) y
where x.subsetid_ = y.subsetid_
-- and x.title_ = y.title_
)
set @mid = isnull(@mid,0)
return(isnull(@mid,0))
end

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 09:50:01

select * from [dbo].[get_MailingId_] ('hin' ,
'Tools_and_ideas_HBPMo06')

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-26 : 10:04:15

still getting same error...don't know why?

it says invalid object name

talleyrand
Starting Member

35 Posts

Posted - 2008-02-26 : 11:59:51

It's a scalar value function so you'll want to access it like a column, not a table.

SELECT [dbo].[get_MailingId_] ('hin', 'Tools_and_ideas_HBPMo06')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 12:03:40

or use a variable to take value

DECLARE @Var int

SET @Var = [dbo].[get_MailingId_] ('hin', 'Tools_and_ideas_HBPMo06')

SELECT @Var

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-26 : 12:22:43

thanks...now i got the results.

now i know the difference fo table-valued and scalar valued function.

thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources