help with sp - 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)

help with sp

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-02-02 : 23:00:18

I'm attempting to use an OUTPUT parameter for the first time. I'm getting an error here, probably something stupid but I can't figure out why. Hopefully somebody can lend a hand, and maybe critiqe the SP. I basically just check to see if the insert should proceed or abort depending on whether a row is found in the first check, I then pass this value to my ASP.NET code. Is this the best way to do it ?

I get this error:

Server: Msg 156, Level 15, State 1, Procedure insert_tblInstantMessage2, Line 33
Incorrect syntax near the keyword 'ELSE'.

Thanks alot

Mike123

alter PROCEDURE [insert_tblInstantMessage2]
(
@MessageToID [int],
@MessageFromID [int],
@Message [varchar](500),
@Date [smalldatetime],
@blocked [tinyint] OUTPUT
)
AS SET NOCOUNT ON

SELECT userID from tblBlockList WHERE userID = @messageToID and BlockUserID = @MessageFromID

IF @@Rowcount = 0

INSERT INTO [tblInstantMessage]
(
[MessageToID],
[MessageFromID],
[Message],
[Date],
[Checked]
)
VALUES
(
@MessageToID,
@MessageFromID,
@Message,
@Date,
'0'
)
SELECT @blocked = 1

ELSE

SELECT @blocked = 0

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-03 : 00:13:24

The if statement only coveres the next statment so is not in force for the else. Put in a begin...end block to include multiple statements.
Do you want to return the user id?

alter PROCEDURE [insert_tblInstantMessage2]
(
@MessageToID [int],
@MessageFromID [int],
@Message [varchar](500),
@Date [smalldatetime],
@blocked [tinyint] OUTPUT
)
AS SET NOCOUNT ON

SELECT userID from tblBlockList WHERE userID = @messageToID and BlockUserID = @MessageFromID

IF @@Rowcount = 0
begin
INSERT INTO [tblInstantMessage]
(
[MessageToID],
[MessageFromID],
[Message],
[Date],
[Checked]
)
VALUES
(
@MessageToID,
@MessageFromID,
@Message,
@Date,
'0'
)
SELECT @blocked = 1
end
ELSE
begin
SELECT @blocked = 0
end

GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-02-03 : 01:26:32

thanks alot, worked perfectly!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources