[dba-SQLServer] Stored Procedure Help Required

If Availability Type is stored somewhere in the system in the form of, let's say, table (tblAvailabilityTypes) structured as Type int, Description varchar(...), etc., then you can convert your VALUES clause into SELECT:

...
SELECT @PayrollNo_2, @JobDate_3, @AvailCode_4, Type
   From tblAvailabilityTypes

This way you don't even have to pass your @Type_5 as a parameter.


Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of paul.hartland at fsmail.net
Sent: Thursday, February 26, 2004 9:32 AM
To: dba-sqlserver
Subject: [dba-SQLServer] Stored Procedure Help Required

To all,
 
I'm ok with basic stored procedure's, however I want to try something just a little more advanced.  I have the following SP at the moment:
 
CREATE PROCEDURE [genesis_insert_AvailabilityAll]
            (@PayrollNo_2   [nvarchar](35),
             @JobDate_3     [datetime],
             @AvailCode_4   [nvarchar](2),
             @Type_5          [int])
 
AS 
 
SET NOCOUNT ON
 
INSERT INTO [GenesisTest].[dbo].[tblAvailabilityAll] 
             (          [PayrollNo],
                        [JobDate],
                        [AvailabilityCode],
                        [Type]) 
 
VALUES 
            ( @PayrollNo_2,
              @JobDate_3,
              @AvailCode_4,
              @Type_5)
 
The above SP inserts an availability record into tblAvailabilityAll, however the employee has to be given three types of availability 0, 1, 2 I have a Visual Basic FE and have a loop which passes the PayrollNo, JobDate, AvailCode and Type.  As Type is the only value that changes, is there a way I could just pass the SP PayrollNo, JobDate and Availcode and have the SP increment the type ?
 
Thanks in advance for any help received.
 
Paul Hartland
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com