Author |
Topic |
rsquared
Starting Member
5 Posts |
Posted - 2012-10-18 : 12:00:19
|
I need to write a stored procedure that will change or update a boolean field to true if it is false, and false if it is true. In order to update the correct record, I need to use the record number as a parameter.Thanks ! |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
rsquared
Starting Member
5 Posts |
Posted - 2012-10-18 : 14:11:32
|
quote: Originally posted by Lamprey What have you tried so far? Can you post your DDL & DML (expected output would be nice too). Here are some links that can help you prepare that:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
ALTER PROCEDURE [dbo].[spCheckActivePaient] -- Add the parameters for the stored procedure here @MR varchar(50)AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- Insert statements for procedure here SELECT CASE WHEN Active = 1 UPDATE Set Active= 0 ELSE UPDATE Set Active = 1 END , LastName,Active FROM Demographics WHERE MR=@MR |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 14:18:13
|
[code]ALTER PROCEDURE [dbo].[spCheckActivePaient]-- Add the parameters for the stored procedure here@MR varchar(50)AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON-- Insert statements for procedure hereSELECT ~Active , LastName,ActiveFROM Demographics WHERE MR=@MR[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
rsquared
Starting Member
5 Posts |
Posted - 2012-10-18 : 14:32:22
|
quote: Originally posted by visakh16
ALTER PROCEDURE [dbo].[spCheckActivePaient]-- Add the parameters for the stored procedure here@MR varchar(50)AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON-- Insert statements for procedure hereThanks but where is the part that will determine if Active is true? And if it is true I want to update it to false????SELECT ~Active , LastName,ActiveFROM Demographics WHERE MR=@MR ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 14:56:16
|
quote: Originally posted by rsquared
quote: Originally posted by visakh16
ALTER PROCEDURE [dbo].[spCheckActivePaient]-- Add the parameters for the stored procedure here@MR varchar(50)AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON-- Insert statements for procedure hereThanks but where is the part that will determine if Active is true? And if it is true I want to update it to false????SELECT ~Active , LastName,ActiveFROM Demographics WHERE MR=@MR ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
~Active will cause 1 to become 0 and 0 to become 1 . no need of any checking conditions. ~ is unary not operator------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
rsquared
Starting Member
5 Posts |
Posted - 2012-10-18 : 15:10:31
|
quote: Originally posted by visakh16
quote: Originally posted by rsquared
quote: Originally posted by visakh16
ALTER PROCEDURE [dbo].[spCheckActivePaient]-- Add the parameters for the stored procedure here@MR varchar(50)AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON-- Insert statements for procedure hereThanks but where is the part that will determine if Active is true? And if it is true I want to update it to false????SELECT ~Active , LastName,ActiveFROM Demographics WHERE MR=@MR ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
~Active will cause 1 to become 0 and 0 to become 1 . no need of any checking conditions. ~ is unary not operator------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I don't get it. It is not changing the values form 0 to 1???? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 15:30:36
|
are you asking about changing values in tables or just changing why displaying data?If former then it should be an update likeALTER PROCEDURE [dbo].[spCheckActivePaient]-- Add the parameters for the stored procedure here@MR varchar(50)AS-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON-- Insert statements for procedure hereUPDATE dSET Active = ~Active FROM Demographics dWHERE MR=@MR If latter then my posted suggestion workshere's an illustration for you as welldeclare @test table(i bit,r int)insert @testvalues (1,123)insert @testvalues (0,110)insert @testvalues (1,234)select i,~ifrom @testwhere r=123output-------------------i Negatedi-------------------1 0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
rsquared
Starting Member
5 Posts |
Posted - 2012-10-18 : 17:55:21
|
It worked great. Thank for your help. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-18 : 21:42:44
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|