Results 1 to 7 of 7

Thread: sproc this!

  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: sproc this!

    ha ha ,

    havent used sql serv in years, and having trouble wit me sprocet! What devilish deed did i do?

    Use Contacts
    GO

    CREATE PROC MaserIn
    @entred datetime = GETDATE(),
    @initials varchar(2) =Null,
    @Vendor varchar(50),
    @CkNo varchar(20),
    @expType varchar(15),
    @ckDate datetime,
    @ckAmt money,
    @mId int OUTPUT

    AS

    INSERT INTO [Contacts].[dbo].[Master](
    [entered], [initials], [Vendor],
    [CkNo], [expType], [ckDate], [ckAmt])
    VALUES(@entered,
    @initials,
    @Vendor,
    @CkNo,
    @expType,
    @ckDate,
    @ckAmt )

    SELECT @mID=@@iDENTITY

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    Not much, just take away the default to getdate

    ALTER PROC MaserIn
    @entred datetime ,
    @initials varchar(2) =Null,
    @Vendor varchar(50),
    @CkNo varchar(20),
    @expType varchar(15),
    @ckDate datetime,
    @ckAmt money,
    @mId int OUTPUT

    AS

    if @entred is null
    begin
    set @entred = getdate()
    end

    INSERT INTO [Contacts].[dbo].[Master](
    [entered], [initials], [Vendor],
    [CkNo], [expType], [ckDate], [ckAmt])
    VALUES(@entred,
    @initials,
    @Vendor,
    @CkNo,
    @expType,
    @ckDate,
    @ckAmt )

    SELECT @mID=@@iDENTITY

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    and change @@IDENTITY to SCOPE_IDENTITY()
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Oct 2003
    Posts
    311
    Why change @@ID?

  5. #5
    Join Date
    Oct 2003
    Posts
    311
    How can i put todays date in with out using =getdate as a defualt?

  6. #6
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    You can call the stored proc get back the identity by the following code
    declare @mid int
    exec MaserIn null,'ch','Iris','1','Consultant','8/17/2004',300,@mid = @mid output
    select @mid

    Or

    You can rewrite the stored proc to default entred to be getdate() all the time as the following code
    alter PROC MaserIn
    @initials varchar(2) =Null,
    @Vendor varchar(50),
    @CkNo varchar(20),
    @expType varchar(15),
    @ckDate datetime,
    @ckAmt money,
    @mId int OUTPUT

    AS

    INSERT INTO [Master](
    [entered], [initials], [Vendor],
    [CkNo], [expType], [ckDate], [ckAmt])
    VALUES(getdate(),
    @initials,
    @Vendor,
    @CkNo,
    @expType,
    @ckDate,
    @ckAmt )
    SELECT @mID=@@iDENTITY

    And call the stored proc as following code:
    declare @mid int
    exec MaserIn 'ch','Iris','1','Consultant','8/17/2004',300,@mid = @mid output
    select @mid

    The reason change to scope_identity() is to ensure the identity value you get back is from current session,
    Here is the explanation from books on line---
    SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mikezcg
    How can i put todays date in with out using =getdate as a defualt?
    I'd use
    Code:
    Coalesce(@ckdate, GetDate())
    -PatP

Posting Permissions

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