problem with identity in stored proc

[ace333]

This is my stored procedure and it gives the following error, I'm new to sql server stored procs so I'm sure its some basic error...
Incorrect syntax near the keyword 'IDENTITY'.Procedure(InsITRRComments)



CREATE PROCEDURE dbo.InsITRRComments

@CommentId int IDENTITY,
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
@FullUserName varchar(50),
@AmendedDateTime datetime,
@ProcessedFlag char(1)

AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ---------- -------------- -------------------------------------------
* 16 Sept 05 Noel Nicholson Original
*************************************************************************/

---------------------------------------------------------------------------
IF EXISTS( SELECT * FROM ITRRComments WHERE CommentId = @CommentId )
BEGIN
RAISERROR('Stored Proc:InsITRRComments: You cannot insert a duplicate item. Insert failed.',16,1)
RETURN
END

set identity_insert ITRRComments on


INSERT INTO ITRRComments
VALUES (
@CommentId,
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId,
@FullUserName,
@AmendedDateTime,
'N'
)


END

 

[vongrunt]

Identity as input argument? Remove it.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ace333]

This error now, what have i missed
[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@CommentId'.(42000,137) Procedure(InsITRRComments)



CREATE PROCEDURE dbo.InsITRRComments

/** @CommentId int IDENTITY,**/
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
@FullUserName varchar(50),
@AmendedDateTime datetime,
@ProcessedFlag char(1)

AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ---------- -------------- -------------------------------------------
* 16 Sept 05 Original
*************************************************************************/

---------------------------------------------------------------------------
IF EXISTS( SELECT * FROM ITRRComments WHERE CommentId = @CommentId )
BEGIN
RAISERROR('Stored Proc:InsITRRComments: You cannot insert a duplicate item. Insert failed.',16,1)
RETURN
END

SET IDENTITY_INSERT ITRRComments ON


INSERT INTO ITRRComments
VALUES (
/** @CommentId,**/
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId,
@FullUserName,
@AmendedDateTime,
'N'
)


END

 

[hmckillop]

Reading into what you want to do, it looks like you want to override the identity field and insert your own value.
If this is the case just remove the key work identity in the parameter

and also remember to switch the identity inserts back on.

CREATE PROCEDURE dbo.InsITRRComments

    @CommentId          int,
    @DacType            varchar(25),
    @DacTypeId          int,
    @TypeOfComment      varchar(50),
    @Comments           varchar(4000),
    @RiskId_External_ID varchar(16),
    @UserId             varchar(25),
    @FullUserName       varchar(50),
    @AmendedDateTime    datetime,
    @ProcessedFlag      char(1)

AS
BEGIN
    /*************************************************************************
     *
     * Name:           InsITRRComments
     *
     * Description:       Insert new Term Type item.
     *
     * Inputs:         
     *                          @CommentId                    
     *                     @DacType            
         *                 @DacTypeId          
         *                   @TypeOfComment      
         *                 @Comments           
         *                 @RiskId_External_ID
         *                 @UserId             
         *                 @FullUserName       
         *                 @AmendedDateTime    
         *                 @ProcessedFlag      
     *
     * Outputs:        None
     *
     *
     * Notes:
     *
     * History:
     *   Date       Author          Changes
     *   ----------    --------------  -------------------------------------------
     *   16 Sept 05    Noel Nicholson      Original
     *************************************************************************/

    ---------------------------------------------------------------------------
    IF EXISTS( SELECT * FROM ITRRComments WHERE CommentId = @CommentId )
    BEGIN
        RAISERROR('Stored Proc:InsITRRComments: You cannot insert a duplicate item. Insert failed.',16,1)
        RETURN
    END
    
    set identity_insert ITRRComments on


    INSERT INTO ITRRComments
    VALUES (
        @CommentId,                   
        @DacType,            
        @DacTypeId,          
        @TypeOfComment,      
        @Comments,           
        @RiskId_External_ID,
        @UserId,             
        @FullUserName,       
        @AmendedDateTime,    
        'N'   
    )
    set identity_insert ITRRComments off


END

"I'm living so far beyond my income that we may almost be said to be living apart

 

[ace333]

getting the following error now

12:10:28.586 [Microsoft][ODBC SQL Server Driver][SQL Server]An explicit value for the identity column in table 'ITRRComments' can only be specified when a column list is used and IDENTITY_INSERT is ON.(23000,8101) Procedure(InsITRRComments)

 

[hmckillop]

Try

CREATE PROCEDURE dbo.InsITRRComments

    @CommentId          int,
    @DacType            varchar(25),
    @DacTypeId          int,
    @TypeOfComment      varchar(50),
    @Comments           varchar(4000),
    @RiskId_External_ID varchar(16),
    @UserId             varchar(25),
    @FullUserName       varchar(50),
    @AmendedDateTime    datetime,
    @ProcessedFlag      char(1)

AS
BEGIN
    /*************************************************************************
     *
     * Name:           InsITRRComments
     *
     * Description:       Insert new Term Type item.
     *
     * Inputs:         
     *                          @CommentId                    
     *                     @DacType            
         *                 @DacTypeId          
         *                   @TypeOfComment      
         *                 @Comments           
         *                 @RiskId_External_ID
         *                 @UserId             
         *                 @FullUserName       
         *                 @AmendedDateTime    
         *                 @ProcessedFlag      
     *
     * Outputs:        None
     *
     *
     * Notes:
     *
     * History:
     *   Date       Author          Changes
     *   ----------    --------------  -------------------------------------------
     *   16 Sept 05    Noel Nicholson      Original
     *************************************************************************/

    ---------------------------------------------------------------------------
    IF EXISTS( SELECT * FROM ITRRComments WHERE CommentId = @CommentId )
    BEGIN
        RAISERROR('Stored Proc:InsITRRComments: You cannot insert a duplicate item. Insert failed.',16,1)
        RETURN
    END
    
    set identity_insert ITRRComments on


    INSERT INTO ITRRComments
(CommentId,                   
        DacType,            
        DacTypeId,          
        TypeOfComment,      
        Comments,           
        RiskId_External_ID,
        UserId,             
        FullUserName,       
        AmendedDateTime) --ensure these fields names match in your db
    VALUES (
        @CommentId,                   
        @DacType,            
        @DacTypeId,          
        @TypeOfComment,      
        @Comments,           
        @RiskId_External_ID,
        @UserId,             
        @FullUserName,       
        @AmendedDateTime,    
        'N'   
    )
    set identity_insert ITRRComments off


END

"I'm living so far beyond my income that we may almost be said to be living apart