Procedure returned value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Procedure returned value

Author

Topic

blueangel2008
Starting Member

2 Posts

Posted - 2008-12-10 : 04:26:34

i got a procedure in SQL Server at the end of the program i write this code
SELECT @@IDENTITY

COMMIT TRANSACTION INSERT_NOMENCLATURE
RETURN

the problem that i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name

when i excute my procedure In SQL i have the value but in the name of column i have (no column name)
i write a script in C# to get the value with data reader but the method used in datareader is GetValueParam(Command,columName) the procedure dosen't return a name for this column i tried to give a name
SELECT @@IDENTITY AS ID

COMMIT TRANSACTION INSERT_NOMENCLATURE
RETURN

But still not work

thank you for help!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-10 : 04:50:18

SELECT @@IDENTITY AS [Required Column Name] will give you a column name. Example:


SELECT @@IDENTITY AS [Ident Column]

Returns a column called Ident Column

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-10 : 04:51:11

You might want to look up SCOPE_IDENTITY instead -- @@IDENTITY can be unreliable. TRIGGERS will break your code otherwise!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

blueangel2008
Starting Member

2 Posts

Posted - 2008-12-10 : 05:02:34

This is the code of my procedure at the end as you see I select what you ask to do but it return alawys a results with no name column


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_INS_QUESTION]

      @lib varchar(32),     -- le libellé à insérer
      @desc varchar(1024),  -- la description à insérer
      @id_parent int,       -- Ancêtre ou frère point d'origine de l'insertion 
      @mode char(2)         -- le mode d'insertion :
                                    -- FA : Fils Ainé,
                                    -- FC : Fils Cadet,
                                    -- GF : Grand frère,
                                    -- PF : Petit Frère,
                                    -- P  : Père
      
AS

 DECLARE @OK int

 DECLARE @bgp int          -- borne gauche parent
 DECLARE @bdp int          -- borne droite parent 
 DECLARE @nivp int         -- niveau parent 

 DECLARE @bgi int          -- borne gauche à insérer
 DECLARE @bdi int          -- borne droite à insérer
 DECLARE @nivi int         -- niveau à insérer

 SET NOCOUNT ON

-- gestion des effets de bord
 IF @mode IS NULL OR @lib IS NULL OR @lib = ''
 BEGIN
    RAISERROR ('Insertion impossible sans libellé ou mode ! (TABLE Question)', 16, 1)
    RETURN
 END

 SET @mode = UPPER(@mode)
 IF NOT( @mode = 'FA' OR @mode = 'FC' OR @mode = 'GF' OR @mode = 'PF'  OR @mode = 'P')
 BEGIN
    RAISERROR ('Insertion impossible, mode inconnu !', 16, 1)
    RETURN
 END

 -- démarrage transaction 
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
 BEGIN TRANSACTION INSERT_QUESTION
 

-- pas de parent => seul cas, table vide ou insertion d'un collatéral 
 IF @id_parent IS NULL
 BEGIN
    SELECT @OK = count(*) FROM Question
    IF @OK = 0 OR @OK IS NULL
    BEGIN
       IF @mode = 'FA' OR @mode = 'FC'
       BEGIN
          RAISERROR ('Insertion impossible dans un arbre pour un fils sans père !', 16, 1)
          GOTO LBL_ERROR
          RETURN
       END
       ELSE
       BEGIN
-- première insertion 
          INSERT INTO Question ( Label, Description, Tlevel, BL, BR )
                 VALUES( @lib, @desc, 0, 1, 2 )
          IF @@ERROR <> 0
          BEGIN
             GOTO LBL_ERROR
             RETURN
          END
          COMMIT TRANSACTION INSERT_QUESTION
          SELECT @@IDENTITY
          RETURN
       END
    END
    ELSE 
-- Insertion d'un collatéral 
    BEGIN 
       RAISERROR ('Insertion impossible dans un arbre pour un collatéral sans précision du parent !', 16, 1)
       GOTO LBL_ERROR
       RETURN
    END
 END

-- Le parent existe toujours ?
 SELECT @OK = count(*) FROM Question WHERE QuestionId = @id_parent
 IF @OK = 0 OR @OK IS NULL
 BEGIN
    RAISERROR ('Insertion impossible, le parent n''existe plus !', 16, 1)
    GOTO LBL_ERROR
    RETURN
 END

-- On a un parent : on récupère ses éléments
 SELECT @bgp = BL, @bdp = BR, @nivp = Tlevel 
        FROM Question 
        WHERE QuestionId = @id_parent

-- insertion d'un père
 IF @mode = 'P'
 BEGIN
    -- Décalage de l'ensemble colatéral droit
    UPDATE Question
           SET BR = BR + 2
           WHERE BR > @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END
    UPDATE Question
           SET BL = BL + 2
           WHERE BL > @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    -- Décalalage ensemble visé vers le bas
    UPDATE Question
           SET BL = BL + 1,
               BR = BR + 1,
               Tlevel = Tlevel + 1
           WHERE BL >= @bgp AND BR <= @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    -- Insertion du nouveau père
    INSERT INTO Question ( Label, Description, Tlevel, BL, BR )
           VALUES( @lib, @desc, @nivp, @bgp, @bdp + 2 )
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END
 END

-- Insertion d'un grand frère
 IF @mode = 'GF'
 BEGIN
    -- Limite sup.
    UPDATE Question
           SET BR = BR + 2
           WHERE BR > @bgp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    -- Limite inf.
    UPDATE Question
           SET BL = BL + 2
           WHERE BL >= @bgp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    SET @bgi = @bgp
    SET @bdi = @bgp + 1
    SET @nivi = @nivp
    INSERT INTO Question ( Label, Description, Tlevel, BL, BR )
           VALUES( @lib, @desc, @nivi, @bgi, @bdi )
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END
 END
 

--  Insertion d'un petit frère 
 IF @mode = 'PF'
 BEGIN
    -- Limite sup.
    UPDATE Question
           SET BR = BR + 2
           WHERE BR > @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    -- Limite inf.
    UPDATE Question
           SET BL = BL + 2
           WHERE BL >= @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    SET @bgi = @bdp + 1
    SET @bdi = @bdp + 2
    SET @nivi = @nivp
    INSERT INTO Question ( Label, Description, Tlevel, BL, BR )
           VALUES( @lib, @desc, @nivi, @bgi, @bdi )
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END
 END

--  Insertion d'un fils ainé
 IF @mode = 'FA'
 BEGIN
    -- Limite sup.
    UPDATE Question
           SET BR = BR + 2
           WHERE BR > @bgp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    -- Limite inf.
    UPDATE Question
           SET BL = BL + 2
           WHERE BL > @bgp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    SET @bgi = @bgp + 1
    SET @bdi = @bgp + 2
    SET @nivi = @nivp + 1
    INSERT INTO Question ( Label, Description, Tlevel, BL, BR )
           VALUES( @lib, @desc, @nivi, @bgi, @bdi )
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END
 END

--  Insertion d'un fils cadet
 IF @mode = 'FC'
 BEGIN
    -- Limite sup.
    UPDATE Question
           SET BR = BR + 2
           WHERE BR >= @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    -- Limite inf.
    UPDATE Question
           SET BL = BL + 2
           WHERE BL > @bdp
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END

    SET @bgi = @bdp
    SET @bdi = @bdp + 1
    SET @nivi = @nivp + 1
    INSERT INTO Question ( Label, Description, Tlevel, BL, BR )
           VALUES( @lib, @desc, @nivi, @bgi, @bdi )
    IF @@ERROR <> 0
    BEGIN
       GOTO LBL_ERROR
       RETURN
    END
 END

-- renvoi de l'identifiant de l'élément inséré

 SELECT SCOPE_IDENTITY() AS NODEID
 COMMIT TRANSACTION INSERT_QUESTION
 RETURN 
	
 LBL_ERROR:
 ROLLBACK TRANSACTION INSERT_QUESTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-10 : 07:32:09

Hi.

I'm not a C# programmer but I think that call GetValueParam is actually retrieving the RETURN value rather than that generated by the SELECT field.

Try doing


DECLARE @returnRow INT

<rest of your code>

SELECT @returnRow = SCOPE_IDENTITY()
RETURN @returnRow

Rather than trying to SELECT it.

Or, change the method used.

Again, I don't know much about C# but I think you are using the wrong method.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources