[dba-SQLServer]More SQL Server 7 Stored Procedure help if possible

Djabarov, Robert Robert.Djabarov at usaa.com
Wed Feb 26 13:57:06 CST 2003


Francisco,

It's still failing on compile, besides you don't really need a FROM clause because you're not joining anything.

NOLOCK is used to resolve concurrency issues in multi-user environment.

______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
( (210) 753-3148 - pager

 -----Original Message-----
From: 	Francisco H Tapia [mailto:my.lists at verizon.net] 
Sent:	Wednesday, February 26, 2003 12:18 PM
To:	dba-sqlserver at databaseadvisors.com
Subject:	Re: [dba-SQLServer]More SQL Server 7 Stored Procedure help if possible

Right-o it here is here it is again w/o syntax errors..the previous one was
OTOMH

CREATE PROCEDURE stp_UpdateAvailability (@PayrollNo as Int) AS
UPDATE tblAvailabilityAll
    SET AvailabilityCode = CASE AvailabilityCode
                                            WHEN A THEN 1
                                            WHEN N THEN 2
                                            WHEN S THEN 3
                                            WHEN H THEN 4
                                        ELSE AvailabilityCode --othewise
just leave as is.
       END
FROM tblAvailabilityAll
Where PayrollNo = @PayrollNo AND IsNumeric(AvailabilityCode) = 0

BTW, I like the use of Inner joins to weed out the Numeric based
Availability codes.  I think all in all your version runs faster as Joins
are much quicker than breaking down through a case statement.  Also, I've
never seen a use of the nolock hint, what kind of performance boost do you
get when using it? average?

-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Wednesday, February 26, 2003 9:51 AM
Subject: RE: [dba-SQLServer]More SQL Server 7 Stored Procedure help if
possible


: Nice...But if Paul creates a table that contains Availability codes, then
it will be much cleaner:
:
: create table tblAvailabilityCodes (
:    CodeID int identity(1,1) not null primary key nonclustered,
:    CodeValueChar char(1) not null,
:    CodeValueNum char(1) not null,
:    Description varchar(255) null)
: go
: create table tblAvailabilityAll (
:    PayrollNo int not null,
:    AvailabilityCode char(1) not null)
: go
: create clustered index idxCodeValue on tblAvailabilityCodes
(CodeValueChar)
: go
: insert tblAvailabilityCodes (CodeValueChar, CodeValueNum) values ('A',
'1')
: insert tblAvailabilityCodes (CodeValueChar, CodeValueNum) values ('N',
'2')
: insert tblAvailabilityCodes (CodeValueChar, CodeValueNum) values ('S',
'3')
: insert tblAvailabilityCodes (CodeValueChar, CodeValueNum) values ('H',
'4')
: go
: create proc spU_tblAvailabilityAll (
:    @PayrollNo int )  -- Same assumption that PayrollNo is integer field
: as
:    declare @RetVal int, @error int
:    begin tran
:    update a set AvailabilityCode = c.CodeValueNum
:       from tblAvailabilityAll a (nolock)
:       inner join tblAvailabilityCodes c (nolock)
:          on a.AvailabilityCode = c.CodeValueChar
:       where a.PayrollNo = @PayrollNo
:    select @RetVal=@@rowcount, @error=@@error
:    if @error != 0 begin
:    raiserror ('Failed to update!', 15, 1)
:    rollback tran
:    return (1)
:    end
:    commit tran
:    select RowsAffected = @RetVal
:    return (0)
: go
:
: Besides, Francisco's version will not compile...sorry :)
:
: ______________________________________________________
: Robert Djabarov
: Certified MS SQL Server DBA
: Certified MS SQL Server Programmer
: Certified MS VB Programmer
: ? (210) 913-3148 - phone
: ( (210) 753-3148 - pager
:
:  -----Original Message-----
: From: Francisco H Tapia [mailto:my.lists at verizon.net]
: Sent: Wednesday, February 26, 2003 11:18 AM
: To: dba-sqlserver at databaseadvisors.com
: Subject: Re: [dba-SQLServer]More SQL Server 7 Stored Procedure help if
possible
:
: : Anyone have any idea how to do this within one Stored Procedure (or is
it
: possible in a single Stored Procedure.....???
: Yes ;o)
:
: : I would be grateful fr any sample code etc...
: awright, but only cuz you'd be grateful ;o), [Wednesday... and I need my
SOF
: (soldier of fortune II) kick.]
: I'm going to ASSuME that the payroll is the identity key for this table,
or
: the identifying key for this table...
:
: CREATE PROCEDURE stp_UpdateAvailability (@PayrollNo as Int) AS
: UPDATE tblAvailabilityAll
:     SET Availability Code = CASE AvailabilityCode
:                                             WHEN A THEN 1
:                                             WHEN N THEN 2
:                                             WHEN S THEN 3
:                                             WHEN H THEN 4
:                                         ELSE AvailabilityCode --othewise
: just leave as is.
: FROM tblAvailabilityAll
: Where PayrollNo = @PayrollNo AND IsNumeric(AvailabilityCode) = 0
:
: You mentioned that you are a novice.... and perhaps you know this.. but
I'll
: say it anyway...
: **NEVER RUN TEST PROCEDURES ON A PRODUCTION SERVER!!!, ALWAYS USE A TEST
: SERVER**
: the license for a developer copy of Sql is about $500 so you can install
SQL
: Server on your desktop, this will help you out a lot in that you can
: practice your backup/restore procedures and double-check your backups.
: -Francisco
: http://rcm.netfirms.com
: ----- Original Message -----
: From: <paul.hartland at fsmail.net>
: To: <dba-SQLServer at databaseadvisors.com>; <accessd at databaseadvisors.com>;
: <dba-vb at databaseadvisors.com>
: Sent: Wednesday, February 26, 2003 8:47 AM
: Subject: [dba-SQLServer]More SQL Server 7 Stored Procedure help if
possible
:
:
: : To all,
: :
: : If you read my first email regarding Stored Procedure's you will know
that
: I am quite a novice at the moment.  I have a table called
tblAvailabilityAll
: which contains fields PayrollNo and an AvailabilityCode.  The Availability
: codes are A,N,S,H and when an employee leaves, in the first instance we
need
: to update A,N,S,H to 1,2,3,4 accordingly.
: :
: : Anyone have any idea how to do this within one Stored Procedure (or is
it
: possible in a single Stored Procedure.....???
: :
: : I would be grateful fr any sample code etc...
: :
: : Thanks in advance
: :
: : Paul Hartland
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com






More information about the dba-SQLServer mailing list