Standard Store procedure look like

  • Hi,

    Suppose i write a general proc like this.

    Create proc Example

    @empid int,

    @Mode varchar (1),

    @fname varchar (100),

    @lname varchar(100)

    As

    If (@Mode='S'

    Begin

    Select fname,lname from emp where empid=@empid

    End

    If (@Mode='I'

    Begin

    Insert into emp(fname,lname) values(@fname,@lname)

    End

    If (@Mode='U')

    Begin

    Update emp set fname=@fname,lname=@lname where empid=@empid

    End

    GO

    This run ok.How should i implement error handler,comit and rolback transaction,like any standard strore procedure.

    Also according to you all,what is wrong in this store procedure.

    Thanks

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • Hi,

    Show you emp table structure

    Because during insert, your not call emp colums

    ARUN SAS

  • Hi,

    You can assume anything.Though structure of my table is

    CREATE TABLE [emp] (

    [empid] [int] IDENTITY (1, 1) NOT NULL ,

    [Fname] [varchar] (100) NOT NULL ,

    [Lname] [varchar] (100) NOT NULL

    ) ON [PRIMARY]

    GO

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • Hi,

    In your table shows all col are not null,

    Then in the sp line

    If (@Mode='I'

    Begin

    Insert into emp(fname,lname) values(@fname,@lname)

    End

    returns with error

    AND what error you need to return,

    Because it’s automatically return by not null column

    ARUN SAS

  • Hi,

    It should not accept null values.Now it does and do not return any value and record get save.

    It should not accept null values at all.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • Hi,

    Because the table column having IDENTITY (1, 1)

    You should not pass the value to this column, so that its wound raises the error

    ARUN SAS

    πŸ™‚

  • Hi,

    Listen, I am not passing value to empid which is identity(1,1).

    Fname and Lname are not null,but when null value pass,it accept it and do not throw any error.

    Try to understand my problem.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • HI,

    try this then exec the sp with null

    CREATE proc Example

    (

    @empid int,

    @Mode varchar (1),

    @fname varchar (100)= null,

    @lname varchar(100)=null

    )

    As

    begin

    -- CREATE TABLE [emp] (

    -- [empid] [int] IDENTITY (1, 1) NOT NULL ,

    -- [Fname] [varchar] (100) NOT NULL ,

    -- [Lname] [varchar] (100) NOT NULL

    -- ) ON [PRIMARY]

    -- GO

    If (@Mode='S')

    Begin

    Select fname,lname from emp where empid = @empid

    End

    If (@Mode='I')

    Begin

    Insert into emp(fname,lname) values(@fname,@lname)

    End

    If (@Mode='U')

    Begin

    Update emp set fname=@fname,lname=@lname where empid =@empid

    End

    end

    go

  • pandeharsh (3/31/2009)


    Hi,

    Suppose i write a general proc like this.

    Create proc Example

    @empid int,

    @Mode varchar (1),

    @fname varchar (100),

    @lname varchar(100)

    As

    If (@Mode='S'

    Begin

    Select fname,lname from emp where empid=@empid

    End

    If (@Mode='I'

    Begin

    Insert into emp(fname,lname) values(@fname,@lname)

    End

    If (@Mode='U')

    Begin

    Update emp set fname=@fname,lname=@lname where empid=@empid

    End

    GO

    This run ok.How should i implement error handler,comit and rolback transaction,like any standard strore procedure.

    Also according to you all,what is wrong in this store procedure.

    Thanks

    It's going to recompile almost every single time it gets called. The procedure name is used to check against cache to see if there is an existing plan and then the plan is verified against the query being run. If the first time through it creates a plan for the SELECT query and the second time through it goes to run the INSERT statement, it's going to cause a recompile. This will pause the execution of the plan and possibly block other sessions while the plan recompiles (although you could get statement level recompiles). If you really wanted to do it this way, better to create a procedure for each independent action and then call those procedures from this wrapper procedure. That way each procedure gets it's own execution plan that doesn't need to be recompiled over & over.

    It works for really simple tables, but it's going to get messy and hard to maintain when you get to larger tables. Also, this is predicated on single row inserts, updates & deletes. What happens when you have to start dealing with sets? I wouldn't generally do things like this.

    But yes, I would put transactions and error handling in place.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Personally, I don't like writing everything in a single procedure. Instead, I write procedures according to their need and follow these standard rules for any basic table.

    Take for an example of mstEmployees table, for this table I will create procedures for GET (usp_mst_GetEmployeeByID), GET ALL (usp_mst_GetEmployees) & SAVE (usp_mst_SaveEmployee) employees.

    And regarding error & transaction handling, you really don't need that in these type of procedures, as the GET/GET ALL methods are just plain SELECTs and SAVE method is either INSERT or UPDATE (only 1 DML statement).

    --Ramesh


  • Hi,

    Thanks a lot for advice.

    Like you mention,that you would add transaction and error handler,how will you go about it ?I just need a proper structure of it.

    Thanks

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • You don't need error handling or transactions for the SELECT part of the query. There's nothing going on there that requires it.

    As to the rest, here's an article I wrote on error handling[/url]. Just refer to the 2005 code, not the 2000 code. If you're going to insist on having this entire thing in one proc (not a good idea), then I'd put the TRY/CATCH around the whole thing and the same with the transaction. There's a section on using transactions in the same code, but you can do a search to find better examples.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi

    You can use BEGIN TRANSACTION things like this but this may not help you in all the ways. Especially when you have batch updates then you want to put these in single transaction. So when you are doing any DML operations, it completely depends on the requirements.

    Thanks -- Vijaya Kadiyala

    http://www.dotnetvj.com

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply