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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-12-13 : 08:11:28
|
| Ali writes "Hi all, I'm having real difficulties getting the following to work:A stored procedure accepts as an input an (int) SiteID, the stored procedure needs to check a table (table1) and return the latest row containing information from that site - ie the newest row where the siteid column = the input SiteID. the loaddate column is the date the row was added - hence the MAX(loaddate) constraint *should* select the latest record.The stored procedure then needs to set the value of IsOverride (bit) in that row as an output ie:create PROCEDURE sample_SP@SiteID int,@IsOverride bit OUTPUT,SET@IsOverride =(SELECT IsOverride FROM table1 WHERE MAX(loaddate) AND SiteID = @SiteID)RETURN @IsOverrideENDbut that doesn’t work! I think it doesn’t like the AND statement within the SELECT sub query, I get this error: “An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.”Oddly, the very simple:SELECT @IsOverride = IsOverride,FROM ControlSignalWHERE SiteID = @SiteIDSeems to work but can I trust this to always return the latest value??All help welcome - Cheers:Ali.(ms sql server 2005)" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-13 : 08:14:47
|
| [code]SELECT IsOverride FROM table1 t1 WHERE loaddate = (Select MAX(loaddate) from table1 t2 WHERE t2.SiteID = t1.SiteID) and t1.SiteID = @SiteID[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
|
|
|
Returning infomation from a stored procedure