?
Solved

Multi-tasking Stored Procedure

Posted on 2002-04-23
11
Medium Priority
?
327 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 720 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

771 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