how use replace in exec statement in sql server 2008

Question

I have a stored proc, say, "call_Me" with few parameters:

Declare @Greet varchar(100) = 'Hi ||User||'

Exec Call_Me 1,'something', @Greet --parameters: bit, string, string

during the call, I want to be able to replace the

||User||

bit with something else. normally, in a select statement, I would do this:

select 1, 'something', Replace(@Greet, '||User||', u.Username) from UserTable

which works fine, But today, for the first time, I am trying to use it in exec statement, the error says its expecting select, I tried adding select in every possible (and sensible) way but it just didnt seem to work out.

How can I use a replace during an execute statement call?

Many thanks in advance!

Answer 1

You'd need to format @Greet before passing it to the sproc:

Declare @Greet varchar(100) = 'Hi ||User||'
SELECT @Greet = Replace(@Greet, '||User||', u.Username) 
FROM UserTable u
WHERE Id = 1

Exec Call_Me 1,'something', @Greet --parameters: bit, string, string

Answer 2

You can only use a literal or a variable reference in a procedure call:

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

Use this:

Declare @Greet varchar(100) = 'Hi ||User||'
Declare @param VARCHAR(100) = REPLACE(@Greet, '||User||', 'replacement')

Exec Call_Me 1,'something', @param