Using a procedure with a lookup variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a procedure with a lookup variable

Author

Topic

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-19 : 18:28:35

I need to add a lookup variable with the following dbo.roundTime procedure, which is working find by itself:

ALTER function [dbo].[RoundTime] (@Time datetime, @RoundTo float)
returns datetime
as
begin
declare @RoundedTime smalldatetime
declare @Multiplier float
 
set @Multiplier= 24.0/@RoundTo
 
set @RoundedTime= ROUND(cast(cast(convert(varchar,@Time,114) as datetime) as float) * @Multiplier,0)/@Multiplier
return @RoundedTime
end

But I need for the @RoundTo float variable to be looked up from the dbo.hospital table to find the column called RoundUP WHERE the Hospital_ID = the Hospital_ID in the dbo.patient table.

How would I include this lookup into this procedure?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-19 : 18:30:22

Could you show us a data example to make your business rule more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-19 : 20:08:01

I have two tables that I am interested in; dbo.patient and dbo.hospital. For each record in dbo.patient, there will be a total_test_time that needs to be rounded to the nearest hour or half hour (depending on other factors based on how a hospital likes to round the total time).

So I have a field in dbo.patient that will receive the rounded time calculation called round_time_tech.

Up to this point, it all works fine if I use a constant for the variable @RoundTo. But what I want to do is to automatically lookup in the table dbo.hospital this variable value from the field called RoundUP (which will be .5 or 1) WHERE hospital_id = hospital_id from both tables.

So the question is how do I perform a lookup into the hospital table to find this value to place into the @RoundTo variable?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 00:45:04

how are two tables related? whats the common field among them? you join the tables based on common fields to lookup related value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-20 : 01:52:26

Yes, both tables are tied together with a field named hospital_id (they both have the same named field).

There can be many patients to one hospital.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 09:37:22

then join based on that related field and take lookup value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-20 : 09:51:54

Visakh,

Thanks for responding. I understand that I need to join with the hospital_ids, but my problem is that I don't understand how to add that to this function.

That is, how do I set that up using the function I that I showed?

Any help would be GREATLY appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 09:53:56

how do you currently call this function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-21 : 17:10:19

I call this function from the dbo.patient table's column called: Round_time_tech which has this function call dbo.RoundTime(MyDateTime,1)

(of course it is the constant "1" that I want to be able to remove from this call out and have the procedure itself lookup that value from dbo.hospital)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 23:16:28

can you show the full query with function call?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-22 : 00:12:43

I am not sure what you are asking - I am wanting to use the procedure I listed in this first part of the post but to add a lookup for the @Roundto Float variable. I am not sure how to write this the lookup part and add it to the procedure.

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 00:17:29

it will be like


SELECT othercolumns...,
[dbo].[RoundTime](t.datetimecol,l.reqdcol)
FROM YourTable t
JOIN lookuptable l
ON t.linkingcol=l.linkingcol
...

this is only thing i can suggest based on info provided

linkingcol is column by which two tables are related

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-04-22 : 07:49:44

Visakh,

Thank you for responding. I can see how you are joining the two tables together to do the "look up", but I am not clear on how I would call this.

I understand how to call my one function - dbo.RoundTime(column1, column2) in my patient table, but I am not yet "seeing" what to do with this. Is this a separate function that I should create or do I add it show how to my dbo.RounTime function? I am not sure how to combined these two to get the returning result.

I am sorry, I am still learning much about SQL but have learned a lot with help from this forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:39:18

nope. if you have already created [dbo].[RoundTime] function then this is how you call it. if not, create and then use this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources