Solved

Multi-tasking Stored Procedure

Posted on 2002-04-23
11
318 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
  • 6
  • 5
11 Comments
 
LVL 142

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 142

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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 142

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 142

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 142

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Distinct values from two tables 14 20
separate column 24 21
SQL Split character from numbers 3 18
When to use an Aggregate Function. 18 38
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now