CODEWITHSUNDEEP
sqlssmssql-function
Tim
Tim

Reputation: 2911

Cannot find user-defined function in function calling stored procedure

I am new to SQL, so I am probably making a very simple mistake, and am hoping that someone can point out what it is.

I have the following existing stored procedure that generates a new primary key (I know there is an automatic way to do this, but we want to handle generation on our own):

ALTER   PROCEDURE [dbo].[newid] @tablename AS CHAR(10), @fieldname AS CHAR(15) = '', @ioffset AS INT, @theID as int = 0 output 
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @value AS INT
 IF @ioffset < 1 SET @ioffset = 1
 IF LEN(@fieldname) > 0
  BEGIN
    UPDATE id SET @value = CONVERT(INT,cvalue)+@ioffset-1, cvalue = CONVERT(CHAR,(CONVERT(INT,cvalue)+@ioffset)) WHERE UPPER(ctablename) = UPPER(@tablename) AND UPPER(cfieldname) = UPPER(@fieldname)
   SELECT @value
  END
 ELSE
  BEGIN
   UPDATE id SET @value = CONVERT(INT,cvalue)+@ioffset-1, cvalue = CONVERT(CHAR,(CONVERT(INT,cvalue)+@ioffset)) WHERE UPPER(ctablename) = UPPER(@tablename)
   SELECT @value 
  End
 SET NOCOUNT OFF
set @theID = @value
END

I need to call this from a select statement, so I had to make a shell function that would call it. I created the following scalar-valued function:

ALTER FUNCTION [dbo].[GetNextID]
(   
    @tablename AS CHAR(10),
    @fieldname AS CHAR(15) = '',
    @ioffset AS INT 
)
RETURNS int 
AS
    BEGIN 
        RETURN dbo.newid(@tablename, @fieldname, @ioffset)
    END

I am now trying to test this, using the following:

DECLARE @ret int
EXEC @ret = dbo.GetNextID 'Client','iclientid',1
PRINT @ret

I get the error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.newid", or the name is ambiguous.

As I said, I am new to SQL and I am sure that there is something very simple that I am missing.

SOLUTION

Since you cannot access a Stored Procedure from a Function, I decided to argue the case for allowing auto-increment. Since this was a one time conversion to an in-house database, setting up the table to allow it, and then changing it after the data has been transferred seemed to be the best way to handle it.

Upvotes: 1

Views: 1347

Answers (2)

Gatej Alexandru
Gatej Alexandru

Reputation: 92

You can't call stored procedure in functions.

Upvotes: 1

user2989408
user2989408

Reputation: 3137

Try this for executing the function

DECLARE @ret int
SET @ret = (SELECT dbo.GetNextID ('Client','iclientid',1))
PRINT @ret

Upvotes: 0

Related Questions