can you run a function? - SQL Server Forums

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

can you run a function?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-16 : 15:08:04

Hi I have a function that is used by a stored procedure. I can of course run the sp and I see a problem that a field is 0 and it should not be. But it's getting this data from the function.

I have trouble running thefunction in SQL Server studio. is there a way to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-16 : 15:20:26

Yes. If it's a table-valued function, then you just run a SELECT statement and pass it the parameters. If it's a scalar function, then you run EXEC and pass it the parameters. Check BOL for details.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-16 : 15:50:27

HI thanks Tara, I did the exec but it doesn't display any data, just that the exce query was successful

exec dbo._FnGetBudgetAmount '20100101', 'Mo1', 'Baked Goods'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-16 : 16:37:58

select dbo._FnGetBudgetAmount('20100101', 'Mo1', 'Baked Goods')

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-16 : 22:11:20

Thank you very much Russel.
This select returns a zero. WHich I need to debug somehow because the record is this: which shows the data in Budget amount. Yet this select and indeed the stored proc that calls it, returns a zero.

CustomerBudgetID CUSTNMBR USCATVAL ApplicablePeriod BudgetDateFrom BudgetDateTo BudgetQuantity BudgetAmount Dept
772 M01 BakedGood NULL 2010-01-01 00:00:00.000 5000-01-01 00:00:00.000 NULL 2574.29 NULL

the actual function is this:

USE [Mylibn]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[_FnGetBudgetAmount]
(
-- Add the parameters for the function here
@Year Date,
@CustNMBR varchar(100),
@USCATVAL varchar(100)
)
RETURNS float
AS
BEGIN
--
DECLARE @ResultVar float
set @ResultVar = 0
--
SELECT @ResultVar = isnull( [BudgetAmount],0)
FROM [Mylibn].[dbo].[CustomerBudget] Where (@Year between BudgetDateFrom and BudgetDateTo) and CUSTNMBR = @CustNMBR and [USCATVAL] = @USCATVAL

-- Return the result of the function
RETURN @ResultVar

END

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-16 : 22:42:21

You're passing 'Baked Goods' but the value in the table is 'BakedGood'

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-16 : 23:13:55

Many many Thanks Russel, all the best!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources