December 18, 2015 at 3:37 pm
Just curious what I'm missing here. I'm unable to pass ERROR_MESSAGE( ) (or any of the other error related values) as a parameter:
create procedure printme
@p_val varchar(50)
as
begin
print @p_val
end
go
declare @l_num int
begin
begin try
set @l_num = 1/0;
end try
begin catch
exec printme ERROR_MESSAGE()
end catch
end
go
The error I get is
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.
I can put it into a variable first and everything works fine, but the plan is to pass the 6 or 7 of these variables into a routine for logging, and I don't want to have to put those variables in every program.
Thanks,
--=Chuck
December 18, 2015 at 3:58 pm
You can't do that directly, unfortunately.
There's been at least one long-lived Connect item requesting this ability: https://connect.microsoft.com/SQLServer/feedback/details/352110/t-sql-use-scalar-functions-as-stored-procedure-parameters.
Until MS decides to allow scalar functions to be used directly for specifying parameter values in addition to literals and variables, you'll have to pass it to a variable first.
For a workaround you could create a wrapper stored procedure that assigns the values to variables and calls the printme stored procedure with those variables. Then you wouldn't have to duplicate the declaring and assigning in a bunch of different places in your app code.
Maybe something like this?
create procedure printme
@p_val varchar(50)
as
begin
print @p_val
end
go
CREATE PROCEDURE print_error
AS
BEGIN
DECLARE @error_message varchar(50)
SET @error_message=ERROR_MESSAGE()
EXEC printme @error_message
END
GO
declare @l_num int
begin
begin try
set @l_num = 1/0;
end try
begin catch
exec print_error
end catch
end
go
That should get you close to what you're wanting, if I understand your goal correctly. For ERROR_MESSAGE(), I might also consider making the variable bigger than varchar(50), since that would truncate a lot of error messages. Of course, if that was just something to quickly mock up a sample for here, then disregard 🙂
Cheers!
December 18, 2015 at 4:03 pm
Bummer! Oh well, thanks for the quick end-of-the-workweek response 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply