how use replace in exec statement in sql server 2008

Question

Hi,

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

Answer 3

I am not sure if I understand correctly your problem, but maybe using cursor solves your problem:

DECLARE CURSOR users LOCAL FAST_FORWARD FOR
    SELECT 
        username
    FROM
        usertable

OPEN

DECLARE @username NVARCHAR(50)
DECLARE @Greet VARCHAR(100) = 'Hi ||User||'

FETCH NEXT FROM users INTO @username

WHILE @@FETCH_STATUS = 0 BEGIN

    EXEC Call_Me 1, 'something', REPLACE(@Greet, '||User||', @username)

    FETCH NEXT FROM users INTO @username
END

CLOSE users
DEALLOCATE users

If you don't need to call it in loop you can try something like (this can be a new stored procedure):

DECLARE @username NVARCHAR(50)
DECLARE @Greet VARCHAR(100) = 'Hi ||User||'

SELECT
    @username = username
FROM
    usernames
WHERE
    user_id = 1

IF @@ROWCOUNT = 1 BEGIN
    EXEC Call_Me 1, 'something', REPLACE(@Greet, '||User||', @username)
END