Francisco H Tapia
my.lists at verizon.net
Wed Feb 26 12:17:32 CST 2003
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
: