SQL Stored Procedure Question

Question

Hello All! 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 with 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

Answer 1

From first glance one problem is that you initially declare a variable called @CID, but later you use the undeclared @CourseID.

If that doesn't solve your problem, please give details of the actual error you are getting.

Answer 2

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

Answer 3

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

Answer 4

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';

Answer 5

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