UDF as computed column in temporary table in proc

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

.NET Inside SQL Server (2005)

UDF as computed column in temporary table in proc

Author

Topic

Hosmerica
Starting Member

1 Post

Posted - 2008-06-16 : 14:49:50

I've created a stored procedure that creates and uses a temporary table with a computed column. I want the computed column to call a user-defined function(UDF) that I've created. Here's an example:

CREATE PROCEDURE [dbo].[usp_Proc]
(
@Date datetime
)
AS
BEGIN

--Drop the temp table if it still exists so reports come out accurate
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND type in (N'U'))
DROP TABLE #temp;

--Create the temp table for use in this stored procedure
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND type in (N'U'))
CREATE TABLE #temp (
[ID] INT PRIMARY KEY IDENTITY(1,1),
[Column1] NVARCHAR (30) DEFAULT ('XXXX-XXXXX'),
[Column2] INT DEFAULT (0),
[Column3] INT DEFAULT (0),
[Column4] INT DEFAULT (0),
[Column5] as ([Column2] + [Column3] + [Column4]),
[Column6] as (dbo.FunctionName('5381', [Column1]))
)

--Insert data
INSERT INTO #temp([Column1], [Column2], [Column3], [Column4])
SELECT 'String', 1, 2, 3

--Perform more calculations
<snipped...>

SELECT * FROM #temp

DROP TABLE #temp

END

This is an example of the function:

CREATE FUNCTION [dbo].[FunctionName]
(
-- Add the parameters for the function here
@Type nvarchar(4),
@Quantity int
)
RETURNS Money
AS
BEGIN

RETURN (cast((SELECT ([Value] * cast(@Quantity as int)) FROM tblTable WHERE [ID] = @Type) as Money))
END

The error message I'm getting after I've created both the stored procedure and the UDF is when calling the stored procedure. Here it is:

Msg 4121, Level 16, State 1, Procedure usp_Proc, Line 13
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FunctionName", or the name is ambiguous.

There's no way the function name is ambiguous. Is it even possible to do what I'm trying to do or am I just calling it the wrong way?

Hosmerica

bfoster
Starting Member

30 Posts

Posted - 2008-07-03 : 10:57:18

The second parameter in your function definition is @Quantity INT and the second parameter that you are trying to pass to the function is Column1 NVARCHAR(30) and the default value for that column is 'XXXX-XXXXX'. I haven't tried running your code yet, but that's what I noticed as something that might be wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources