Post reply

Select,Update and condition check in Stored Procedure...

  • July 6, 2011 at 8:51 pm

    #243927

    Hi,

    i want to perform select,update and condition check in Stored procedure.(change old name to new name)

    First im doing a select operation of two columns from a table master1

    select a_name, l_user, * from master1 where (a_name='ss1'or l_user='ss1')

    next i want to perform a update operation based on the condition check:

    if a_name ='ss1' then

    update master1 set a_name=UPPER('rr1') where a_name='ss1'

    if l_user='ss1' then

    update master1 set l_user=UPPER('rr1') where l_user='ss1'

    Note : i will be passing parameters instead of values....

    ssl --> @oldname

    rrl --> @newname

    Can anyone help me on how to implement this logic in Stored procedure??

    Thanks in Advance...

  • July 6, 2011 at 9:02 pm

    #1349394

    You can do it a bunch of ways. Here's one done in my own code format. Note that I haven't included error handling, and for production quality code you should.

    if (object_id('dbo.ChangeUserName') is not null) begin

    drop procedure dbo.ChangeUserName;

    end; -- if

    go

    --

    -- ChangeUserName:changes any username or login name matching the

    --old name to be the new name

    --

    create procedure dbo.ChangeUserName(

    @oldname sysname,

    @newname sysname

    )

    as begin

    set nocount on;

    begin transaction;

    updatedbo.master1

    seta_name = upper(@newname)

    wherea_name = @oldname;

    updatedbo.master1

    setl_user = upper(@newname)

    wherel_user = @oldname;

    commit;

    return (0);

    end; -- procedure

    go

  • July 6, 2011 at 9:53 pm

    #1349410

    Thanks 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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