December 21, 2007 at 9:21 am
I have a proc that is returning the above error. According to BOL, I can
add defaults to input parameters on procedures. My procedure is as follows.
My execution is below the proc.
CREATE PROCEDURE PRC_GET_EMAIL_ADDRESS
@v_sequence int,
@v_type char(1) = NULL,
@v_email varchar(50) OUTPUT
AS
BEGIN
DO SOME STUFF
END
If I execute
EXECUTE prc_get_email_address @v_ems1, P, @v_ema1 OUTPUT
Select @v_ema1
--------------------------------------------------
I get the expected result.
If I execute
EXECUTE prc_get_email_address @v_ems1, @v_ema1 OUTPUT
I get:
Server: Msg 8162, Level 16, State 2, Line -2120289368
Formal parameter '@v_type' was defined as OUTPUT but the actual parameter
not declared OUTPUT.
December 21, 2007 at 9:35 am
In your second call with only two parameters, the second variable is mapped to the second variable in the stored procedure. If you are going to execute the stored procedure with just two variables, you need to do it like this:
EXECUTE prc_get_email_address @v_sequence = @v_ems1, @v_email = @v_ema1 OUTPUT
😎
December 21, 2007 at 12:42 pm
A small change in the proc...
1st proc
CREATE PROCEDURE mailsecurity
@dbname varchar(20),
@name varchar(10),
@emp varchar(4),
@admin-2 char(1) output
as
begin
set nocount on
declare @SQLCmd nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
DO SOME STUFF
'
END
2nd proc
CREATE PROCEDURE PRC_GET_EMAIL_ADDRESS
@dbname varchar(20),
@name varchar(4)
AS
begin
set nocount on
declare @SQLCmd nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
declare ADmin char(1)
execute mailSecurity @name,NULL,@Admin output
DO SOME STUFF
'
END
when running the second proc as
exec PRC_GET_EMAIL_ADDRESS 'Employees','ABC'
an getting this error
Msg 8162, Level 16, State 2, Procedure mailsecurity , Line 0
Formal parameter '@emp' was defined as OUTPUT but the actual parameter not declared OUTPUT.
December 21, 2007 at 1:03 pm
Procedure mailsecurity is expecting 4 parameters, inthe following order:
@dbname, @name, @emp, @admin-2 (output)
Your call in PRC_GET_EMAIL_ADDRESS is only sending 3 parameters mapped to the expected parameters as follows:
@name to @dbname, NULL to @name, @admin-2 to @emp, and NULL (not provided) to @admin.
Your procedure is expecting 4 parameters and isn't getting them. If you are not providing the parameters in the
declared order, you need to do the following:
exec mailSecurity @dbname = @yourVar1, @admin-2 = @yourVar4 OUTPUT, @emp = @yourVar3, @name = @yourVar2
December 21, 2007 at 1:16 pm
when i run like
exec mailsecurity @dbname,@emp,NULL,Admin output
it is asking to me to delcare @dbname but there is already a parameter @dbname for the second proc
December 21, 2007 at 1:52 pm
You are confusing the the names in the call below with the names of your parameters in the stored procedure definition. they are not the same.
exec mailsecurity @dbname,@emp,NULL,Admin output
exec mailsecurity @dbname = @dbname, @name = @emp, @emp = NULL, @admin-2 = @admin-2 OUTPUT
In the above call, the variable names on the left side of the '=' are the variable names declared in the stored procedure definition. The variables and null on the right side of the '=' are the values being passed or returned from the stored procedure.
Does this help?
December 26, 2007 at 7:40 am
yeah when i do that am getting the following error
exec mailsecurity @dbname = @dbname, @name = @emp, @emp = NULL, @admin-2 = @admin-2 OUTPUT
error is
Msg 137, Level 15, State 2, Line 7
Must declare the variable '@dbname'.
exec mailsecurity @dbname = '+@dbname+', @name ='+ @emp+', @emp = NULL, @admin-2 = @admin-2 OUTPUT
error is
Msg 925, Level 19, State 1, Line 1
Maximum number of databases used for each query has been exceeded. The maximum allowed is 8
December 27, 2007 at 7:12 am
1st proc
CREATE PROCEDURE mailsecurity
@dbname varchar(20),
@name varchar(10),
@emp varchar(4),
@admin-2 char(1) output
as
begin
set nocount on
declare @SQLCmd nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
DO SOME STUFF
'
END
2nd proc
CREATE PROCEDURE getaddress
@dbname varchar(20),
@name varchar(4)
AS
begin
set nocount on
declare @SQLCmd nvarchar(4000)
set @SQLCmd = 'use ' + @DBName + ';
declare ADmin char(1)
execute mailSecurity @name,NULL,@Admin output -- Causing error
DO SOME STUFF
'
END
when running the second proc as
exec getaddress 'Employees','SKU'
an getting this error
Msg 8162, Level 16, State 2, Procedure mailsecurity , Line 0
Formal parameter '@emp' was defined as OUTPUT but the actual parameter not declared OUTPUT.
when i pass parameters in the 2nd proc like execute mailSecurity @dbname=@dbname,@name=@name,@emp=NULL,@Admin=@Admin output
I get an error saying Must declare @dbname, but i assume there is already @dbname declared as parameter.
December 27, 2007 at 9:30 am
Mike,
The name in the proc is irrelevant to your Call. You need to declare @dbname in your batch, not in the proc.
If I have
CREATE PROCEDURE getaddress
@dbname varchar(20),
@name varchar(4)
I can call this in two ways.
exec getaddress 'Employee', 'SKU'
OR
declare @db varchar(20), @nm varchar(4)
select @dbname = 'Employee', @nm = 'SKU'
exec getaddress @db, @nm
If I don't have the declare statement, then @db is not defined in my batch. Which is what is happeneing below. You're confusing the variable in your batch with the parameter in the proc. The only reason that you would care about the variable name is if you were only passing some parameters or sending them out of order as in
declare @db varchar(20), @nm varchar(4)
select @dbname = 'Employee', @nm = 'SKU'
exec getaddress @name = @nm, @dbname=@db
exec getaddress @name = @nm
IF you have declared a parameter as OUTPUT, then you must include the OUTPUT notation after that parameter in your call.
December 28, 2007 at 1:00 am
Best practise is to always map your procedure parameters.
Otherwise parameterse are mapped according to their position in the parameterlist in the exec-clause.
Also keep in mind when using e.g. a vb.net command object : parameters are mapped according to their position in the command object. (not by cmdobject.parameter.name !! )
So the order of adding parameterobjects to your commandobject is crucial !
e.g.
DECLARE @rc int
DECLARE @myIdNr int
DECLARE @myResultvalue varchar(128)
-- TODO: Set parameter values here.
select @myIdNr = 123
, @myResultvalue ='' --init to blanc
EXECUTE @rc = [theowner].[theproc]
@IdNr=@myIdNr,
@Resultvalue =@myResultvalue OUTPUT ;
print @rc
print @Resultvalue
Indeed, it's a pitty MS does not generate an exec statement with mapped parameters, but with ordinal variables.
"VB.Net support from your SQLServer DBA" can be found at http://www.sqlservercentral.com/scripts/Miscellaneous/31639/
:w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply