SQL Server - Pass Server and database to stored procedure

Question

(Excuse the amateur nature of the question)

I am trying to pass the servername and database name to a stored procedure which does some logging as follows

exec [dbo].[Logger]
      @Server = @@servername,
      @Database = db_name(db_id()),
      @Task = 'Some task';

but I keep getting this error -

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'db_id'.

I have got this to work using declare @dbname varchar(255) = db_name(db_id()); at the top and then using that. But it seems like an unnecessary variable.

Thanks

That extra variable **is** indeed necessary since you **cannot** use functions in a stored procedure execution call - you need to have all values needed in variables and/or literals — marc_s, May 10 '13 at 16:54
duplicate of: http://stackoverflow.com/questions/5967035/using-function-as-a-parameter-when-executing-a-stored-procedure — John Sobolewski, May 10 '13 at 16:55

score 1 · Accepted Answer · answered May 10 '13 at 17:35

1

Try this:

declare @servername varchar(100) = @@servername
declare @dbname = db_name(db_id())

exec [dbo].[Logger]
      @Server = @servername,
      @Database = @dbname,
      @Task = 'Some task'

answered May 10 '13 at 17:35

Jasmina Shevchenko

797
6
17

SQL Server - Pass Server and database to stored procedure

1 Answers1