Multi-tasking Stored Procedure

Hi experts,

Can anybody tell me if what I want possible?

I want a stored procedure that will do the following:

1. The store procedure will have @DOCTORKEY (int) as INPUT and STAFFSTAMP (char(50)) as OUTPUT

2. Check if a table (say TEMPTABLE) exist...if not then it will create that particular table.
   -- structure will be TEMPTABLEKEY (char(50)), STAFFKEY (int), MEMBERNAME (varchar), ADDRESS(varchar) and DATEENTERED(Datetime)
   -- also will create an index for TEMPTABLEKEY field Ascending order.

3. If TEMPTABLE exists, then using DATEENTERED field, will then check if there are/is entries which are(is) two (2) days old from CURRENT DATE (system date). If there are, then these entries will be deleted.


4. After the tasks 2 and 3 above, it will then execute this query:

   SELECT M.DOCTORKEY, M.NAME, M.ADDRESS,
          D.DOCTORNAME,
          T.STAFFKEY
   FROM  SCHEDULE S
   JOIN MEMBER M ON M.MEMBERKEY = S.MEMBERKEY
   JOIN DOCTOR D ON D.DOCTORKEY = M.DOCTORKEY
   JOIN STAFF T ON T.STAFFKEY = S.STAFFKEY
   WHERE DOCTORKEY= @DOCTORKEY
   
   
5. If the query above returns data, it will then be SAVE on the TEMPTABLE declared above:

   TEMPTABLE FIELDS:
   =================
   
   TEMPTABLEKEY = T.STAFFKEY +(space)+ CURRENTDATE(datetime is needed)
   STAFFKEY = T.STAFFKEY
   MEMBERNAME = M.NAME
   ADDRESS = M.ADDRESS
   DATEENTERED = CURRENT DATE (System Date)
   
6. After saving the queried data, the TEMPTABLEKEY value will then be pass to @STAFFSTAMP as OUTPUT value.
7. If Task #5 doesn't return any data at all, @STAFFSTAMP will then be equal to 0 +(space)+ CURRENT DATE (System Date)


Thanks In Advance,
jrmn
LVL 4
jrmnAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You should look into the possible values for the convert function for the style:

SET @Date = CONVERT(varchar(25), GetDate() , 131 )

unfortunately, there is no style 100% equal to your requirements, but you might by happy with the above.
You can of course use substring() and/or replace() to change the layout of @Date

CHeers

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
implementation is quite straightforward:

-- 1. The store procedure will have @DOCTORKEY (int) as INPUT and STAFFSTAMP (char(50)) as OUTPUT

CREATE PROC MultiTaskProc
@DOCTORKEY int,
@StaffStamp varchar(50) OUTPUT
AS
DECLARE @Date varchar
SET NOCOUNT ON

SET @Date = CONVERT(varchar(25), GetDate() , 120 )

-- 2. Check if a table (say TEMPTABLE) exist...
IF NOT EXIST (SELECT * FROM sysobjects where name = 'TEMPTABLE' and xtype = 'U' ) THEN
BEGIN
-- if not then it will create that particular table.
EXEC ('CREATE TABLE TEMPTABLE ( TEMPTABLEKEY char(50), STAFFKEY int, MEMBERNAME varchar(50), ADDRESS varchar(50)
, DATEENTERED Datetime) ')

 -- also will create an index for TEMPTABLEKEY field Ascending order.
  EXEC ('CREATE INDEX TEMPTABLE_IDX ON TEMPTABLE ( TEMPTABLEKEY )')
END

-- 3. If TEMPTABLE exists, then using DATEENTERED field,
-- will then check if there are/is entries which
-- are(is) two (2) days old from CURRENT DATE (system
-- date). If there are, then these entries will be deleted.
DELETE TEMPTABLE
WHERE datediff ( day, DATEENTERED, getdate()) > 2)

-- 4. After the tasks 2 and 3 above, it will
-- then execute this query:
-- 5. If the query above returns data, it will
-- then be SAVE on the TEMPTABLE declared above:

  INSERT INTO TEMPTABLE
  (
  TEMPTABLEKEY,
  STAFFKEY,
  MEMBERNAME,
  ADDRESS,
  DATEENTERED)
  SELECT  
     T.STAFFKEY + ' ' + @Date,
     T.STAFFKEY,
     M.NAME,
     M.ADDRESS,
     GetDate()
  FROM  SCHEDULE S
  JOIN MEMBER M ON M.MEMBERKEY = S.MEMBERKEY
  JOIN DOCTOR D ON D.DOCTORKEY = M.DOCTORKEY
  JOIN STAFF T ON T.STAFFKEY = S.STAFFKEY
  WHERE DOCTORKEY= @DOCTORKEY
 
-- 6. After saving the queried data,
-- the TEMPTABLEKEY value will then be
--pass to @STAFFSTAMP as OUTPUT value.
-- 7. If Task #5 doesn't return any data at
-- all, @STAFFSTAMP will then be equal
-- to 0 +(space)+ CURRENT DATE (System Date)

  SELECT  
     @STAFFSTAMP = COALESCE(T.STAFFKEY ,0) + ' ' + @Date
  FROM  SCHEDULE S
  JOIN MEMBER M ON M.MEMBERKEY = S.MEMBERKEY
  JOIN DOCTOR D ON D.DOCTORKEY = M.DOCTORKEY
  JOIN STAFF T ON T.STAFFKEY = S.STAFFKEY
  WHERE DOCTORKEY= @DOCTORKEY
 
GO


Cheers
0
 
jrmnAuthor Commented:
Hi angelIII,

Thanks for the reply.
Just few more questions:

I think we have a little problem on this area here:

INSERT INTO TEMPTABLE
 (
 TEMPTABLEKEY,
 STAFFKEY,
 MEMBERNAME,
 ADDRESS,
 DATEENTERED)
 SELECT  
    T.STAFFKEY + ' ' + @Date,
    T.STAFFKEY,
    M.NAME,
    M.ADDRESS,
    GetDate()
 FROM  SCHEDULE S
 JOIN MEMBER M ON M.MEMBERKEY = S.MEMBERKEY
 JOIN DOCTOR D ON D.DOCTORKEY = M.DOCTORKEY
 JOIN STAFF T ON T.STAFFKEY = S.STAFFKEY
 WHERE DOCTORKEY= @DOCTORKEY


When I viewed the table entries after running the stored procedure,
It seems that only the T.STAFFKEY was/were saved on the TEMPTABLEKEY.
The ' '+ @Date portion wasn't. So the column says "1" when it should be "1 04/24/2002".

Another question how about it I pass as stored procedure input the STAFFKEY and use it to fill
the TEMPTABLE's TEMPTABLEKEY...is this possible?
It will be sort of like TEMPTABLE.TEMPTABLEKEY = @STAFFKEY+' '+@Date...is this possible?


It is possible also if instead of like this:

 SELECT  
    @STAFFSTAMP = COALESCE(T.STAFFKEY ,0) + ' ' + @Date
 FROM  SCHEDULE S
 JOIN MEMBER M ON M.MEMBERKEY = S.MEMBERKEY
 JOIN DOCTOR D ON D.DOCTORKEY = M.DOCTORKEY
 JOIN STAFF T ON T.STAFFKEY = S.STAFFKEY
 WHERE DOCTORKEY= @DOCTORKEY

============================
I will pass the @STAFFKEY input parameter...sort of like this?

 SELECT  
    @STAFFSTAMP = COALESCE(@STAFFKEY,0) + ' ' + @DATE
 FROM  SCHEDULE S
 JOIN MEMBER M ON M.MEMBERKEY = S.MEMBERKEY
 JOIN DOCTOR D ON D.DOCTORKEY = M.DOCTORKEY
 JOIN STAFF T ON T.STAFFKEY = S.STAFFKEY
 WHERE DOCTORKEY= @DOCTORKEY


TIA,
jrmn
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
regarding the first part, is missed this:
DECLARE @Date varchar -> is equal to DECLARE @Date varchar(1)  ooops
thus, change to:
DECLARE @Date varchar (20)


For the second part, your suggestion seems correct (syntax)

CHeers
0
 
jrmnAuthor Commented:
Hi angelIII,

It seems we have a little problem here:

SELECT  
   T.STAFFKEY + ' ' + @Date,
   
Maybe because T.STAFFKEY is an INTEGER type while @Date is a VARCHAR.

If this is the problem, how can I convert T.STAFFKEY from INT to VARCHAR?

jrmn
0
 
jrmnAuthor Commented:
when I run the store procedure script, it proceduces:

Server: Msg 245, Level 16, State 1, Procedure MULTITASKPROC, Line 25
Syntax error converting the varchar value '2002-04-24 16:36:21' to a column of data type int.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DO this:
SELECT  
  CAST( T.STAFFKEY as varchar(10)) + ' ' + @Date,

Cheers
0
 
jrmnAuthor Commented:
Hi angelIII,

How about the DATEENTERED field containing only DATE not DATETIME...is this possible?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
The value is datetime, even if the time part is 0:00:00 all the time. Shouldn't make any difference?!
CHeers
0
 
jrmnAuthor Commented:
Hi angelIII,

Yup, I think you have a point there. Just one more thing.
Can I have the @DATE formatted instead of this :

2002-04-25 17:07:50

to this,

4/25/2002 5:07:51 PM

So that when I saved it on the TEMPTABLEKEY field, it will look like this : 1 4/25/2002 5:07:51 PM instead of 1 2002-04-25 17:07:50

Thanks,
jrmn
0
 
jrmnAuthor Commented:
Thanks angelIII your answers and comments helped me a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.