Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how use replace in exec statement in sql server 2008

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!

like image 502
iamserious Avatar asked Oct 27 '10 11:10

iamserious


People also ask

What does replace () do in SQL?

The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.

How do I replace a specific column value in SQL?

The Replace statement is used to replace all occurrences of a specified string value with another string value. The Replace statement inserts or replaces values in a table. Use the Replace statement to insert new rows in a table and/or replace existing rows in a table.


2 Answers

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
like image 135
AdaTheDev Avatar answered Oct 19 '22 19:10

AdaTheDev


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
like image 38
Quassnoi Avatar answered Oct 19 '22 18:10

Quassnoi