CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Brian Battles
Brian Battles

Reputation: 1011

Error calling simple stored procedure to create text log file

I created a stored procedure to write text out to a log file.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[WriteTextFileLog]
    @TextForLog nvarchar(MAX)
AS
    DECLARE @CMDTEXT as nvarchar(255)

    SELECT @CMDTEXT = 'echo ' + @TextForLog + ' >> "\\pathtotextfile\LogFile.txt"'

    EXEC master..xp_cmdshell @CMDTEXT;

When I call it like this:

MYDATABASE.dbo.writetextfilelog  @TextForLog = CONVERT(varchar(23), GETDATE, 121)

I get this error:

Incorrect syntax near the keyword 'CONVERT'

I must be missing something obvious...any ideas?

Upvotes: 2

Views: 326

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

As @marc_s said in the comments, EXECUTE doesn't accept expressions as parameter values, only plain constant string or variable or DEFAULT keyword.

Here is the command's syntax for calling a stored procedure:

[ { EXEC | EXECUTE } ]  
    {   
      [ @return_status = ]  
      { module_name [ ;number ] | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ]  
      [ ,...n ]  
      [ WITH <execute_option> [ ,...n ] ]  
    }  
[;]

So, prepare the value of the parameter in advance in a variable:

DECLARE @VarText nvarchar(max);
SET @VarText = N'Blah blah test ' + CONVERT(varchar(23), GETDATE(), 121);
EXEC MYDATABASE.dbo.writetextfilelog @TextForLog = @VarText;

I'd recommend you to use nvarchar(max) sparingly, only when you really need more than 4000 characters. Otherwise use a sensible limit, like nvarchar(255).

Upvotes: 1

Related Questions