[dba-SQLServer]A2K-SQL Stored Procedures

What we are doing is switching systems that record incoming signals from
alarms from the B32 environment to a new receiver.  We will be doing testing
in batches with the phone company, taking blocks of our customers by CS_no
(which is the little box that hangs on your wall for our security systems).
The phone company will call, we will change the "special" field for the
specified block of customers to the modified "newspecial" field.  I have
another routine to reverse if all goes wrong, but this is the stored
procedure, with the place that needs to be changed clearly marked.

I would like my user to be able to run this from Access specifying his block
as needed so I don't have to sit with him at 2:00 in the morning just to
change a piece of code.  
Here is the sproc:

CREATE procedure gps_SpecialSystem as

SET CONCAT_NULL_YIELDS_NULL OFF

truncate table gps_specialsystemdata
insert gps_specialsystemdata(
          system_no,
          cs_no,
          special,
          newspecial,
          length)
   
SELECT system_no, 
               cs_no, 
               special,
               special+'RSI' ,
               len(special) as length
FROM system

WHERE (
/********************************************************************/
/***THIS IS THE CS NUMBER GROUPING TO CHANGE***/
(cs_no LIKE '21%') 
/********************************************************************/
and
(len(special) in (1,2,3) or
special is null))

update  gps_specialsystemdata
set exception=1
where special <> 'SUB'

update system
set system.special=gps_specialsystemdata.newspecial

FROM gps_SpecialSystemData
where 
    gps_SpecialSystemData.system_no = system.system_no AND 
    gps_SpecialSystemData.cs_no = system.cs_no






-----Original Message-----
From: Susan Geller [mailto:sgeller at cce.umn.edu]
Sent: Wednesday, July 30, 2003 8:58 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]A2K-SQL Stored Procedures


Karen,

A few ways to do this depending on what you want to do with the sproc
when it gets the parameter.  Does it just do something on it's own or
does it return a recordset and if so what do you want to do with the
recordset?

--Susan


-----Original Message-----
From: Nicholson, Karen [mailto:knicholson at gpsx.net] 
Sent: Tuesday, July 29, 2003 4:59 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]A2K-SQL Stored Procedures


Is it possible to build a front-end in A2K to execute stored procedures
in SQL version 7?  I need to allow the user to enter in a parameter that
is in the stored procedure, if it is possible to create a parameter in
SQL that is determined by a combo box in Access.  ???? Thanks. 
_______________________________________________
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