|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Procedure has no parameters and arguments were supplied.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Procedure has no parameters and arguments were supplied.
|
|
| a|ias7 2002-11-27, 6:23 pm |
| Hi all,
I am stuck with this stored procedure. It takes some parameters from a JSP
page and executes two inserts in SQL 2000 database. However, there is
exception
"Procedure has no parameters and arguments were supplied" and I can not
figure out where the error is - I know only is my T-SQL
Here is:
----sp_createNewFinishing----
CREATE PROCEDURE sp_createNewRefinishing
DECLARE @resurfacing varchar(50)
DECLARE @prefinishing varchar(50)
DECLARE @finishingType varchar(50)
DECLARE @numFinishingCoats int
DECLARE @saleTypeID int
DECLARE @unitDescription varchar(50)
DECLARE @areaName varchar(50)
DECLARE @priceAttributeName varchar(50)
DECLARE @regCost float
DECLARE @saleCost float
DECLARE @regRetailPrice float
DECLARE @saleRetailPrice float
DECLARE @AREAID int
DECLARE @UOMID int
DECLARE @PRICEATTRIBUTEID int
AS
// Here I want to execute 3 othre SP so obtain three different INT values. I
have alse a 4th value that comes from the JSP input and with all of them
construct a composite PRIMARY_KEY for the second INSERT
EXECUTE sp_areaIndex @areaName, @AREAID OUTPUT
EXECUTE sp_UOMINDEX @unitDescription, @UOMID OUTPUT
EXECUTE sp_priceAttribIndex @priceAttributeName, @PRICEATTRIBUTEID OUTPUT
// 1st INSERT - PRIMARY_KEY IS AUTONUMBER
INSERT INTO tblReFinishing (resurfacing, prefinishing, finishingType,
numFinishingCoats)
VALUES (@resurfacing, @prefinishing, @finishingType, @numFinishingCoats)
// 2nd INSERT - PRIMARY_KEY IS COMPOSITE FROM THE FIRST 4 VALUES.
INSERT INTO tblSaleItem(areaID, saleTypeID, priceAttributeID, uomID,
regCost, saleCost, regRetailPrice, saleRetailPrice)
VALUES (@AREAID, @saleTypeId, @PRICEATTRIBUTEID, @UOMID, @regCost,
@saleCost, @regRetailPrice, @saleRetailPrice)
GO
---sp_areaIndex---
CREATE PROCEDURE sp_areaIndex @areaName varchar(50) = '%', @AREAID int
OUTPUT
AS
SELECT @AREAID = areaID
FROM tblAreas
WHERE areaName = @areaName
GO
--sp_priceAttribIndex--
CREATE PROCEDURE sp_priceAttribIndex @priceAttributeName varchar(50) = '%',
@PRICEATTRIBUTEID int OUTPUT
AS
SELECT @PRICEATTRIBUTEID = priceAttributeID
FROM tblPriceAttributes
WHERE priceAttributeName = @priceAttributeName
GO
---sp_UOMIndex---
CREATE PROCEDURE sp_UOMIndex @unitDescription varchar(50) = '%', @UOMID int
OUTPUT
AS
SELECT @UOMID = uomID
FROM tblUOMs
WHERE unitDescription = @unitDescription
GO
Any feedback will be greatly appreciated
| |
| Jasper Smith 2002-11-27, 6:23 pm |
| > I have alse a 4th value that comes from the JSP input
Just a guess but your main procedure has no input parameters
so how are you expecting to pass parameters to it from JSP ?
The error is simply indicating that you are passing parameters
to a procedure that doesn't have any.
--
HTH
Jasper Smith (SQL Server MVP)
"a|ias7" <stoeff@REMOVE.omnihub.com> wrote in message
news:1038441594.996640@nntp.acecape.com...
> Hi all,
>
> I am stuck with this stored procedure. It takes some parameters from a JSP
> page and executes two inserts in SQL 2000 database. However, there is
> exception
> "Procedure has no parameters and arguments were supplied" and I can not
> figure out where the error is - I know only is my T-SQL
>
> Here is:
> ----sp_createNewFinishing----
> CREATE PROCEDURE sp_createNewRefinishing
>
> DECLARE @resurfacing varchar(50)
> DECLARE @prefinishing varchar(50)
> DECLARE @finishingType varchar(50)
> DECLARE @numFinishingCoats int
> DECLARE @saleTypeID int
> DECLARE @unitDescription varchar(50)
> DECLARE @areaName varchar(50)
> DECLARE @priceAttributeName varchar(50)
> DECLARE @regCost float
> DECLARE @saleCost float
> DECLARE @regRetailPrice float
> DECLARE @saleRetailPrice float
>
> DECLARE @AREAID int
> DECLARE @UOMID int
> DECLARE @PRICEATTRIBUTEID int
>
> AS
>
> // Here I want to execute 3 othre SP so obtain three different INT values.
I
> have alse a 4th value that comes from the JSP input and with all of them
> construct a composite PRIMARY_KEY for the second INSERT
>
> EXECUTE sp_areaIndex @areaName, @AREAID OUTPUT
> EXECUTE sp_UOMINDEX @unitDescription, @UOMID OUTPUT
> EXECUTE sp_priceAttribIndex @priceAttributeName, @PRICEATTRIBUTEID OUTPUT
>
> // 1st INSERT - PRIMARY_KEY IS AUTONUMBER
> INSERT INTO tblReFinishing (resurfacing, prefinishing, finishingType,
> numFinishingCoats)
> VALUES (@resurfacing, @prefinishing, @finishingType, @numFinishingCoats)
>
> // 2nd INSERT - PRIMARY_KEY IS COMPOSITE FROM THE FIRST 4 VALUES.
> INSERT INTO tblSaleItem(areaID, saleTypeID, priceAttributeID, uomID,
> regCost, saleCost, regRetailPrice, saleRetailPrice)
> VALUES (@AREAID, @saleTypeId, @PRICEATTRIBUTEID, @UOMID, @regCost,
> @saleCost, @regRetailPrice, @saleRetailPrice)
> GO
>
>
> ---sp_areaIndex---
> CREATE PROCEDURE sp_areaIndex @areaName varchar(50) = '%', @AREAID int
> OUTPUT
> AS
> SELECT @AREAID = areaID
> FROM tblAreas
> WHERE areaName = @areaName
> GO
>
> --sp_priceAttribIndex--
> CREATE PROCEDURE sp_priceAttribIndex @priceAttributeName varchar(50) =
'%',
> @PRICEATTRIBUTEID int OUTPUT
> AS
> SELECT @PRICEATTRIBUTEID = priceAttributeID
> FROM tblPriceAttributes
> WHERE priceAttributeName = @priceAttributeName
> GO
>
> ---sp_UOMIndex---
> CREATE PROCEDURE sp_UOMIndex @unitDescription varchar(50) = '%', @UOMID
int
> OUTPUT
> AS
> SELECT @UOMID = uomID
> FROM tblUOMs
> WHERE unitDescription = @unitDescription
> GO
>
> Any feedback will be greatly appreciated
>
>
>
>
| |
| a|ias7 2002-11-27, 8:23 pm |
| Hi,
I do actually -
<snip>
import java.sql.CallableStatement
----
CallableStatement cstmt = con.prepareCall( "{call
sp_createNewRefinishing(?,?,?,?,?,?,?,?,?,?,?,?)}");
---
cstmt.executeQuery();
---
</snip>
Further I tried execute the procedure from the SQL analyzer and got the same
error as from the JSP client. And I can see in my IDE debugger all the
vallues I pass from the Test Case to the CallableStatemet are pupaleted
correctrly.
"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
news:O0Gz8KnlCHA.2456@tkmsftngp04...
> > I have alse a 4th value that comes from the JSP input
>
> Just a guess but your main procedure has no input parameters
> so how are you expecting to pass parameters to it from JSP ?
> The error is simply indicating that you are passing parameters
> to a procedure that doesn't have any.
>
> --
> HTH
>
> Jasper Smith (SQL Server MVP)
>
> "a|ias7" <stoeff@REMOVE.omnihub.com> wrote in message
> news:1038441594.996640@nntp.acecape.com...
> > Hi all,
> >
> > I am stuck with this stored procedure. It takes some parameters from a
JSP
> > page and executes two inserts in SQL 2000 database. However, there is
> > exception
> > "Procedure has no parameters and arguments were supplied" and I can not
> > figure out where the error is - I know only is my T-SQL
> >
> > Here is:
> > ----sp_createNewFinishing----
> > CREATE PROCEDURE sp_createNewRefinishing
> >
> > DECLARE @resurfacing varchar(50)
> > DECLARE @prefinishing varchar(50)
> > DECLARE @finishingType varchar(50)
> > DECLARE @numFinishingCoats int
> > DECLARE @saleTypeID int
> > DECLARE @unitDescription varchar(50)
> > DECLARE @areaName varchar(50)
> > DECLARE @priceAttributeName varchar(50)
> > DECLARE @regCost float
> > DECLARE @saleCost float
> > DECLARE @regRetailPrice float
> > DECLARE @saleRetailPrice float
> >
> > DECLARE @AREAID int
> > DECLARE @UOMID int
> > DECLARE @PRICEATTRIBUTEID int
> >
> > AS
> >
> > // Here I want to execute 3 othre SP so obtain three different INT
values.
> I
> > have alse a 4th value that comes from the JSP input and with all of them
> > construct a composite PRIMARY_KEY for the second INSERT
> >
> > EXECUTE sp_areaIndex @areaName, @AREAID OUTPUT
> > EXECUTE sp_UOMINDEX @unitDescription, @UOMID OUTPUT
> > EXECUTE sp_priceAttribIndex @priceAttributeName, @PRICEATTRIBUTEID
OUTPUT
> >
> > // 1st INSERT - PRIMARY_KEY IS AUTONUMBER
> > INSERT INTO tblReFinishing (resurfacing, prefinishing, finishingType,
> > numFinishingCoats)
> > VALUES (@resurfacing, @prefinishing, @finishingType,
@numFinishingCoats)
> >
> > // 2nd INSERT - PRIMARY_KEY IS COMPOSITE FROM THE FIRST 4 VALUES.
> > INSERT INTO tblSaleItem(areaID, saleTypeID, priceAttributeID, uomID,
> > regCost, saleCost, regRetailPrice, saleRetailPrice)
> > VALUES (@AREAID, @saleTypeId, @PRICEATTRIBUTEID, @UOMID, @regCost,
> > @saleCost, @regRetailPrice, @saleRetailPrice)
> > GO
> >
> >
> > ---sp_areaIndex---
> > CREATE PROCEDURE sp_areaIndex @areaName varchar(50) = '%', @AREAID int
> > OUTPUT
> > AS
> > SELECT @AREAID = areaID
> > FROM tblAreas
> > WHERE areaName = @areaName
> > GO
> >
> > --sp_priceAttribIndex--
> > CREATE PROCEDURE sp_priceAttribIndex @priceAttributeName varchar(50) =
> '%',
> > @PRICEATTRIBUTEID int OUTPUT
> > AS
> > SELECT @PRICEATTRIBUTEID = priceAttributeID
> > FROM tblPriceAttributes
> > WHERE priceAttributeName = @priceAttributeName
> > GO
> >
> > ---sp_UOMIndex---
> > CREATE PROCEDURE sp_UOMIndex @unitDescription varchar(50) = '%', @UOMID
> int
> > OUTPUT
> > AS
> > SELECT @UOMID = uomID
> > FROM tblUOMs
> > WHERE unitDescription = @unitDescription
> > GO
> >
> > Any feedback will be greatly appreciated
> >
> >
> >
> >
>
>
>
| |
| Steve Kass 2002-11-28, 1:23 am |
| What you've posted here is not valid T-SQL (DECLARE cannot come after
CREATE PROCEDURE procedure_name
Can you post the actual code, cut and pasted, not retyped from the code that
produces the error? It's impossible to say what's wrong without seeing
the code
that causes the error.
Steve Kass
Drew University
a|ias7 wrote:
>Hi all,
>
>I am stuck with this stored procedure. It takes some parameters from a JSP
>page and executes two inserts in SQL 2000 database. However, there is
>exception
>"Procedure has no parameters and arguments were supplied" and I can not
>figure out where the error is - I know only is my T-SQL
>
>Here is:
>----sp_createNewFinishing----
>CREATE PROCEDURE sp_createNewRefinishing
>
>DECLARE @resurfacing varchar(50)
>DECLARE @prefinishing varchar(50)
>DECLARE @finishingType varchar(50)
>DECLARE @numFinishingCoats int
>DECLARE @saleTypeID int
>DECLARE @unitDescription varchar(50)
>DECLARE @areaName varchar(50)
>DECLARE @priceAttributeName varchar(50)
>DECLARE @regCost float
>DECLARE @saleCost float
>DECLARE @regRetailPrice float
>DECLARE @saleRetailPrice float
>
>DECLARE @AREAID int
>DECLARE @UOMID int
>DECLARE @PRICEATTRIBUTEID int
>
>AS
>
>// Here I want to execute 3 othre SP so obtain three different INT values. I
>have alse a 4th value that comes from the JSP input and with all of them
>construct a composite PRIMARY_KEY for the second INSERT
>
>EXECUTE sp_areaIndex @areaName, @AREAID OUTPUT
>EXECUTE sp_UOMINDEX @unitDescription, @UOMID OUTPUT
>EXECUTE sp_priceAttribIndex @priceAttributeName, @PRICEATTRIBUTEID OUTPUT
>
>// 1st INSERT - PRIMARY_KEY IS AUTONUMBER
>INSERT INTO tblReFinishing (resurfacing, prefinishing, finishingType,
>numFinishingCoats)
> VALUES (@resurfacing, @prefinishing, @finishingType, @numFinishingCoats)
>
>// 2nd INSERT - PRIMARY_KEY IS COMPOSITE FROM THE FIRST 4 VALUES.
>INSERT INTO tblSaleItem(areaID, saleTypeID, priceAttributeID, uomID,
>regCost, saleCost, regRetailPrice, saleRetailPrice)
> VALUES (@AREAID, @saleTypeId, @PRICEATTRIBUTEID, @UOMID, @regCost,
>@saleCost, @regRetailPrice, @saleRetailPrice)
>GO
>
>
>---sp_areaIndex---
>CREATE PROCEDURE sp_areaIndex @areaName varchar(50) = '%', @AREAID int
>OUTPUT
>AS
>SELECT @AREAID = areaID
>FROM tblAreas
>WHERE areaName = @areaName
>GO
>
>--sp_priceAttribIndex--
>CREATE PROCEDURE sp_priceAttribIndex @priceAttributeName varchar(50) = '%',
>@PRICEATTRIBUTEID int OUTPUT
>AS
>SELECT @PRICEATTRIBUTEID = priceAttributeID
>FROM tblPriceAttributes
>WHERE priceAttributeName = @priceAttributeName
>GO
>
>---sp_UOMIndex---
>CREATE PROCEDURE sp_UOMIndex @unitDescription varchar(50) = '%', @UOMID int
>OUTPUT
>AS
>SELECT @UOMID = uomID
>FROM tblUOMs
>WHERE unitDescription = @unitDescription
>GO
>
>Any feedback will be greatly appreciated
>
>
>
>
>
>
| |
| Jasper Smith 2002-11-28, 2:23 am |
| My point (as Steve point's out and I didn't make very clearly)
is that the syntax is wrong. Stored procedure parameters are
not prefixed by declare hence your stored procedure as posted
in your original post does NOT have any input parameters.
It should look more like this...
CREATE PROCEDURE sp_createNewRefinishing
(
@resurfacing varchar(50),
@prefinishing varchar(50),
@finishingType varchar(50),
@numFinishingCoats int,
@saleTypeID int,
@unitDescription varchar(50),
@areaName varchar(50),
@priceAttributeName varchar(50),
@regCost float,
@saleCost float,
@regRetailPrice float,
@saleRetailPrice float,
)
AS
SET NOCOUNT ON
DECLARE @AREAID int
DECLARE @UOMID int
DECLARE @PRICEATTRIBUTEID int
EXECUTE sp_areaIndex @areaName, @AREAID OUTPUT
EXECUTE sp_UOMINDEX @unitDescription, @UOMID OUTPUT
EXECUTE sp_priceAttribIndex @priceAttributeName, @PRICEATTRIBUTEID OUTPUT
// 1st INSERT - PRIMARY_KEY IS AUTONUMBER
INSERT INTO tblReFinishing (resurfacing, prefinishing, finishingType,
numFinishingCoats)
VALUES (@resurfacing, @prefinishing, @finishingType, @numFinishingCoats)
// 2nd INSERT - PRIMARY_KEY IS COMPOSITE FROM THE FIRST 4 VALUES.
INSERT INTO tblSaleItem(areaID, saleTypeID, priceAttributeID, uomID,
regCost, saleCost, regRetailPrice, saleRetailPrice)
VALUES (@AREAID, @saleTypeId, @PRICEATTRIBUTEID, @UOMID, @regCost,
@saleCost, @regRetailPrice, @saleRetailPrice)
GO
--
HTH
Jasper Smith (SQL Server MVP)
"a|ias7" <stoeff@REMOVE.omnihub.com> wrote in message
news:hVeF9.4333$yy.564361@newsread1.prod.itd.earthlink.net...
> Hi,
>
> I do actually -
> <snip>
> import java.sql.CallableStatement
> ----
>
> CallableStatement cstmt = con.prepareCall( "{call
> sp_createNewRefinishing(?,?,?,?,?,?,?,?,?,?,?,?)}");
> ---
> cstmt.executeQuery();
>
> ---
> </snip>
>
> Further I tried execute the procedure from the SQL analyzer and got the
same
> error as from the JSP client. And I can see in my IDE debugger all the
> vallues I pass from the Test Case to the CallableStatemet are pupaleted
> correctrly.
>
>
> "Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
> news:O0Gz8KnlCHA.2456@tkmsftngp04...
> > > I have alse a 4th value that comes from the JSP input
> >
> > Just a guess but your main procedure has no input parameters
> > so how are you expecting to pass parameters to it from JSP ?
> > The error is simply indicating that you are passing parameters
> > to a procedure that doesn't have any.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > "a|ias7" <stoeff@REMOVE.omnihub.com> wrote in message
> > news:1038441594.996640@nntp.acecape.com...
> > > Hi all,
> > >
> > > I am stuck with this stored procedure. It takes some parameters from a
> JSP
> > > page and executes two inserts in SQL 2000 database. However, there is
> > > exception
> > > "Procedure has no parameters and arguments were supplied" and I can
not[c
olor=darkred]
> > > figure out where the error is - I know only is my T-SQL
> > >
> > > Here is:
> > > ----sp_createNewFinishing----
> > > CREATE PROCEDURE sp_createNewRefinishing
> > >
> > > DECLARE @resurfacing varchar(50)
> > > DECLARE @prefinishing varchar(50)
> > > DECLARE @finishingType varchar(50)
> > > DECLARE @numFinishingCoats int
> > > DECLARE @saleTypeID int
> > > DECLARE @unitDescription varchar(50)
> > > DECLARE @areaName varchar(50)
> > > DECLARE @priceAttributeName varchar(50)
> > > DECLARE @regCost float
> > > DECLARE @saleCost float
> > > DECLARE @regRetailPrice float
> > > DECLARE @saleRetailPrice float
> > >
> > > DECLARE @AREAID int
> > > DECLARE @UOMID int
> > > DECLARE @PRICEATTRIBUTEID int
> > >
> > > AS
> > >
> > > // Here I want to execute 3 othre SP so obtain three different INT
> values.
> > I
> > > have alse a 4th value that comes from the JSP input and with all of[/color]
them[
color=darkred]
> > > construct a composite PRIMARY_KEY for the second INSERT
> > >
> > > EXECUTE sp_areaIndex @areaName, @AREAID OUTPUT
> > > EXECUTE sp_UOMINDEX @unitDescription, @UOMID OUTPUT
> > > EXECUTE sp_priceAttribIndex @priceAttributeName, @PRICEATTRIBUTEID
> OUTPUT
> > >
> > > // 1st INSERT - PRIMARY_KEY IS AUTONUMBER
> > > INSERT INTO tblReFinishing (resurfacing, prefinishing, finishingType,
> > > numFinishingCoats)
> > > VALUES (@resurfacing, @prefinishing, @finishingType,
> @numFinishingCoats)
> > >
> > > // 2nd INSERT - PRIMARY_KEY IS COMPOSITE FROM THE FIRST 4 VALUES.
> > > INSERT INTO tblSaleItem(areaID, saleTypeID, priceAttributeID, uomID,
> > > regCost, saleCost, regRetailPrice, saleRetailPrice)
> > > VALUES (@AREAID, @saleTypeId, @PRICEATTRIBUTEID, @UOMID, @regCost,
> > > @saleCost, @regRetailPrice, @saleRetailPrice)
> > > GO
> > >
> > >
> > > ---sp_areaIndex---
> > > CREATE PROCEDURE sp_areaIndex @areaName varchar(50) = '%', @AREAID int
> > > OUTPUT
> > > AS
> > > SELECT @AREAID = areaID
> > > FROM tblAreas
> > > WHERE areaName = @areaName
> > > GO
> > >
> > > --sp_priceAttribIndex--
> > > CREATE PROCEDURE sp_priceAttribIndex @priceAttributeName varchar(50) =
> > '%',
> > > @PRICEATTRIBUTEID int OUTPUT
> > > AS
> > > SELECT @PRICEATTRIBUTEID = priceAttributeID
> > > FROM tblPriceAttributes
> > > WHERE priceAttributeName = @priceAttributeName
> > > GO
> > >
> > > ---sp_UOMIndex---
> > > CREATE PROCEDURE sp_UOMIndex @unitDescription varchar(50) = '%',[/color]
@UOMID
> > int
> > > OUTPUT
> > > AS
> > > SELECT @UOMID = uomID
> > > FROM tblUOMs
> > > WHERE unitDescription = @unitDescription
> > > GO
> > >
> > > Any feedback will be greatly appreciated
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
|
|
|
|
|