the name is not a valid identifier error in function

Question

     Create FUNCTION [dbo].[fngetname]        
    (         
       @OrganisationID int=null,        
       @UserID int=null,        
       @ClientTypeID int        

    )        
    RETURNS  varchar(500)        
    BEGIN        
    DECLARE @Name VARCHAR(500)        

     DECLARE @dbName VARCHAR(500)            
    set @dbName=(select ClientDataBase from dbo.ClientType where ClientTypeID=@ClientTypeID)       

    begin        

   set @Name='select UserName from ['+ @dbName+'].dbo.Users where UserID='+convert(varchar,@UserID)  
    exec @Name   
    end        

       return @Name                
    end

function code seems to raise no errors itself, what is your problem exactly? — DrCopyPaste, Feb 25 '14 at 08:51
when executing select dbo.fngetname(1,null,9) getting error. The name 'select UserName from [GMS_Latest].dbo.Users where UserID=1' is not a valid identifier — user3350258, Feb 25 '14 at 08:58
that suggests your error does not lie in the code you are showing us, but in the code calling that function - does that database `GMS_Latest` exist are you sure of it? — DrCopyPaste, Feb 25 '14 at 09:01

score 6 · Answer 1 · edited May 23 '17 at 10:24

6

There are two issues here, first if you want to execute dynamic sql, you need to encapsulate your statement variable in parenthesises:

you need to add parenthesises to the exec @Name in your function declaration. The function gets written but cannot execute it stops at the exec

replace that exec @Name with exec (@Name)

you can easily reproduce this error by just trying two simple lines (ofc replace the table and db-name with something you have ;)):

DECLARE @statement VARCHAR(MAX) = 'SELECT * FROM [nana].dbo.Fruits'
exec @statement

This should throw the exact same error. Then add () around the @statement and it will work.

The second issue is, that you cannot use dynamic sql in a function, because they have to be deterministic (look here).

To get around this, rewrite that function behaviour into a stored procedure and then it should work ;).

One way to rewrite your function into a procedure might look like this(untested, because I do not have your database structure set up):

CREATE PROCEDURE spGetName @OrganisationID INT = NULL, @UserID INT = NULL, @ClientTypeID INT
AS
BEGIN
    DECLARE @Name VARCHAR(500)        
    DECLARE @dbName VARCHAR(500)            
    SET @dbName = (SELECT ClientDataBase FROM dbo.ClientType WHERE ClientTypeID = @ClientTypeID)

    SET @Name = 'select UserName from [' + @dbName + '].dbo.Users where UserID=' + CONVERT(VARCHAR, @UserID)  
    exec(@Name)
END
GO

edited May 23 '17 at 10:24

Community

1
1

answered Feb 25 '14 at 09:05

DrCopyPaste

4,023
1
22
57

I am trying to execute your suggestion exec(@name) that follows this issue Invalid use of a side-effecting operator 'EXECUTE STRING' within a function. – user3350258 Feb 25 '14 at 09:10
@user3350258 sorry for the trouble I edited my answer – DrCopyPaste Feb 25 '14 at 09:16
@user3350258 what do you mean exactly? why not? – DrCopyPaste Feb 25 '14 at 09:18
1

@user3350258 I added an example of how you could rewrite your function into a procedure – DrCopyPaste Feb 25 '14 at 09:36
But I am using function in many stored procedures to generate a username – user3350258 Feb 25 '14 at 09:58
@user3350258 I am afraid then you will have to rewrite all calls to that function into calls to a procedure or you are out of luck ;) – DrCopyPaste Feb 25 '14 at 10:00

score 0 · Answer 2 · answered Oct 03 '14 at 18:31

0

Try using

exec sp_executesql @statement

answered Oct 03 '14 at 18:31

Jorge Mejia

1,153
1
10
27

the name is not a valid identifier error in function

2 Answers2