7

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says:

Incorrect syntax near '+'

Is this possible ?

I need to see something like:

Investigation is pending for 1234

My query:

    EXEC sp_wf_create_notification 
        @processid,
        @vactivityid,
        @vstepid,
        1,
        @vidColumn,
        @vidColumnTable,
        @investigationidout,
        @owner,
        'Pending Investigation',
        'Investigation is pending for  ['+ @investigationidout +'] '
        @nextstepurl,
        @vstatus OUTPUT,
        @verror OUTPUT
15

The parameters supplied to a procedure must be constants or variables. A function or the output of a function or an expression cannot be used.

You would have to first set a variable with the required expression:

declare @investigation varchar(100);
set @investigation = 'Investigation is pending for  ['+ @investigationidout +'] ';

EXEC sp_wf_create_notification 
    @processid,
    @vactivityid,
    @vstepid,
    1,
    @vidColumn,
    @vidColumnTable,
    @investigationidout,
    @owner,
    'Pending Investigation',
    @investigation
    @nextstepurl,
    @vstatus OUTPUT,
    @verror OUTPUT
;

For OUTPUT variables, don't forget to add OUTPUT after its name.

See MSDN Specify Parameters:

The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.

1

For example, I did something like this to pass concatenated strings to my stored procedure

select top 10 * from sbat

declare @string varchar(max) = 'Rows inserted:' + cast(@@ROWCOUNT as varchar)

exec proc_Log @msg = @string, @tablename = 'STG_SBAT_test'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.