Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    6

    Unanswered: Flushing Output Results of a Stored Procedure

    I am running SQL Server 2005 Express. I have the following stored procedure:

    Code:
    USE [BlackberryTracker]
    GO
    /****** Object:  StoredProcedure [dbo].[spCreateBlackberry]    Script Date: 08/14/2009 08:25:37 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[spCreateBlackberry] (@ServiceID char(8), @PhoneNumber char(10), @SIM char(20), @IMEI char(15), @PIN char(8), @Model varchar(24), @FirstName varchar(64), @MiddleInitial varchar(8), @LastName varchar(64), @Unit varchar(64), @Billet varchar(64), @Rank varchar(8), @UserName varchar(24), @Building varchar(24), @Floor varchar(8), @Room varchar(24), @AssetTag char(10), @ComputerName char(12)) AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	INSERT INTO tblBlackberry (ServiceID, PhoneNumber) VALUES (@ServiceID, @PhoneNumber);
    	SELECT @@IDENTITY;
    	INSERT INTO tblBlackberryHistory (BlackberryID, SIM, IMEI, PIN, Model, FirstName, MiddleInitial, LastName, Unit, Billet, Rank, UserName, Building, Floor, Room, AssetTag, ComputerName, DateTime) VALUES (@@IDENTITY, @SIM, @IMEI, @PIN, @Model, @FirstName, @MiddleInitial, @LastName, @Unit, @Billet, @Rank, @UserName, @Building, @Floor, @Room, @AssetTag, @ComputerName, GETDATE());
    
    END
    It appears that the first INSERT statement is returning a resultset. So the whole procedure is returning the output from the first INSERT and then the @@IDENTITY. I want to return only the @@IDENTITY.

    I appreciate any assistance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by wswartzendruber
    It appears that the first INSERT statement is returning a resultset.
    It appears or it is? Because the SQL you posted shouldn't do that. Exactly what is the result set?

    Also, usually scope_identity is preferred to @@identity.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is there a trigger on tblBlackberry? If there is, you may need to use SCOPE_IDENTITY, rather than @@identity.

  4. #4
    Join Date
    Aug 2009
    Posts
    6
    There are no triggers as of yet. I have uploaded a screenshot of what I'm seeing.

    EDIT: I forgot about I trigger I applied to one of the tables. But can anyone still tell me how to make it work even if I did have a trigger in there?
    Last edited by wswartzendruber; 08-14-09 at 13:44.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Could you expand the triggers folder for tblBlackberry? If there are any triggers, please can you post the code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ummm....SCOPE_IDENTITY?

  7. #7
    Join Date
    Aug 2009
    Posts
    6
    I dropped the trigger. It went through and deleted all related rows from the other table.

    And yes, I changed @@IDENTITY to SCOPE_IDENTITY().

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •