CODEWITHSUNDEEP
sql-server-2008
user3350258
user3350258

Reputation: 11

the name is not a valid identifier error in function

     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

Upvotes: 1

Views: 12304

Answers (2)

Jorge Mejia
Jorge Mejia

Reputation: 1163

Try using

exec sp_executesql @statement

Upvotes: 0

DrCopyPaste
DrCopyPaste

Reputation: 4117

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

Upvotes: 6

Related Questions