[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