problem with the store procedure

Question

0.00/5 (No votes)

See more:

I have incorrect syntax near select in that query.. can any one can sort out my problem

SQL

ALTER PROCEDURE [dbo].[InsertOrder] 
@CustomerID varchar(50),
@OrderDate datetime,
@FirstName varchar(50),
@LastName varchar(50),
@Company varchar(50),
@Address varchar(50),
@Country varchar(50),
@City varchar(50),
@Province varchar(50),
@ZipCode varchar(50),
@Telephone varchar(50),
@Fax varchar(50),
@totalAmount varchar(50)


AS
BEGIN
	INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid) 
	VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount,select userid from users where CustomerID=@CustomerID)
	SELECT @@IDENTITY
END

Posted 24-Nov-11 9:27am

codegeekalpha

Updated 24-Nov-11 10:14am

Add a Solution

5 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sander Rossel · Answer 1 · 2011-11-24T10:40:00

Solution 1

I am assuming all your tables and columns exist.
Not sure if you can't be more specific, but try putting your subquery between brackets, like so.

SQL

INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid)
VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount,
    (select userid from users where CustomerID=@CustomerID)
)
SELECT @@IDENTITY

Posted 24-Nov-11 10:40am

Sander Rossel

Updated 24-Nov-11 10:41am

v2

Comments

codegeekalpha 24-Nov-11 16:50pm

i am getting error subquries are not allowed only scalar functions are allowed

Sander Rossel 24-Nov-11 17:11pm

Your subquery
select userid from users where CustomerID=@CustomerID
can return more than one result. That is the problem.
For example some customer has 3 users, so which userid will it insert?
Make sure your subquery returns only 1 result. I can't really help with that since I don't know your structure and requirements.

codegeekalpha 24-Nov-11 23:40pm

users table and orders table are 1 to many relationship and one userid have only one Customerid .. so ur guesss is wrong.. i think...

Sander Rossel 25-Nov-11 2:16am

Yeah, that is true, but you are querying a userid using a customerid. And multiple users can have the same customerid.
The only value that exists only once in the users table is the Primary Key and possibly any Unique Constraints. Your userid is probably a Primary Key, but you are not asking for a specific userid, you want all rows where the user is from a specific customer, which might be more than one :)

So one order has one user, but multiple orders can have the same user.
One user is from one customer, but multiple users can be from the same customer.

Hope it helps :)

Amir Mahfoozi · Answer 2 · 2011-11-24T18:35:00

How about this one :

SQL

ALTER PROCEDURE [dbo].[InsertOrder] 
@CustomerID varchar(50),
@OrderDate datetime,
@FirstName varchar(50),
@LastName varchar(50),
@Company varchar(50),
@Address varchar(50),
@Country varchar(50),
@City varchar(50),
@Province varchar(50),
@ZipCode varchar(50),
@Telephone varchar(50),
@Fax varchar(50),
@totalAmount varchar(50)
 

AS
BEGIN
 declare @uid int
 select @uid = userid from users where CustomerID=@CustomerID

	INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid) 
	VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount, @uid)
	SELECT @@IDENTITY
END

D K N T H · Answer 3 · 2011-11-24T18:38:00

SQL

INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid)
    VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount,select userid from users where CustomerID=@CustomerID)

based on your query, the select userid from users where CustomerID=@CustomerID can return more than 1 result.

thanks.

srinivas vadepally · Answer 4 · 2011-11-24T18:47:00

Solution 4

Hi farooq,
Go through the following link,
Click here[^]
If you do not get the solution, send me a query.

Posted 24-Nov-11 18:47pm

srinivas vadepally

Rethinaprasath · Answer 5 · 2011-11-24T18:50:00

CREATE PROCEDURE [dbo].[SPStockPurchase]
(
@RefNo varchar(50),
@PDate varchar(50),
@ItemCode varchar(50),
@MedicineName varchar(100),
@BatchNo varchar(50),
@SupplierName varchar(50),
@ManufactureDate varchar(50),
@ExpireDate varchar(50),
@Price varchar(50),
@Quantity varchar(50),
@BasicAmt varchar(50),
@Vat decimal(18,1),
@CST decimal(18,1),
@Discount decimal(18,1),
@Amount varchar(50),
@SellingpricePerUnit varchar(50),
@Sellingprice varchar(50),
@SellingTax decimal(18,1),
@Free varchar(50),
@VatPercentage decimal(18,1),
@CstPercentage decimal(18,1),
@DiscountPercentage decimal(18,1),
@SellingTaxPercentage decimal(18,1)
)

AS
BEGIN
insert into tblPurchase values(@RefNo,@PDate,@ItemCode,@MedicineName,@BatchNo,@SupplierName,
@ManufactureDate,@ExpireDate,@Price,@Quantity,@BasicAmt,@Vat,@CST,
@Discount,@Amount,@SellingpricePerUnit,@Sellingprice,@SellingTax,
@Free,@VatPercentage,@CstPercentage,@DiscountPercentage,
@SellingTaxPercentage)
END

problem with the store procedure

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

problem with the store procedure

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us