Solved

Help fixing my SQL Statement using IF EXISTS

Posted on 2011-03-02
5
251 Views
Last Modified: 2012-05-11
I have an output parameter called @PickTicketID.  What I'm trying to do is this.

If the record exists return the ID of the record via the output parameter.  If not, Insert a new record and return the new record ID through the output parameter.

Now the Insert works and returns the ID without a problem.  However, if the record exists it doesn't return the ID at all.  Only a NULL value.

How can return the existing ID through the output parameter when a record is found?

Thanks!
ALTER PROCEDURE [dbo].[myStoredProcedure]
	(
	@ComponentID		nvarchar(50),
	@InventoryType		nvarchar(50),
	@ClientID			int,
	@PickTicketID		int OUTPUT
	)
AS	
BEGIN

	SET NOCOUNT ON;
	
	-- CHECK IF RECORD EXISTS IN TABLE
	
	IF EXISTS (SELECT ID FROM Ticket WHERE (ComponentID = @ComponentID) AND (InventoryType = @InventoryType) AND (ClientID = @ClientID))
		SELECT @PickTicketID = (SELECT ID 
					FROM Ticket 
					WHERE (ComponentID = @ComponentID) AND (InventoryType = @InventoryType) AND (ClientID = @ClientID))
	
	ELSE
	
	-- INSERT new record
	INSERT INTO Ticket(ComponentID, InventoryType, ClientID) VALUES (@ComponentID,@InventoryType,@ClientID)
	
	-- RETURN THE ID
	SELECT @PickTicketID = SCOPE_IDENTITY()
	
END

Open in new window

0
Comment
Question by:cdemott33
5 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 35018412
I think you're missing a begin and end in the else part of your if statement.


      IF EXISTS (SELECT ID FROM Ticket WHERE (ComponentID = @ComponentID) AND (InventoryType = @InventoryType) AND (ClientID = @ClientID))
        BEGIN

            SELECT @PickTicketID = (SELECT ID
                              FROM Ticket
                              WHERE (ComponentID = @ComponentID) AND (InventoryType = @InventoryType) AND (ClientID = @ClientID))
      END
      ELSE
      BEGIN
              -- INSERT new record
              INSERT INTO Ticket(ComponentID, InventoryType, ClientID) VALUES (@ComponentID,@InventoryType,@ClientID)
      
             -- RETURN THE ID
             SELECT @PickTicketID = SCOPE_IDENTITY()
        END
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35018434
TRY
ALTER PROCEDURE [dbo].[myStoredProcedure]
	(
	@ComponentID		nvarchar(50),
	@InventoryType		nvarchar(50),
	@ClientID			int,
	@PickTicketID		int OUTPUT
	)
AS	
BEGIN

	SET NOCOUNT ON;
	
	-- CHECK IF RECORD EXISTS IN TABLE
	
	SELECT @PickTicketID = ID 
	FROM Ticket 
	WHERE (ComponentID = @ComponentID) AND (InventoryType = @InventoryType) AND (ClientID = @ClientID)
		
	-- INSERT new record
	IF @PickTicketID IS NULL
	BEGIN
		INSERT INTO Ticket(ComponentID, InventoryType, ClientID) VALUES (@ComponentID,@InventoryType,@ClientID)
		-- RETURN THE ID
		SELECT @PickTicketID = SCOPE_IDENTITY()
	END;
	
END

Open in new window

0
 

Author Closing Comment

by:cdemott33
ID: 35018445
PERFECT!  Thank you
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35018525
like this...

you hadn't got the insert processing within its own begin/end block so probably the select @pickticket=scope_identity()
was always overwriting the exists value
ALTER PROCEDURE [dbo].[myStoredProcedure]
	(
	@ComponentID		nvarchar(50),
	@InventoryType		nvarchar(50),
	@ClientID			int,
	@PickTicketID		int OUTPUT
	)
AS	
BEGIN

	SET NOCOUNT ON;
	
	-- CHECK IF RECORD EXISTS IN TABLE
	
	Declare @rows int
	
		SELECT @PickTicketID =  ID 
		  FROM Ticket 
		 WHERE ComponentID = @ComponentID
		   AND InventoryType = @InventoryType
		   AND ClientID = @ClientID
	Select @rows=@@ROWCOUNT
	if @rows=0
	begin 
	   	-- INSERT new record
	    INSERT INTO Ticket(ComponentID, InventoryType, ClientID) 
	     VALUES (@ComponentID,@InventoryType,@ClientID)
	
	      -- RETURN THE ID
	     SELECT @PickTicketID = SCOPE_IDENTITY()
    END
    
    Return
    go

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35018552
Any time. Glad to help.

Lee
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question