Sql-server – SQL Server passing variable to a string in a stored proc

parametersql serversql-server-2012stored-procedures

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

Best Answer

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.

Related Question