timohorn
asked on
access 2010 stored procedure made as text can not be edited
When i create a stored procedure as text in msacces, it works fine, but when i want to edit it i get numerous errors. if i choose not to save the changes and run the procedure again. it works fine
when i remove the second set no count on an save i get the following error
must declare the scalar variable @isbn
when i used the same sort of procedure in access 2003 it worked just fine ??
when i remove the second set no count on an save i get the following error
must declare the scalar variable @isbn
when i used the same sort of procedure in access 2003 it worked just fine ??
CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS
SET NOCOUNT ON
SET NOCOUNT ON CREATE TABLE #voorraad_afrekentitel(ISBN varchar(13) NOT NULL, jaar char(4), fonds int, titel varchar(50), auteur varchar(50), voornaam varchar(50),
eindvoorraad int, inslagen int, verkopen int, verkopenR int)
INSERT
INTO #voorraad_afrekentitel(Isbn, jaar, fonds, Titel, Auteur, Voornaam, Eindvoorraad, inslagen, verkopen, verkopenr)
SELECT DISTINCT
dbo.VW_eindvoorraad.ISBN, dbo.VW_eindvoorraad.jaar, dbo.PI_artikel_informatie.Fonds, dbo.VW_eindvoorraad.Titel,
dbo.VW_eindvoorraad.Auteur, dbo.VW_eindvoorraad.Voornaam, dbo.VW_eindvoorraad.eindvoorraad, dbo.VW_Inslagen.inslagen,
ISNULL(dbo.VW_Inslagen.inslagen, 2) - ISNULL(dbo.VW_eindvoorraad.eindvoorraad, 2) AS verkopen,
dbo.[Tsomverkopen in jaar].[verkopen royalty]
FROM dbo.VW_eindvoorraad LEFT OUTER JOIN
dbo.[Tsomverkopen in jaar] ON dbo.VW_eindvoorraad.ISBN = dbo.[Tsomverkopen in jaar].ISBN AND
dbo.VW_eindvoorraad.jaar = dbo.[Tsomverkopen in jaar].Jaar LEFT OUTER JOIN
dbo.PI_artikel_informatie ON dbo.VW_eindvoorraad.ISBN = dbo.PI_artikel_informatie.ISBN LEFT OUTER JOIN
dbo.VW_Inslagen ON dbo.VW_eindvoorraad.jaar = dbo.VW_Inslagen.jaar AND dbo.VW_eindvoorraad.ISBN = dbo.VW_Inslagen.Isbn
WHERE (dbo.VW_eindvoorraad.ISBN = @isbn)
SELECT Isbn, jaar, fonds, Titel, Auteur, Voornaam, Eindvoorraad, inslagen, verkopen, verkopenr
FROM #voorraad_afrekentitel RETURN
ASKER
i wil try it tomorrow morning its 23:30 here and let you know i hope so, but still its strange.
The Netherlands?
This may be a bit squirrelly
CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS
Why is the bold text there?
shouldn't it be
CREATE PROCEDURE OpgeslagenProcedure2 AS
CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS
Why is the bold text there?
shouldn't it be
CREATE PROCEDURE OpgeslagenProcedure2 AS
Ah,
Formatting!
Try
CREATE PROCEDURE OpgeslagenProcedure2
@Isbn char(13) = ''
AS
SET NOCOUNT ON
...
It may be insisting on a default for your parameter
<must declare the scalar variable @isbn>
Formatting!
Try
CREATE PROCEDURE OpgeslagenProcedure2
@Isbn char(13) = ''
AS
SET NOCOUNT ON
...
It may be insisting on a default for your parameter
<must declare the scalar variable @isbn>
ASKER
Yes. The netherlands try it tomorow , but with my old acces i had no troubleshooter at all ..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Unrelated to your problem but:
A. You have SET NOCOUNT ON twice.
B. There is no need for a temporary table
C. Use aliases as in something like this:
A. You have SET NOCOUNT ON twice.
B. There is no need for a temporary table
C. Use aliases as in something like this:
CREATE PROCEDURE OpgeslagenProcedure2
@Isbn char(13)
AS
SET NOCOUNT ON
SELECT DISTINCT
e.ISBN,
e.jaar,
p.Fonds,
e.Titel,
e.Auteur,
e.Voornaam,
e.eindvoorraad,
i.inslagen,
ISNULL(i.inslagen, 2) - ISNULL(e.eindvoorraad, 2) AS verkopen,
t.[verkopen royalty]
FROM dbo.VW_eindvoorraad e
LEFT JOIN dbo.[Tsomverkopen in jaar] t ON e.ISBN = t.ISBN AND e.jaar = t.Jaar
LEFT JOIN dbo.PI_artikel_informatie p ON e.ISBN = p.ISBN
LEFT JOIN dbo.VW_Inslagen i ON e.jaar = i.jaar AND e.ISBN = i.Isbn
WHERE e.ISBN = @isbn
RETURN
ASKER
access 2007 does the trick
i have no time to experiment, maybey later
Thanxs
i have no time to experiment, maybey later
Thanxs
<CREATE PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS....>
This creates it. When you go to edit it, it must be
ALTER PROCEDURE OpgeslagenProcedure2 @Isbn char(13) AS...
Is the problem as simple as that?