Solved

Multi-tasking Stored Procedure

Posted on 2002-04-23
11
326 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:jrmn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6962530
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
 
LVL 4

Author Comment

by:jrmn
ID: 6965000
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6965027
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Author Comment

by:jrmn
ID: 6965076
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
 
LVL 4

Author Comment

by:jrmn
ID: 6965082
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6965090
DO this:
SELECT  
  CAST( T.STAFFKEY as varchar(10)) + ' ' + @Date,

Cheers
0
 
LVL 4

Author Comment

by:jrmn
ID: 6965123
Hi angelIII,

How about the DATEENTERED field containing only DATE not DATETIME...is this possible?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6965156
The value is datetime, even if the time part is 0:00:00 all the time. Shouldn't make any difference?!
CHeers
0
 
LVL 4

Author Comment

by:jrmn
ID: 6968035
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 180 total points
ID: 6968046
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
 
LVL 4

Author Comment

by:jrmn
ID: 6968094
Thanks angelIII your answers and comments helped me a lot.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question