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.
| 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. |
 |
|
|
|
|
|
UDF as computed column in temporary table in proc