Procedure has no parameters and arguments were ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Procedure has no parameters and arguments were ...

Author

Topic

carla
Starting Member

12 Posts

Posted - 2011-01-13 : 13:30:50

hi yall! im getting this error now... "Procedure has no parameters and arguments were supplied" can someone know what it means??

This is my code:

ALTER PROCEDURE [dbo].[spInsertaDominiosXVencer]
AS

DECLARE @intDominioId as numeric
DECLARE @strDominio as varchar(200)
DECLARE @dtmDominioFechaVencimiento as datetime
DECLARE @year as int
DECLARE @month as int

SET @month = Month(Dateadd(month,3,getdate()))
SET @year = Year(getdate())

IF month(getdate())= 12
BEGIN
set @year = year(getdate())+ 1
END
ELSE
BEGIN
set @year = year(getdate())
END

print @year
print @month

DECLARE curDominios CURSOR FOR

SELECT @intDominioId,@strDominio,@dtmDominioFechaVencimiento FROM tblDominio where dtmDominioFechaVencimiento >= dateadd(month,3,getdate()) and dtmDominioFechaVencimiento < dateadd(month,3,getdate())+1

OPEN curDominios

FETCH NEXT FROM curDominios
INTO
@intDominioId,@strDominio,@dtmDominioFechaVencimiento

-- Verficar @@FETCH_STATUS si existen mas registros
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblDominio(intDominioId,strDominio,dtmDominioFechaVencimiento)
VALUES (@intDominioId,@strDominio,@dtmDominioFechaVencimiento)

FETCH NEXT FROM curDominios
INTO
@intDominioId,@strDominio,@dtmDominioFechaVencimiento
END

CLOSE curDominios
DEALLOCATE curDominios

carla

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-13 : 13:53:03

how do you get this error? ... while executing the procedure or ....

carla
Starting Member

12 Posts

Posted - 2011-01-13 : 14:00:44

nop, this happens while i tried to run the job

quote:
Originally posted by MIK_2008

how do you get this error? ... while executing the procedure or ....

carla

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-13 : 15:11:16

That means your procedure has no paramters (which it doesn't) and you are trying to pass parameters (which you can't).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-13 : 15:11:57

For got my example:

CREATE PROCEDURE foo
AS
BEGIN
    SELECT 1
END

EXEC Foo 123

-- Msg 8146, Level 16, State 2, Procedure foo, Line 0
-- Procedure foo has no parameters and arguments were supplied.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-01-14 : 00:40:49

lamprey has given the perfect example.you are exec the procedure in your job by passing params.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-14 : 01:06:17

in Addition to Lamprey and Ahmeds, please note that if you run a Stored procedure which has been defined with parameters then you will need to provide all required parameters for successful execution.. however if you have created it without any parameter (as you can see Lamprey has given an example of such SP) then you should only run it by "Execute <ProcedureName>" if you provide parameter you will get the specified error!

the Code in your original post is of a stored procedure having no parameter ... and this procedure should be called without providing any parameter.. I don't see any line in this code that can give this error, however check/Make sure that all SPs without parameters are called/executed correctly in the Job!...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-14 : 11:55:54

quote:
Originally posted by MIK_2008

in Addition to Lamprey and Ahmeds, please note that if you run a Stored procedure which has been defined with parameters then you will need to provide all required parameters for successful execution..

Not quite true. If you have provided defaults for the paramters, then you do not need to specify them:

CREATE PROCEDURE foo
(
    @Bar INT = 1
)
AS
BEGIN
    SELECT @Bar
END

EXEC Foo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources