?
Solved

Help fixing my SQL Statement using IF EXISTS

Posted on 2011-03-02
5
Medium Priority
?
264 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

762 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