CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jesun Bicar
Jesun Bicar

Reputation: 103

Error Executing stored procedure

When i tried to execute this stored procedure it give me is not a valid identifier error can anyone help me?

CREATE Proc [dbo].[AddLogtrail]
    @cmodule varchar(100),   
    @caudittype varchar(15),     
    @ctable varchar(100),    
    @cfield varchar(100),    
    @cfieldtype varchar(100),   
    @coriginalval varchar(100),      
    @cnewval    varchar(100),    
    @cuser varchar(100),     
    @creason varchar(100)     AS    

    DECLARE @SQL  varchar (400)     
    set  @SQL = 'Insert into AudittrailLog (ddatetime,cmodulename,caudittype,ctablename,cfieldname,cfieldtype,coriginalval,cnewval,cuser,creason) values ' + 
    '(convert(datetime,GETDATE(),105),'+@cmodule+','+@caudittype+','+@ctable+','+@cfield+','+@cfieldtype+','+@coriginalval+','+@cnewval+','+@cuser+','+@creason+')';
    PRINT @SQL
    Exec @SQL

Upvotes: 0

Views: 2049

Answers (3)

Ashley Pillay
Ashley Pillay

Reputation: 888

Is your question just a sample or your actual procedure?

Because there's no reason to use dynamic SQL for this procedure. Your query is static. It's a simple inset where all fields are known. Why aren't you just using an INSERT statement directly?

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You need to wrap each varchar in '':

CREATE Proc [dbo].[AddLogtrail]
    @cmodule varchar(100),   
    @caudittype varchar(15),     
    @ctable varchar(100),    
    @cfield varchar(100),    
    @cfieldtype varchar(100),   
    @coriginalval varchar(100),      
    @cnewval    varchar(100),    
    @cuser varchar(100),     
    @creason varchar(100)     AS    

    DECLARE @SQL  varchar (400)     
    set  @SQL = 'Insert into AudittrailLog (ddatetime,cmodulename,caudittype,ctablename,cfieldname,cfieldtype,coriginalval,cnewval,cuser,creason) values ' + 
    '(convert(datetime,GETDATE(),105),'''+@cmodule+''','''+@caudittype+''','''+@ctable+''','''+@cfield+''','''+@cfieldtype+''','''+@coriginalval+''','''+@cnewval+''','''+@cuser+''','''+@creason+''')';
    PRINT @SQL
    Exec @SQL

I hope I didn't miss any of ''.

Upvotes: 0

Chamal
Chamal

Reputation: 1449

You need to add additional quotes for varchar values

set  @SQL = 'Insert into AudittrailLog (ddatetime,cmodulename,caudittype,ctablename,cfieldname,cfieldtype,coriginalval,cnewval,cuser,creason) values ' + 
    '(convert(datetime,GETDATE(),105),'''+@cmodule+''','''+@caudittype+''','''+@ctable+''','''+@cfield+''','''+@cfieldtype+''','''+@coriginalval+''','''+@cnewval+''','''+@cuser+''','''+@creason+''')';

When printed it should display like this.

Insert into AudittrailLog ( ddatetime ,cmodulename ,caudittype ,ctablename ,cfieldname ,cfieldtype ,coriginalval ,cnewval ,cuser ,creason) values (convert(datetime,GETDATE(),105),'test','test','test','test','test','test','test','test','test')

Also execute it like below 

EXEC (@SQL)

Upvotes: 1

Related Questions