Hi Guys,
I have an error that does not make sense and am hoping someone can tell me what I doing wrong. I'm attempting to call a UDF from another UDF and I receive an error say that Server can't find stored procedure udf_NextLetter. The errors occur at the point when I am trying to call this UDF. The UDF does exist in my database. I can clearly see it from the database explorer. Any assistance you can provide is appreciated. Here's the function code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
IF OBJECT_ID (N'udf_UpdatePrefix', N'FN') IS NOT NULL
DROP FUNCTION udf_UpdatePrefix;
GO
CREATE FUNCTION udf_UpdatePrefix(@CurrentPrefix char(4))
RETURNS char(4)
BEGIN
-- Declare the return variable here
DECLARE @NewPrefix char(4)
DECLARE @LastChar char(4)
DECLARE @NextChar char(4)
-- Add the T-SQL statements to compute the return value here
If LEN(LTRIM(RTRIM(@CurrentPrefix))) = 1
SET @NewPrefix = @CurrentPrefix +'A'
ELSE IF LEN(LTRIM(RTRIM(@CurrentPrefix))) = 2 OR LEN(LTRIM(RTRIM(@CurrentPrefix))) = 3
BEGIN
SET @LastChar = RIGHT(@CurrentPrefix, 1)
IF @LastChar = 'Z'
SET @NewPrefix = @CurrentPrefix
ELSE
BEGIN
EXEC @NextChar = udf_NextLetter(@LastChar)
SET @CurrentPrefix = SUBSTRING(@CurrentPrefix, 1, Len(@CurrentPrefix) - 1) + @NextChar
END
END
ELSE IF LEN(LTRIM(RTRIM(@CurrentPrefix))) = 4
SET @LastChar = RIGHT(@CurrentPrefix, 1)
IF @LastChar = 'Z'
SET @NewPrefix = '****'
ELSE
BEGIN
EXEC @NextChar = udf_NextLetter(@LastChar)
SET @CurrentPrefix = SUBSTRING(@CurrentPrefix, 1, Len(@CurrentPrefix) - 1) + @NextChar
END
-- Return the result of the function
RETURN LTRIM(RTRIM(@NewPrefix))
END
GO
You can't use EXEC MySproc in a Function, so sounds like the error you are getting is misleading, but that's probably what it is referring to.
If udf_NextLetter is a function (seems likely from the name 
) you need to use SELECT and not EXECUTE to "run" it. You also need to specify the schema prefix too (presumably dbo.udf_NextLetter(...)
Thanks for the reply. Use of EXEC was based on information found on an internet posting. I'm still getting used to the mechanics of coding server side functions and procedures. Yes udf_Nextletter is a function. I employ the use of prefixes when I name database objects. Could you please provide an example of the proper method to call this UDF? It seems that my efforts to find the correct method for doing so didn't work out, but once I see an example of the correct method it will make sense to me. Much appreciated.
I have modified the code according to your instructions using select as well as adding the prefix and the editor still shows there is an error. Red lime under both function calls. Function compiles without error but when I hover over the position of the error it says cannot find column dbo or the user defined function or the name is ambiguous. Any help you can provide is appreciated.
It would help if you posted the actual code you have, otherwise I'm just guessing from your description.
If it compiles without error does it run? or do you then get an error? If so post the error message.
I haven't looked at your code carefully, so the issue may be related to something elsewhere in the code, but to answer your basic question
EXEC @NextChar = udf_NextLetter(@LastChar)
should probably me
SELECT @NextChar = dbo.udf_NextLetter(@LastChar)
Yes those are the changes I made.
Here's the modified code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
IF OBJECT_ID (N'udf_UpdatePrefix', N'FN') IS NOT NULL
DROP FUNCTION udf_UpdatePrefix;
GO
CREATE FUNCTION udf_UpdatePrefix(@CurrentPrefix char(4))
RETURNS char(4)
BEGIN
-- Declare the return variable here
DECLARE @NewPrefix char(4)
DECLARE @LastChar char(4)
DECLARE @NextChar char(4)
-- Add the T-SQL statements to compute the return value here
If LEN(LTRIM(RTRIM(@CurrentPrefix))) = 1
SET @NewPrefix = @CurrentPrefix +'A'
ELSE IF LEN(LTRIM(RTRIM(@CurrentPrefix))) = 2 OR LEN(LTRIM(RTRIM(@CurrentPrefix))) = 3
BEGIN
SET @LastChar = RIGHT(@CurrentPrefix, 1)
IF @LastChar = 'Z'
SET @NewPrefix = @CurrentPrefix
ELSE
BEGIN
SELECT @NextChar = [dbo].udf_NextLetter
SET @CurrentPrefix = SUBSTRING(@CurrentPrefix, 1, Len(@CurrentPrefix) - 1) + @NextChar
END
END
ELSE IF LEN(LTRIM(RTRIM(@CurrentPrefix))) = 4
SET @LastChar = RIGHT(@CurrentPrefix, 1)
IF @LastChar = 'Z'
SET @NewPrefix = '****'
ELSE
BEGIN
SELECT @NextChar = [dbo].udf_NextLetter
SET @CurrentPrefix = SUBSTRING(@CurrentPrefix, 1, Len(@CurrentPrefix) - 1) + @NextChar
END
-- Return the result of the function
RETURN LTRIM(RTRIM(@NewPrefix))
END
GO
The function compiles but the editor says there is still a problem with the lines which execute the UDF. Cannot find column dbo or function dbo.udf_NextLetter or the name is ambiguous.
As I said before: Does it execute? If not, what error do you get when you execute it?
Your code has this:
SELECT @NextChar = [dbo].udf_NextLetter
what I recommended was
SELECT @NextChar = dbo.udf_NextLetter(@LastChar)
you need to supplier whatever parameters udf_NextLetter requires. (Square brackets around [dbo] are optional, so what you have is fine)