Prev: can dynamic SQL be stored in tables?
Next: using between to seach for date
From: Brad Pears on 2 Jul 2008 12:44
I am using SQL Server 2000 and have written a fucntion that I would like to
include in a view. The function returns a decimal value.

When I go to my view, right click and select "Add table..." and then click
on the "Functions" tab, there is nothing there to pick. What am I missing?
How do I include an SQL server function as part of a view?

Thanks, Brad


From: Brad Pears on 2 Jul 2008 12:57
Oops, got it figured out.

Still curious as to why it does not show up when I click on the "Functions"
tab when adding a new table to view BUT I was able to simply enter the
function name and associated parms into a column in designview and it
works...

Brad

"Brad Pears" <bradp(a)truenorthloghomes.com> wrote in message
news:ekoKdLG3IHA.4476(a)TK2MSFTNGP02.phx.gbl...
>I am using SQL Server 2000 and have written a fucntion that I would like to
>include in a view. The function returns a decimal value.
>
> When I go to my view, right click and select "Add table..." and then click
> on the "Functions" tab, there is nothing there to pick. What am I missing?
> How do I include an SQL server function as part of a view?
>
> Thanks, Brad
>


From: Eric Isaacs on 2 Jul 2008 12:58
In the SQL window, you can type the function name within the SELECT
clause...

SELECT
dbo.functionname(parameter1) AS FunctionValue,
tablename.fieldname
FROM
tablename
WHERE
....

Just be careful with functions in views. They can easily be abused
and used inappropriately within views. Each line of the returned SQL
executes that function. If the function has join's within it, you can
likely accomplish the same thing much more efficiently within a stored
procedure or just by doing the right join's in your view.

Also, declare your functions WITH SCHEMABINDING in SQL SERVER 2005.
http://sqltips.wordpress.com/category/schemabinding/


-Eric Isaacs
From: Brad Pears on 2 Jul 2008 13:23
Ok, thanks for that...

I also have another question... My function returns only one value. To get
this value (right now I am just testing and specifically set it) so I can
display it in my view, I need to call/execture a stored procedure that
returns the value.

So in my function, I want to call the stored procedure and pass it the two
parameters it needs to do it's job)

How do I call a stored procedure from within a function and how do I set a
declared value in my function to the returned value of the stored
procedure??

See sample code below... Help!

Brad

CREATE FUNCTION func_TotalPointLoad(@vcJobNo varchar(50),@iPCID integer)
RETURNS decimal(10,2)
AS
BEGIN

DECLARE @dCalcTotalPointLoad decimal(10,2)

-- I want to call a stored procedure called
dbo.spP_GetPointLoad_JobNo_PCID(@vcJobNo, @iPCID)
-- and then set @dCalcTotalPointLoad to it's returned value similar to the
following - but it does not work

set @dCalcTotalPointLoad =
dbo.spP_GetPointLoad_JobNo_PCID(@vcJobNo, @iPCID)

RETURN @dCalcTotalPointLoad

END




"Eric Isaacs" <eisaacs(a)gmail.com> wrote in message
news:5de90b18-5601-4d9f-941a-a12428b90100(a)a9g2000prl.googlegroups.com...
> In the SQL window, you can type the function name within the SELECT
> clause...
>
> SELECT
> dbo.functionname(parameter1) AS FunctionValue,
> tablename.fieldname
> FROM
> tablename
> WHERE
> ...
>
> Just be careful with functions in views. They can easily be abused
> and used inappropriately within views. Each line of the returned SQL
> executes that function. If the function has join's within it, you can
> likely accomplish the same thing much more efficiently within a stored
> procedure or just by doing the right join's in your view.
>
> Also, declare your functions WITH SCHEMABINDING in SQL SERVER 2005.
> http://sqltips.wordpress.com/category/schemabinding/
>
>
> -Eric Isaacs


From: Eric Isaacs on 5 Jul 2008 17:21
On Jul 2, 10:23 am, "Brad Pears" <br...(a)truenorthloghomes.com> wrote:
> Ok, thanks for that...
>
> I also have another question... My function returns only one value. To get
> this value (right now I am just testing and specifically set it) so I can
> display it in my view, I need to call/execture a stored procedure that
> returns the value.
>
> So in my function, I want to call the stored procedure and pass it the two
> parameters it needs to do it's job)
>
> How do I call a stored procedure from within a function and how do I set a
> declared value in my function to the returned value of the stored
> procedure??
>
> See sample code below... Help!
>
> Brad
>
> CREATE FUNCTION func_TotalPointLoad(@vcJobNo varchar(50),@iPCID integer)
> RETURNS decimal(10,2)
> AS
> BEGIN
>
> DECLARE @dCalcTotalPointLoad decimal(10,2)
>
> -- I want to call a stored procedure called
> dbo.spP_GetPointLoad_JobNo_PCID(@vcJobNo, @iPCID)
> -- and then set @dCalcTotalPointLoad to it's returned value similar to the
> following - but it does not work
>
> set @dCalcTotalPointLoad =
> dbo.spP_GetPointLoad_JobNo_PCID(@vcJobNo, @iPCID)
>
> RETURN @dCalcTotalPointLoad
>
> END


I would need to see the DDL for the stored procedure to tell you
exactly how to do this, but you didn't include that in your quesiton.
Using something like the following would be the way to do it, if the
TotalPointLoad was an OUTPUT parameter in the stored procedure.

EXEC dbo.spP_GetPointLoad_JobNo_PCID @vcJobNo, @iPCID,
@dCalcTotalPointLoad OUT

If the stored procedure returns a result set, you won't be able to do
it from a function, because you can't create a temporary table within
functions. You could wrap that stored procedure in another stored
procedure that returns an output parameter, however, but that's a lot
of work to get the function to call a stored procedure, just to use
the function within a view. I still think it might be easier to just
do this all from a single stored procedure.

Why do you want to create a view, function and stored procedure?

-Eric Isaacs
 |  Next  |  Last
Pages: 1 2
Prev: can dynamic SQL be stored in tables?
Next: using between to seach for date