Solved

Help fixing my SQL Statement using IF EXISTS

Posted on 2011-03-02
5
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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