SQL Stored Procedure Question

I'm having an issue trying to set variable in SQL in a stored procedure. I'm checking to see if a record is set to active, and the idea is to set it to inactive or vice versa.

Admittedly I'm not the best with SQL and have tried CASE statements along wi开发者_高级运维th my example, but nothings working...

Could someone show me what I'm missing???

Thanks!!!

 ALTER Procedure [dbo].[spAlterStatus]
 @CID as int,
 @Active varchar(10)
 AS

select @Active = Active  from Course where
CID = @CID;

 if @Active='0' set @Active = '1';
 if @Active='1' set @Active = '0';


UPDATE Course
SET Active = @Active WHERE  CourseID = @CourseID

I'm not sure why everyone is selecting AND updating - you can do this all in one operation:

ALTER Procedure [dbo].[spAlterStatus] 
    @CID as int
AS 
    UPDATE Course 
    SET Active = CASE WHEN Active = '0' THEN '1' ELSE '0' END
    WHERE CourseID = @CID

This is a problem:

if @Active='0' set @Active = '1';
if @Active='1' set @Active = '0';

These two statements execute one after another: so @Active always ends up being '0'.

Try something like this:

if @Active='0' set @Active = '1';
else set @Active = '0';

This should work, maybe you have to rename the CID / CourseID. I think it should be the same field.

ALTER Procedure [dbo].[spAlterStatus]
 @CID as int,
 @Active varchar(10)
 AS

select @Active = CASE Active WHEN '0' THEN '1' ELSE '0' END AS Act from Course where
CID = @CID;

UPDATE Course
SET Active = @Active WHERE  CID = @CID

I'm not 100% sure what you want to do but does this work better:

ALTER Procedure [dbo].[spAlterStatus] 
    (
     @CID int,
     @Active varchar(10)
     )
AS

SET @Active = (select Active  from Course where CID = @CID)

if @Active= '0' 
     BEGIN
     set @Active = '1';
     END
ELSE IF @Active= '1'
     BEGIN
     set @Active = '0';
     END

UPDATE Course
SET Active = @Active 
WHERE  CourseID = @CID