SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Calling function from a stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marwest98
Starting Member

USA
9 Posts

Posted - 04/28/2009 :  10:06:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/28/2009 :  11:27:15  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000