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)ASBEGIN --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 #tempENDThis is an example of the function:CREATE FUNCTION [dbo].[FunctionName]( -- Add the parameters for the function here @Type nvarchar(4), @Quantity int)RETURNS MoneyASBEGIN RETURN (cast((SELECT ([Value] * cast(@Quantity as int)) FROM tblTable WHERE [ID] = @Type) as Money)) ENDThe 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 13Cannot 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. |
 |
|
|
|
|
|
|