Databases

Welcome to Dream.In.Code
	Getting Help is Easy! Join 136,235 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,262 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

SQL Stored Procedure if statement - modified date

SQL Stored Procedure if statement - modified date, Trying to test if parameter is different from column, to track table m

Options

pepsidime	10 Oct, 2008 - 09:04 AM Post #1
New D.I.C Head Joined: 8 Oct, 2008 Posts: 4 My Contributions	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. CODE 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.
	This Post Was Helpful!


Register for Free to Make These Ads Go Away!

PsychoCoder

RE: SQL Stored Procedure If Statement - Modified Date
10 Oct, 2008 - 09:13 AM

Post #2

using DIC.Core;

Joined: 26 Jul, 2007
Posts: 8,983

Windows

Thanked: 125 times
Dream Kudos: 8625
Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net

My Contributions

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

sql


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

This Post Was Helpful!

pepsidime	RE: SQL Stored Procedure If Statement - Modified Date 10 Oct, 2008 - 10:12 AM Post #3
New D.I.C Head Joined: 8 Oct, 2008 Posts: 4 My Contributions	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.
	This Post Was Helpful!