help with query, output value - 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 2005 Forums

Transact-SQL (2005)

help with query, output value

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-12-12 : 11:48:38

Hey,

Been a long day and I can't figure out what on earth I'm doing wrong here. I think should be something simple ?

Any help greatly appreciated !!

thanks :)
mike123

CREATE PROCEDURE [dbo].[update_linkSwap_toggleLinkStatus]
(
@partnerID int,
@linkID int,
@updated_displayStatus tinyint OUTPUT
)
AS SET NOCOUNT ON

DECLARE @displayStatus tinyint
SELECT @displayStatus = (SELECT displayStatus FROM tblLinkSwap_OutBoundLinks WHERE partnerID = @partnerID AND linkID = @linkID)

IF (@displayStatus = 0)
UPDATE tblLinkSwap_OutBoundLinks SET displayStatus = 1 WHERE partnerID = @partnerID and linkID = @linkID
SELECT @updated_displayStatus = 1

ELSE
UPDATE tblLinkSwap_OutBoundLinks SET displayStatus = 0 WHERE partnerID = @partnerID and linkID = @linkID

SELECT @updated_displayStatus = 0

GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-12-12 : 12:39:00

quote:
Originally posted by mike123

Hey,

Been a long day and I can't figure out what on earth I'm doing wrong here. I think should be something simple ?

Any help greatly appreciated !!

thanks :)
mike123

CREATE PROCEDURE [dbo].[update_linkSwap_toggleLinkStatus]
(
@partnerID int,
@linkID int,
@updated_displayStatus tinyint OUTPUT
)
AS SET NOCOUNT ON

DECLARE @displayStatus tinyint
SELECT @displayStatus = (SELECT displayStatus FROM tblLinkSwap_OutBoundLinks WHERE partnerID = @partnerID AND linkID = @linkID)

IF (@displayStatus = 0)
UPDATE tblLinkSwap_OutBoundLinks SET displayStatus = 1 WHERE partnerID = @partnerID and linkID = @linkID
SELECT @updated_displayStatus = 1

ELSE
UPDATE tblLinkSwap_OutBoundLinks SET displayStatus = 0 WHERE partnerID = @partnerID and linkID = @linkID

SELECT @updated_displayStatus = 0

GO

I dont know what the question is but will take a guess.
You need BEGIN and END for your IF ELSE since you have more than 1 statement.

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-13 : 00:00:44

CREATE PROCEDURE [dbo].[update_linkSwap_toggleLinkStatus]
(
@partnerID int,
@linkID int,
@updated_displayStatus tinyint OUTPUT
)
AS SET NOCOUNT ON

DECLARE @displayStatus tinyint
SELECT @displayStatus = (SELECT displayStatus FROM tblLinkSwap_OutBoundLinks WHERE partnerID = @partnerID AND linkID = @linkID)

IF (@displayStatus IS NULL)
BEGIN
UPDATE tblLinkSwap_OutBoundLinks SET displayStatus = 1 WHERE partnerID = @partnerID and linkID = @linkID
SELECT @updated_displayStatus = 1
END

ELSE
BEGIN
UPDATE tblLinkSwap_OutBoundLinks SET displayStatus = 0 WHERE partnerID = @partnerID and linkID = @linkID
SELECT @updated_displayStatus = 0
END

if displaystatus value having any record with 0 then if statement will execute so that i changed IF (@displayStatus IS NULL)
Just try it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 00:28:14

isnt this ebnough?


CREATE PROCEDURE [dbo].[update_linkSwap_toggleLinkStatus] 
(
@partnerID	 int,
@linkID	 int,
@updated_displayStatus tinyint OUTPUT
)
AS SET NOCOUNT ON

UPDATE tblLinkSwap_OutBoundLinks 
SET @updated_displayStatus =displayStatus = CASE WHEN displayStatus =0 THEN  1 ELSE 0 END 
WHERE partnerID = @partnerID 
and linkID = @linkID

GO

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-12-14 : 11:30:49

Thx guys , resolved now! .. was getting some silly error previously

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources