[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help fixing my SQL Statement using IF EXISTS

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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

650 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