trouble with syntax - 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 2000 Forums

Transact-SQL (2000)

trouble with syntax

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-27 : 21:41:08

How can I do the following below where the

if checked ='0' then

line is the value of checked in the first select??

CREATE PROCEDURE select_Mailread
(
@messageTo int,
@messageID int
)

AS SET NOCOUNT ON

SELECT messageID, subject, messageTo, messageFrom, prevMessage, message, checked, nameOnline, tblmessage.date FROM tblMessage JOIN tblUserDetails ON tblMessage.messageFrom = tbluserdetails.Userid WHERE messageID = @messageID AND MessageTo = @messageTo

if checked ='0' then
UPDATE [ratemybody].[dbo].[tblMessage] SET [Checked] = '1' WHERE ([MessageID] = @MessageID AND [MessageTo] = @MessageTo)
end
GO

thansk alot

mike

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-08-28 : 02:55:48

Changes made are in bold.

CREATE PROCEDURE select_Mailread
(
@messageTo int,
@messageID int
)

AS SET NOCOUNT ON
DECLARE @checked as char(1)

SELECT messageID, subject, messageTo, messageFrom, prevMessage, message, @checked = checked, nameOnline, tblmessage.date FROM tblMessage JOIN tblUserDetails ON tblMessage.messageFrom = tbluserdetails.Userid WHERE messageID = @messageID AND MessageTo = @messageTo

--if checked ='0' then
if @checked = '0'
begin
UPDATE [ratemybody].[dbo].[tblMessage] SET [Checked] = '1' WHERE ([MessageID] = @MessageID AND [MessageTo] = @MessageTo)
end
GO

Ramesh

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-28 : 22:37:00

thansk for the help, however I was hoping to bring back the records in the first SELECT statement as well as do the update but I get this error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Is it not possible to do what I am trying???

Thanks alot

Mike123

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-08-28 : 22:53:21

ratemybody ?????

HAHAHAHAHAHA, now I know why your site gets so much trafic

No, it isn't possible to do it. You need to do your update statement, then your select. If you need to select some values before your update then that needs to be a separate operation.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources