Solved

Help fixing my SQL Statement using IF EXISTS

Posted on 2011-03-02
5
238 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:ewangoya
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

770 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