SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question what's wrong with this storedprocedure please

    I write this stored procedure to modify the identity column depending on entered value it worked well just one time but now not work how can i fix this problem
    Code:
    create proc TickIDStart 
    (
    @IDnew int
    )
    as
    	BEGIN
    set nocount on
    -- Allow the insert in the identity column
    SET IDENTITY_INSERT TCKTs ON
    --Insert any data and delete it to adjust the identity column	
    insert into TCKTs (TCKT_ID,TCKT_DESC, TCKT_SEVERITY_ID, Open_User_ID, RPT_USER_ID, TCKT_SUB_PROJ_ID) values (@IDnew,'text', 1, 1598,1610,122)
    delete from TCKTs where TCKT_ID= @IDnew
    --Disable the insert in the identity column
    SET IDENTITY_INSERT TCKTs Off	
    	END

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    why do you feel you need to do this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why do you feel you need to do this?
    it's apart of project that we need to change the start of id with the year

    for ex : 200800023
    tommorow we need to 200900001

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please try the INSERT and DELETE outside of your stored procedure, using separate query steps, so that you can confirm after the INSERT that it was actually added correctly...

    ... and let us know if you get any error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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