Referencing Table Rows in Functions

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)

Referencing Table Rows in Functions

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 17:50:44

Let's say every row of a table has three fields: Account, FirstTime, LastTime. I have a view that shows the Account as one field, and I want a 0 or 1 value to be the next field, based on the account field for that row. I have a function for it.

Prototype code here:


create function StillActive(Account int, @Month int)
returns int
as
begin
declare @x int
if
datediff(mm,min(dbo.x.FirstTime),max(dbo.x.LastTime)) > @Month when dbo.x.Account = Account,
select @x = 1
else
select @x = 0
return @x
end

Msg 156, Level 15, State 1, Procedure StillActive, Line 9
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Procedure StillActive, Line 11
Incorrect syntax near the keyword 'else'.

What am I missing?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-19 : 17:57:22

'You can also use a case statment

create function StillActive(@Account int, @Month int)
returns int
as
begin
declare @x int
if
datediff(mm,min(dbo.x.FirstTime),max(dbo.x.LastTime)) > @Month
Begin
If (dbo.x.Account = Account)
Begin
select @x = 1
End
else
Begin
select @x = 0
end
End
return @x
End

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 17:59:34

I have no idea what you are trying to do in the function. You have declared @x as int and using it as "dbo.x.FirstTime" which is syntactically incorrect.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-19 : 18:01:08

I should've probably made sure the function made sense prior to correcting the syntax.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-19 : 18:01:14

There are many things wrong with that code, so let's just start with the most basic: You don't have any reference to a table.

CODO ERGO SUM

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:12:27

quote:
Originally posted by Michael Valentine Jones

There are many things wrong with that code, so let's just start with the most basic: You don't have any reference to a table.

I thought that just as if you were to make a view

 SELECT Account, 1/Orders, SUM(GoodOrders+BadOrders

from a table with columns

 Account, Orders, GoodOrders, BadOrders, BobRating

And the query will compute by extracting values row-by-row, that

 SELECT Account, 1/Orders, fnFunction(5)

would work, as long as the fields in the function were the same fields in the row I was trying to insert the function into.
Obviously, that's not true...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:16:19

Dupe http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86615

Peter Larsson
Helsingborg, Sweden

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:16:49

quote:
Originally posted by dinakar

I have no idea what you are trying to do in the function. You have declared @x as int and using it as "dbo.x.FirstTime" which is syntactically incorrect.

The two 'x'es are not supposed to be related. In the actual code, they are not. I just did not want to share the actual column names, and I wanted a quick and dirty variable to test my code on, so I picked one that was one letter.

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 18:29:07

quote:
Originally posted by Peso

Dupe http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86615

I was hoping to try both approaches, but OK then...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 18:41:07

First we have to understand what you are trying to accomplish.
We have repeatedly asked you to post proper sample data and expected output to no vail!

If you do not know how to post these things, have a look at this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81373

It took about 3-4 minutes to solve, because the OP did a good job and the problem was easily understood and the answer was already posted for comparison.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources