SP insert into

[Guest]

I created an SP where I get the result on the screen when runned as a select
statement but when I tried to run it as a insert into nothing happen in the
SQL2000 database. Permission are granted to DBO on the costunit table and on
the SP.

Any IDEA

Thanks

FRED

here's the select statement that return value in Access2003.adp

ALTER PROCEDURE dbo.S_costunit
AS SELECT SUM(dbo.PKGDET.COST / dbo.PKGDET.SF * 1000) AS costunit,
dbo.PKG.DEST, SUM(dbo.PKGDET.SF) AS SFQTY,
dbo.DELSLIP.ORDERNO AS Orderno, dbo.PKG.DESTDATE,
dbo.DELSLIP.INVOICE, dbo.PKG.LINEID AS lineid
FROM dbo.PKG INNER JOIN
dbo.PKGDET ON dbo.PKG.PKGID = dbo.PKGDET.PKGID INNER
JOIN
dbo.DELSLIP ON dbo.PKG.DEST = dbo.DELSLIP.DSID
GROUP BY dbo.PKG.DEST, dbo.DELSLIP.ORDERNO, dbo.PKG.DESTDATE,
dbo.DELSLIP.INVOICE, dbo.PKG.LINEID

And here's the statement that insert no value into my table

ALTER PROCEDURE dbo.S_costunit
AS INSERT INTO dbo.costunit
(costunit, dest, SFqty, Orderno, Destdate, Invoice,
Lineid)
SELECT SUM(dbo.PKGDET.COST / dbo.PKGDET.SF * 1000) AS costunit,
dbo.PKG.DEST, SUM(dbo.PKGDET.SF) AS SFQTY,
dbo.DELSLIP.ORDERNO AS Orderno, dbo.PKG.DESTDATE,
dbo.DELSLIP.INVOICE, dbo.PKG.LINEID AS lineid
FROM dbo.PKG INNER JOIN
dbo.PKGDET ON dbo.PKG.PKGID = dbo.PKGDET.PKGID INNER
JOIN
dbo.DELSLIP ON dbo.PKG.DEST = dbo.DELSLIP.DSID
GROUP BY dbo.PKG.DEST, dbo.DELSLIP.ORDERNO, dbo.PKG.DESTDATE,
dbo.DELSLIP.INVOICE, dbo.PKG.LINEID

 

[Sylvain Lafontaine]

If you try to remove the SELECT statement and insert values directly into
dbo.costunit with a VALUES (...) statement from the same stored procedure,
what does it give?

Also, what does it gives if you run this in the SQL Query Analyser?

S. L.