OK... heres what I've got

Any help is greatly appreciated



CREATE PROCEDURE spAddTicket

	

	@fkProduct int,@fkOS int,@charBIOSName varchar(50),

	@charIP varchar(50),

	@fkUser int,@dtDateOpened datetime,

	@fkPriority int,@fkSubLocation int,@charRoomNum varchar(50),

	@txtProblem text,@txtJustification text

	

AS

DECLARE @fkTicket /*I want this to be the ID generated (pkTicketID) by the

statement below

/* make the ticket*/



INSERT INTO tblTickets

(fkUser,fkStatus)

VALUES 

(@fkUser,1)



/*Enter the info*/



/*

SET @fkTicket = SELECT MAX(pkTicketID) FROM tblTickets

that doesn't work....

*/

INSERT INTO tblInfo

(fkTicket,charBIOSName,charIP,fkSubLocation,charRoomNum,

fkPriority,fkOS,fkProduct,txtJustification)

VALUES

(@fkTicket,@charBIOSName,@charIP,@fkSubLocation,@charRoomNum,

@fkPriority,@fkOS,@fkProduct,@txtJustification)



SELECT MAX(pkTicketID) FROM tblTickets

GO

No, No! Don't use SELECT MAX()!



This should work for you:



CREATE PROC usp_MySproc



    @ID    int    output



AS



    INSERT INTO

        table1

        (

            field1

        )

    VALUES

        (

            'foo'

        )



    SET @ID = @@Identity



GO



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "O'Hara, Elliott M" <EMOHARA@k...>

Subject: [asp_web_howto] SQL again





: OK... heres what I've got

: Any help is greatly appreciated

:

: CREATE PROCEDURE spAddTicket

:

: @fkProduct int,@fkOS int,@charBIOSName varchar(50),

: @charIP varchar(50),

: @fkUser int,@dtDateOpened datetime,

: @fkPriority int,@fkSubLocation int,@charRoomNum varchar(50),

: @txtProblem text,@txtJustification text

:

: AS

: DECLARE @fkTicket /*I want this to be the ID generated (pkTicketID) by the

: statement below

: /* make the ticket*/

:

: INSERT INTO tblTickets

: (fkUser,fkStatus)

: VALUES

: (@fkUser,1)

:

: /*Enter the info*/

:

: /*

: SET @fkTicket = SELECT MAX(pkTicketID) FROM tblTickets

: that doesn't work....

: */

: INSERT INTO tblInfo

: (fkTicket,charBIOSName,charIP,fkSubLocation,charRoomNum,

: fkPriority,fkOS,fkProduct,txtJustification)

: VALUES

: (@fkTicket,@charBIOSName,@charIP,@fkSubLocation,@charRoomNum,

: @fkPriority,@fkOS,@fkProduct,@txtJustification)

:

: SELECT MAX(pkTicketID) FROM tblTickets

: GO



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~