Click here to Skip to main content
15,888,351 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am new to SQL. I have a stored proc like this:
SQL
CREATE PROCEDURE [dbo].[Developer_Set]
(
	@ID INT,
        @UserID INT,	
	@EffectiveDate DATETIME,
	@EndDate DATETIME = NULL,
	@UserName VARCHAR(255)
)
AS
BEGIN
	
	SET NOCOUNT ON;

	--Mark previous data as old
	UPDATE Developer
	SET DeletionIndicator = 1
	WHERE ID = @ID AND UserID = @UserID
	
	--Insert New Detail
	INSERT INTO dbo.Developer
	        ( ID ,
	          UserID ,
	          EffectiveDate ,
	          EndDate ,	         
	          DeletionIndicator
	        )
	VALUES  ( @ID , -- 
	          @UserID , -- UserID - int
	          @EffectiveDate, -- EffectiveDate - datetime
	          @EndDate, -- EndDate - datetime	          		 
	          CASE WHEN @EndDate IS NULL THEN 0 ELSE 1 END --DeletionIndicator
	        )

	
END

GO

I wanted to include a Where Statement like this :
SQL
INSERT INTO dbo.Developer
            ( ID ,
              UserID ,
              EffectiveDate ,
              EndDate ,
              DeletionIndicator
            )
    VALUES  ( @ID , --
              @UserID , -- UserID - int
              @EffectiveDate, -- EffectiveDate - datetime
              @EndDate, -- EndDate - datetime
              CASE WHEN @EndDate IS NULL THEN 0 ELSE 1 END --DeletionIndicator
            ) Where ID= @ID



Why am i not able to do this?Please help.
Posted
Updated 15-Jul-13 9:01am
v2
Comments
joshrduncan2012 15-Jul-13 14:56pm    
Why aren't you? Are you getting errors? If so, what are they?
vidkaat 15-Jul-13 14:57pm    
I get "Incorrect syntax near the keyword 'WHERE'.:

1 solution

You can't use WHERE in insert statements. Where compares the rows that are already IN the database in select and update statements. Because you are inserting a NEW row (thus one that is NOT in the database), WHERE simply does not apply. I don't know how to explain it any clearer than that, I'm afraid.

I don't know why you think you need to use that, but no matter why, you have to rethink and figure out how to do it correctly... Sorry.
 
Share this answer
 
v2
Comments
vidkaat 15-Jul-13 15:14pm    
Thank u .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900