Can't get the OUTPUT from my sp! (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

yogaboy -> Can't get the OUTPUT from my sp! (4/2/2005 8:51:42)

Hi, I'm trying to get this stored procedure to work on a mssql2000 box and I can't see why this won't work (it didn't work using ASP so now I'm testing it in straight sql till it's right).

I get the following error message when I call the sp below

Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


I'd really appreciate a bit of help as I am really stuck.
Iain 

CREATE PROCEDURE dbo.uspInsertPersonalInvoice
(
@ClientID smallint = 6,
@InvoiceTotal smallmoney,
@TheScope int OUTPUT
)
AS
 SET NOCOUNT ON
 INSERT INTO dbo.Personal_Invoice
	(DateCreated, ClientID, InvoiceTotal, DateSent)
 	VALUES
	(GETDATE(), @ClientID, @InvoiceTotal, GETDATE())
 SET @TheScope = SCOPE_IDENTITY() 
 SET NOCOUNT OFF


and to call it 
DECLARE @TheID int
exec dbo.uspInsertPersonalInvoice 
@ClientID = 6
, @InvoiceTotal = 257.96
, @TheID OUTPUT 
SELECT @TheID



BeTheBall -> RE: Can't get the OUTPUT from my sp! (4/2/2005 10:05:22)

Disclaimer - I have never used SQL server

Reading the error message, I would wonder if changing the order of things would work. Instead of:

INSERT INTO dbo.Personal_Invoice
(DateCreated, ClientID, InvoiceTotal, DateSent)
VALUES
(GETDATE(), @ClientID, @InvoiceTotal, GETDATE())

This:

INSERT INTO dbo.Personal_Invoice
(DateCreated, DateSent, ClientID, InvoiceTotal)
VALUES
(GETDATE(), GETDATE(), @ClientID, @InvoiceTotal)



yogaboy -> RE: Can't get the OUTPUT from my sp! (4/2/2005 10:21:39)

Thanks Duane, unfortunately it didn't work. I've looked at a lot of other sample code and there appears to be nothing wrong with what I'm doing... but how many times have I thought that! [:)] I don't get any syntax errors when I create the procedure, just when I run it. I'm stumped.

SQL Server really is a lot easier and more powerful than Access, comparably a very nice experience. It's just that now I'm trying much more complicated things!!! [:)]



BeTheBall -> RE: Can't get the OUTPUT from my sp! (4/2/2005 10:31:05)

Wish I had more to offer. My host offers SQL support for about 5$ a month more, I may sign up for at least a year just to wet my feet.



yogaboy -> RE: Can't get the OUTPUT from my sp! (4/2/2005 10:52:29)

The stuff that my clients want really goes far beyond what I can do with Access. The best thing about Server 2000 is user defined functions, it makes manipulating the data move into a different world.

You can use the MSDE (Microsoft Desktop Engine) for free on your local machine - that's where I'm running this particular code. I've got IIS set up and running some ASP to query it. Almost everything is the same as the full server version, just some replication stuff and high-availabilty things are missing - nothing you'll miss if you just want something to play around with and see how it all works. You can download it with the evaluation edition of SQL Server 2000.

I had problems with some of the installations I did, till I did the following as part of the install....

add the following code to a notepad and save it as msde.ini on C: 
[Options]
TARGETDIR="C:\Program Files\Microsoft SQL Server\Mssql$SQL2k\Binn"
DATADIR="C:\Program Files\Microsoft SQL Server\Mssql$SQL2k\Data"
INSTANCENAME=SQL2K


Unpack all the files, and then from a command prompt, change to the MSDE directory and type
Setup /Settings C:\MSDE.INI

It's worked really well ever since.



Spooky -> RE: Can't get the OUTPUT from my sp! (4/2/2005 17:26:30)

Check out the info available here :

http://www.aspfaq.com/show.asp?id=2201



yogaboy -> RE: Can't get the OUTPUT from my sp! (4/3/2005 7:45:08)

Ok, it's fixed - thanks to Spooky and thanks to the author of the article on the end of the link.

Here's the new code - just one or 2 lines different. 
CREATE PROCEDURE dbo.uspInsertPersonalInvoice
(
@ClientID smallint = 6,
@InvoiceTotal smallmoney
)
AS
 SET NOCOUNT ON
DECLARE @TheScope int
 INSERT INTO dbo.Personal_Invoice
        (DateCreated, ClientID, InvoiceTotal, DateSent)
        VALUES
        (GETDATE(), @ClientID, @InvoiceTotal, GETDATE())
 SET @TheScope = SCOPE_IDENTITY() 
SELECT [Returned Id] = @TheScope 
 SET NOCOUNT OFF


and to call it 
exec dbo.uspInsertPersonalInvoice 
@ClientID = 6
, @InvoiceTotal = 257.96


Now, don't ask me why this works! I have a ton of SQL books here (or at least 20 kilos), I've trawled the net, I even went on a SQL Programming course last week (very good it was too) and I've been using the 2 ways, OUTPUT and RETURN, that all of these sources recommended. My code was synctatically correct and everything seemed right - except it didn't work!

Typical[;)]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125