Help with understanding scalar-valued function

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

Help with understanding scalar-valued function

Author

Topic

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-03-22 : 13:27:41

I have a scalar-valued function that works just fine but I don't understand where it is getting the value of its variable from (I got help with this several months ago from this forum). It is:

ALTER FUNCTION [dbo].[Count_Open_Tech](@ClientRegion_ID INT)
	
RETURNS INT 

AS BEGIN
    DECLARE @countOpenTech INT

    SELECT @countOpenTech = COUNT(*)
    FROM dbo.IOM_Tests_Patient
    WHERE ClientRegion_ID = @ClientRegion_ID and  Tech_Sign_Off=0 and cancelled=0 and deleted=0

    RETURN @countOpenTech
END

I would display the value in another table column call countOpenTech with the following computed specification:

(case when [dbo].[Count_Open_Tech]([ClientRegion_ID])<(1) then (0) else [dbo].[Count_Open_Tech]([ClientRegion_ID]) end)

It is used in a website dashboard to show the techs how many patient records are NOT signed off from the table dbo.IOM_Tests_Patient. The techs can belong to different clients and regions ( thus ClientRegion_ID). I am not clear in the above code where the "@ClientRegion_ID is getting its value?

What I want to do is to create another function just like this but based on the tech_ID.

I have tried the following which I was successful in creating it but I was unable in placing it into a computed field with Management Studio next to the other clie:

Create FUNCTION [dbo].[Count_Open_Tech_ID](@tech1_ID INT)
	
RETURNS INT 

AS BEGIN
    DECLARE @countOpenTechID INT

    SELECT @countOpenTechID = COUNT(*)
    FROM dbo.IOM_Tests_Patient
    WHERE tech1_ID = @tech1_ID and  Tech_Sign_Off=0 and cancelled=0 and deleted=0

    RETURN @countOpenTechID
END

I would get a column error using:

(case when [dbo].[Count_Open_Tech_ID]([tech1_ID])<(1) then (0) else [dbo].[Count_Open_Tech_ID]([tech1_ID]) end)

My biggest confusion is trying to figure out how is this function suppose to pick up the variable of the tech's tech1_id?

It does work correctly with the @ClientRegion_ID (the first function) but I don't get how "it" knows what the clientregion_id actually is?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 13:31:09

it works once for each value of clientregion_id in your resultset and returns the count as per logic written inside

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

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-03-24 : 08:41:50

Visakh,

Thank you for responding. I now see that when my application runs the table "dbo.IOM_Tests_Patient", it will count the records within that filtered view and it does do what it is suppose to.

But I still can't figure out where the variable "@ClientRegion_ID" is getting assigned it's value. It seems like I can put any name to this variable and it is working the same, which makes sense on one hand, but only if I knew where that variable is getting its value.

So for example, if my filtered view of the table dbo.IOM_Tests_Patient is based on ClientRegion_ID=105, where does the variable @ClientRegion_ID get assigned that variable?

Then when I create the exact same function based on tech1_ID, it always comes up with 0 - doesn't find any.

I have looked at several resources but just not getting it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 08:46:52

The value for @ClientRegion_ID is provided by the call.
dbo.IOM_Tests_Patient(105)
or
dbo.IOM_Tests_Patient(column_that_holds_the_value_105)

No, you're never too old to Yak'n'Roll if you're too young to die.

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-03-24 : 10:59:54

OOOOH, now I get it!!!

I was so focused on the query itself within the function, I was not paying attention to the "call", which is in a computed column (and I had just copied from the initial help provided months ago).

I just could not figure out, based on the query, where it was getting the variable value. . . . now I see it is from the call itself.

Now that I really look at the "whole" picture, I can see why this can be a very powerful tool! I can reuse the function in several different ways.

Thanks for helping to see this!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 11:03:14

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources