Re: Stored procedure syntax problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,

Simple mistake but surprisingly easy to over look. When ever you use
variables in Transac SQL you need to prefix it with the '@' symbol so the
select statement will become:

Select Customer.FirstName, Products.ProductID, @OrderTotal

It is not however, case sensitive it is just the fact it's missing the @.

Another problem you will be having once that is fixed is the declaration of
the decimal. It differs from the other type declarations. The other tryes
require you to set the size of the variable. For example @CartID char(36)
gives you a char with 36 bytes.

The decimal in your database takes up 9 bytes (or whatever you set it to)
however, when you declare the variable you need to specify the persition
(number of intergers) and the scale (number of decimal places) so:

Create Procedure SP_NewOrder (@CartID char(36), @CustomerID varchar(50),
@OrderTotal decimal(18,4) )

AS ...

Would give you a decimal with 4 decimal places.

Hope this helps

- Mike

---------------------------------------------------------------------------------
<a href="http://www.cogitar.net";> Cogitar Software. (http://www.cogitar.net)
</a>
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://shop-dominion.com (senery landscape pictur gallery)
---------------------------------------------------------------------------------

"martinharvey via DotNetMonster.com" <u14945@uwe> wrote in message
news:583d793a0b56e@xxxxxx
>I would be very grateful if someone could help me with a stored procedure
> syntax problem
>
> I want to insert the value "OrderTotal" into databasetable("Newtable")
> column
> "OrderTotal"
> (money (8)). The value can be returned from the page (Dim amount As
> Decimal =
> ShoppingCart.GetTotal() totalAmountLabel.Text =
> String.Format("{0:c}",amount)
> or returned by the function "ShoppingCart.GetTotal".
>
> This is the syntax i have tried, but I get the error message "Invalid
> column
> name OrderTotal"
>
> CREATE PROCEDURE SP_NewOrder
> (@CartID char (36),@CustomerID Varchar (50),@OrderTotal decimal(8))
>
> AS
>
> INSERT INTO NewTable (FirstName,ProductID,OrderTotal)
>
> SELECT Customer.FirstName,Products.ProductID,Ordertotal
>
> From Customer,Products Join ShoppingCart ON Products.ProductID
> =ShoppingCart.
> ProductID
> WHERE Customer.CustomerID=@CustomerID AND ShoppingCart.CartID = @CartID
> GO
>
> Can anyone tell me where i am going wrong
>
> many thanks
>
> martin
>
> --
> Message posted via DotNetMonster.com
> http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/200512/1


.

Relevant Pages

  • Stored procedure syntax problem
    ... I would be very grateful if someone could help me with a stored procedure ... This is the syntax i have tried, but I get the error message "Invalid column ... >From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: using strict
    ... RESPONSE FROM DEREK: I did notice this after you pointed it out, ... the error message should point you to the line where ... There is no declaration of the $line variable. ... so use strict disallows them knowing that if you really ...
    (perl.beginners)
  • Re: VBA Timer
    ... used in the TMR sub. ... Always quote the exact text of the error message; ... declaration from Shyam's page. ... Public Declare Sub Sleep Lib "kernel32" ...
    (microsoft.public.powerpoint)
  • Re: SOCKET problem
    ... reason for the error message is that you have a statement preceding ... the declaration; a compiler that implements C90 or C95 rather than C99 ...
    (comp.lang.c)
  • Re: Shadowing a parameter
    ... > The real question, though, is why the heck can't the error message be ... If some thing can be parsed as a declaration it is. ... The error you got was nothing to do with not knowing how to invoke ...
    (comp.lang.cpp)