Inserting string variable into S-Proc parameter

Question

I'm receiving an error for the following query:

EXEC dbo.sp_Sproc_Name
@Param1=@ParamValue1
,@Param2='lorem ipsum "' + @ParamValue2 + '" dolor'

I get the error:

Incorrect syntax near '+'.

Therefore, how can I pass a variable as part of my parameter value like I'm trying to do above?

Many Thanks.

Answer 1

Unfortunately, T-SQL does not allow you to build a string inline as a parameter (there are certain exceptions for literals), so you will need to do this:

DECLARE @ParamValue2mod AS varchar(whatever)
SET @ParamValue2mod = 'lorem ipsum "' + @ParamValue2 + '" dolor' 

EXEC dbo.sp_Sproc_Name 
@Param1=@ParamValue1 
,@Param2=@ParamValue2mod