|
| |
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
 |
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
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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)
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
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.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
I don't get any syntax errors when I create the procedure, just when I run it. I'm stumped. 
k¥ 