Calling function from a stored procedure

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calling function from a stored procedure

Author

Topic

marwest98
Starting Member

9 Posts

Posted - 2009-04-28 : 10:06:54

I am creating a Stored procedure that will be calling a function. I am currently testing before I actually create the stored procedure, and I am fairly new to SQLServer 2005.

Here is what will be my stored procedure
declare

@gl_cmp varchar(2),
@gl_fplant varchar(50),
@gl_tplant varchar(50),
@gl_weekbegin datetime,
@gl_weekending datetime,

@tplant varchar(2),
@fplant varchar(2),
@pm_tran_bscst float

set @gl_cmp = 50
set @gl_fplant = 'Krispak Plant'
set @gl_tplant = 'Krispak Plant'
set @gl_weekbegin = '03/15/2009'
set @gl_weekending = '04/11/2009'

IF (@gl_fplant = 'MNS Plant')
BEGIN
SELECT @fplant = '80'
END

if (@gl_fplant = 'Krispak Plant')
BEGIN
Select @fplant = '82'
end

IF (@gl_tplant = 'MNS Plant')
BEGIN
SELECT @tplant = '80'
END

if (@gl_tplant = 'Krispak Plant')
BEGIN
Select @tplant = '82'
end

Begin

select a.gl_cmp_key,
a.pm_shop_key,
a.sf_plant_key,
a.pm_shop_reldt,
case when b.pack_cost is null then 'Missing' else b.pack_cost end as pack_cost,
-- case when b.handling_fees is null then 'Missing' else b.handling_fees end as handling_fees,
case when b.handling_fees is null then
(dbo.GetTranRecord(a.gl_cmp_key, a.pm_shop_key, a.sf_plant_key)) end as pm_tran_bscst,
case when b.addl_fees is null then 'Missing' else b.addl_fees end as addl_fees
-- into dbo.temp_pack_tbl
from pm_shop_tbl a
Inner Join pm_shop_ext b
on b.gl_cmp_key = a.gl_cmp_key
and b.sf_plant_key = a.sf_plant_key
and b.pm_shop_key = a.pm_shop_key
where a.gl_cmp_key = @gl_cmp
and (a.sf_plant_key >= @fplant
and a.sf_plant_key <= @tplant)
and a.pm_shop_reldt >= @gl_weekbegin
and a.pm_shop_reldt <= @gl_weekending
and (b.pack_cost is null
or b.handling_fees is null)
order by a.sf_plant_key

end
Here is the function call:

USE [Adage]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: M.Westerman
-- Create date: 04/28/2009
-- Description: User defined Function to return data to voucher report
-- =============================================
Alter FUNCTION [dbo].[GetTranRecord]
(
-- Add the parameters for the function here
@gl_cmp_key varchar(2), @sf_plant_key varchar(4), @pm_shop_key varchar(10)
)
RETURNS float
AS
BEGIN
-- Declare the return variable here
DECLARE @pm_tran_bscst as float

-- Add the T-SQL statements to compute the return value here
Select @pm_tran_bscst = pm_tran_bscst
from pm_tran_tbl
Where gl_cmp_key = @gl_cmp_key
and sf_plant_key = @sf_plant_key
and pm_shop_key = @pm_shop_key
and (sf_rsrce_key like 'MNSHN%')

-- Return the result of the function
RETURN @pm_tran_bscst

END
GO

I need to return a field called pm_tran_bscst into the stored procedure without overwriting the handling fees field ( create a new field). Unfortunately, I cannot figure out how to get it to come back. Right now it does not seem to be returning anything, but I'm not sure. Right now it is replacing the handling_fees title with pm_tran_bscst. If I run the sql from the function the select in the function works correctly. Here are the results of the above query with the call to the function:

gl_cmp_key pm_shop_key sf_plant_key pm_shop_reldt pack_cost pm_tran_bscst addl_fees
---------- ----------- ------------ ----------------------- ------------------------------ ---------------------- ------------------------------
50 20210 82 2009-04-07 00:00:00.000 39907 NULL Missing
50 20211 82 2009-04-07 00:00:00.000 39907 NULL Missing
50 20212 82 2009-04-07 00:00:00.000 39907 NULL Missing
50 20213 82 2009-04-07 00:00:00.000 39907 NULL Missing
50 20214 82 2009-04-07 00:00:00.000 39907 NULL Missing
50 20215 82 2009-04-07 00:00:00.000 39907 NULL Missing
50 20216 82 2009-04-06 00:00:00.000 39907 NULL Missing
50 20217 82 2009-04-07 00:00:00.000 39907 NULL Missing

Mary Westerman

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-28 : 11:27:15

The case statement you call the function in doesn't have an ELSE statement. Therefore a NULL will be generated if the WHEN condition is not met.

Maybe this?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources