SQL Stored Procedure if statement - modified date

Trying to test if parameter is different from column, to track table m

Page 1 of 1

2 Replies - 12310 Views - Last Post: 10 October 2008 - 11:12 AM Rate Topic: -----

#1 pepsidime  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 08-October 08

SQL Stored Procedure if statement - modified date

Post icon  Posted 10 October 2008 - 10:04 AM

This is an UPDATE procedure which should update columns according to 2 conditions. The second IF statement is working fine. The first one, I am having problems with. I need to check if the incoming parameters match the current values in the table. If they do not, then I need to update ModifiedBy and ModifiedOn. Otherwise, I need to leave those fields alone.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go







CREATE PROCEDURE [dbo].[usp_UpdateTblMarketingPrograms3]
(
	@ID						int
	,@FName					varchar(50)
	,@LName					varchar(50)
	,@Email					varchar(100)
	,@ProgramTitle			varchar(50)
	,@ProgramDesc			varchar(200)
	,@ProgramHeader		 varchar(200)
	,@ProgramIntro			text
	,@ProgramEmail		  text
	,@ProgramFile			varchar(200)
	,@ModifiedBy			varchar(20)
	,@ModifiedOn			datetime
)

AS

IF @FName = FName AND @LName = LName AND @Email = @Email AND @ProgramTitle = ProgramTitle AND
   @ProgramDesc = ProgramDesc AND @ProgramHeader = ProgramHeader AND @ProgramIntro = ProgramIntro AND
   @ProgramEmail = ProgramEmail AND @ProgramFile = ''
	BEGIN
	  UPDATE tbl_MarketingPrograms
	  SET ModifiedBy = @ModifiedBy, ModifiedOn = @ModifiedOn WHERE ID = @ID
	END

IF @ProgramFile = ''
	BEGIN
	  UPDATE tbl_MarketingPrograms
	  SET FName = @FName, LName = @LName, Email = @Email,
	  ProgramTitle = @ProgramTitle, ProgramDesc = @ProgramDesc, ProgramHeader = @ProgramHeader,
	  ProgramIntro = @ProgramIntro, ProgramEmail = @ProgramEmail WHERE ID = @ID
	END
ELSE
	BEGIN
	  UPDATE tbl_MarketingPrograms
	  SET FName = @FName, LName = @LName, Email = @Email,
	  ProgramTitle = @ProgramTitle, ProgramDesc = @ProgramDesc, ProgramHeader = @ProgramHeader,
	  ProgramIntro = @ProgramIntro, ProgramEmail = @ProgramEmail, ProgramFile = @ProgramFile WHERE ID = @ID
	END



The error I am getting is Invalid column name for all the columns in the first IF statement. I know I am missing something, but I don't know what. Maybe a SELECT before the IF? I'm new to SQL. Any help would be appreciated. Thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Stored Procedure if statement - modified date

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1638
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL Stored Procedure if statement - modified date

Posted 10 October 2008 - 10:13 AM

You should check if the column value matches the value coming in, not the other way around. Try this and see if it changes your situation :)

CREATE PROCEDURE [dbo].[usp_UpdateTblMarketingPrograms3]
(
    @ID                        int
    ,@FName                    varchar(50)
    ,@LName                    varchar(50)
    ,@Email                    varchar(100)
    ,@ProgramTitle            varchar(50)
    ,@ProgramDesc            varchar(200)
    ,@ProgramHeader         varchar(200)
    ,@ProgramIntro            text
    ,@ProgramEmail          text
    ,@ProgramFile            varchar(200)
    ,@ModifiedBy            varchar(20)
    ,@ModifiedOn            datetime
)

AS
IF FName = @FName AND LName = @LName AND Email = @Email AND ProgramTitle =@ProgramTitle AND
   ProgramDesc = @ProgramDesc AND ProgramHeader = @ProgramHeader AND ProgramIntro = @ProgramIntro AND
   ProgramEmail = @ProgramEmail AND @ProgramFile = ''
    BEGIN
      UPDATE tbl_MarketingPrograms
      SET ModifiedBy = @ModifiedBy, ModifiedOn = @ModifiedOn WHERE ID = @ID
    END

IF @ProgramFile = ''
    BEGIN
      UPDATE tbl_MarketingPrograms
      SET FName = @FName, LName = @LName, Email = @Email,
      ProgramTitle = @ProgramTitle, ProgramDesc = @ProgramDesc, ProgramHeader = @ProgramHeader,
      ProgramIntro = @ProgramIntro, ProgramEmail = @ProgramEmail WHERE ID = @ID
    END
ELSE
    BEGIN
      UPDATE tbl_MarketingPrograms
      SET FName = @FName, LName = @LName, Email = @Email,
      ProgramTitle = @ProgramTitle, ProgramDesc = @ProgramDesc, ProgramHeader = @ProgramHeader,
      ProgramIntro = @ProgramIntro, ProgramEmail = @ProgramEmail, ProgramFile = @ProgramFile WHERE ID = @ID
    END


Was This Post Helpful? 0
  • +
  • -

#3 pepsidime  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 08-October 08

Re: SQL Stored Procedure if statement - modified date

Posted 10 October 2008 - 11:12 AM

Thanks for the reply. I tried that and I got the same error. I know it has something to do with the fact that I haven't selected a row at that point. I just don't know what to put in. I've tried a WHERE statement at the end of the IF comparison but it gives a syntax error on that.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1