calling UDF within select statement

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)

calling UDF within select statement

Author

Topic

__madmax__
Starting Member

31 Posts

Posted - 2004-10-07 : 16:01:31

hello all,

i think it can be done, but i'am not sure, and i cannot get it to work;

i have function;


CREATE FUNCTION countshit
( 
@uid int,
@aid int

)
RETURNS table
AS
RETURN (
        SELECT count(uid) as test
        FROM iamProfile
        WHERE uid = @uid and iamaid = @aid
)

when i call it from de query analyzer like this;

SELECT * FROM countshit('175', '122')

it return a number. What i want to do, is loop thru an tabel and with values from that table call the function;


select aid, (select * from countshit(175, answers.aid)) as test
from answers

but it give's me an error on the answers.aid part.

Is this the right way to call the function ?

any tips are welcome...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 16:06:15

[code]select aid, dbo.countshit(175, aid) as test
from answers
[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 16:10:15

why would you want to count shit??

Corey

__madmax__
Starting Member

31 Posts

Posted - 2004-10-07 : 16:13:26

hehe,

i excually trying to get some other 'shit' to gether but it got to complex for on query... so ithoughts, let sdo it like this. I'll be happy to dicuss the other one (the big query ofcourse)

@ehorn;

but he is saying

Invalid object name 'dbo.countshit'.

without dbo.
'countshit' is not a recognized function name.

when i call it like this;
select * from countshit(175, 102)

i just get a count for the two numbers.. do i need a different setup in my function ?

i have returns table now

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 16:15:53

[code]
CREATE FUNCTION countshit
(
@uid int,
@aid int

)
RETURNS int
AS
RETURN (
SELECT count(uid)
FROM iamProfile
WHERE uid = @uid and iamaid = @aid
)
[/code]

and then
[code]
select aid, dbo.countshit(175, aid) as test
from answers
[/code]

Corey

__madmax__
Starting Member

31 Posts

Posted - 2004-10-07 : 16:18:06

yes! that's what i thought!

but then he says
Line 9: Incorrect syntax near 'RETURN'.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 16:22:54

fine... try this:


CREATE FUNCTION dbo.countshit
( 
@uid int,
@aid int

)
RETURNS int
AS

Declare @blah int

Select @blah = (SELECT count(uid) FROM iamProfile WHERE uid = @uid and iamaid = @aid)

RETURN @blah

and then


select aid, dbo.countshit(175, aid) as test
from answers

Corey

__madmax__
Starting Member

31 Posts

Posted - 2004-10-07 : 16:28:07

i think my Query Analyzer is broken... really... all the day today... it;s not doing what i am teling him grmbl...

now he dares to tell me;
Incorrect syntax near the keyword 'Declare'.

(really had a bad programming day totday :P)

__madmax__
Starting Member

31 Posts

Posted - 2004-10-07 : 16:32:12

the real problem is this on;


--get XML for match FORM

CREATE PROCEDURE getXmlMatchFormInterview
(
@uid int
)

AS

select 	1 		as tag,
	null		as parent,
	vragen.vid	as [vraag!1!vid],
	vragen.vraag	as [vraag!1!questions!element],
	vragen.soort	as [vraag!1!soort],
	vragen.volg	as [vraag!1!volg],
	NULL		as [answer!2!aid],
	NULL		as [answer!2!volg],	
	NULL		as [answer!2!checked],
	NULL		as [answer!2!antwoord!element],
	NULL		as [answer!2!txt!element]


from vragen
	where gvid = 1

union all 
select
	2,
	1,
	vragen.vid,
	NULL,
	NULL,
	vragen.volg,
	answers.aid,
	answers.volg,
	iamProfile.uid,
	answers.antwoord,
	iamProfile.txt


FROM   answers FULL OUTER JOIN
                      vragen ON answers.vid = vragen.vid FULL OUTER JOIN
                      iamProfile ON vragen.vid = iamProfile.vid
WHERE     (vragen.gvId = 1) AND (vragen.vraag <> 'choose') and iamProfile.uid = @uid
UNION ALL
SELECT    2, 1, vragen.vid, vragen.vraag, vragen.soort, vragen.volg, answers.aid, answers.volg, iamProfile.uid, answers.antwoord, iamProfile.txt
FROM         answers LEFT OUTER JOIN
                      iamProfile ON answers.aid = iamProfile.iamAid FULL OUTER JOIN
                      vragen ON answers.vid = vragen.vid
WHERE     (vragen.gvId = 1) AND (vragen.vraag = 'choose') and iamProfile.uid = @uid

ORDER BY [vraag!1!volg], [vraag!1!vid], [answer!2!volg], Tag

FOR XML EXPLICIT
GO

in the bold part iam trying to get the uid for all the answers.. but it doesn;t work... tried everything today... all day...

if you would like do do something i can supply you some infromation about the tables..
here's the output; http://www.soapshow.nl/xml/get/?type=getXmlMatchFormInterview&uid=220
the radio part <question> need to be filled :(

ps for that part i was gonna use the function...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources