SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select, insert and delete in T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnDW
Starting Member

Belgium
25 Posts

Posted - 11/29/2013 :  18:10:35  Show Profile  Reply with Quote
Hello,
I want to select columns from a record of a table, insert those columns into another table and delete the record from the table. That all with a stored procedure.
The SP receives from an application @OrderdetailId int

The SP Selects :
(select OrderdetailId,Orderid=@Orderid,Productid=@ProductId,Verkoopprijs=@Verkoopprijs,Korting=@Korting, Tal=@Tal from Orderdetail where OrderdetailId = @OrderdetailId)

The SP inserts into another table Controldetail:
insert into Controldetail(ordertailid,orderid,productid,verkoopprijs,korting,tal) values
(@OrderdetailId,@OrderId,@ProductId,@Verkoopprijs,@Korting,@Tal)

The SP deletes from the table the record:
Delete From Orderdetail where OrderdetailId = @OrderdetailId

I'd tried to put this all together in the SP:
ALTER PROCEDURE [dbo].[DelOrderdetailId]
-- Add the parameters for the stored procedure here
@OrderdetailId int,
@OrderId int,
@ProductId int,
@Verkoopprijs decimal,
@Korting nvarchar(2),
@Tal smallint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
(select OrderdetailId,Orderid=@Orderid,Productid=@ProductId,Verkoopprijs=@Verkoopprijs,Korting=@Korting, Tal=@Tal from Orderdetail where OrderdetailId = @OrderdetailId)
insert into Controldetail(ordertailid,orderid,productid,verkoopprijs,korting,tal) values
(@OrderdetailId,@OrderId,@ProductId,@Verkoopprijs,@Korting,@Tal)
-- Insert statements for procedure here
Delete From Orderdetail where OrderdetailId = @OrderdetailId

But I get the message:
Incorrect syntax near '@OrderdetailId'.

Can someone help me?

John

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/30/2013 :  01:37:58  Show Profile  Reply with Quote
you've an additional unwanted column in select and you're mixing it with assignment statement please remove it.
Also assignment should be variable = value not other way around


ALTER PROCEDURE [dbo].[DelOrderdetailId]
-- Add the parameters for the stored procedure here
@OrderdetailId int,
@OrderId int,
@ProductId int,
@Verkoopprijs decimal,
@Korting nvarchar(2),
@Tal smallint 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select OrderdetailId,@Orderid = Orderid,@ProductId = Productid,@Verkoopprijs=Verkoopprijs,@Korting=Korting, @Tal=Tal from Orderdetail where OrderdetailId = @OrderdetailId

insert into Controldetail(ordertailid,orderid,productid,verkoopprijs,korting,tal) values 
(@OrderdetailId,@OrderId,@ProductId,@Verkoopprijs,@Korting,@Tal)
-- Insert statements for procedure here
Delete From Orderdetail where OrderdetailId = @OrderdetailId


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 11/30/2013 03:04:49
Go to Top of Page

JohnDW
Starting Member

Belgium
25 Posts

Posted - 11/30/2013 :  02:55:03  Show Profile  Reply with Quote
I've worked on it and came to the following SP:

ALTER PROCEDURE [dbo].[DelOrderdetailId]
-- Add the parameters for the stored procedure here
@OrderdetailId int


AS
SET NOCOUNT ON;
declare @OrderId int
declare @ProductId int
declare @Verkoopprijs decimal
declare @Korting nvarchar(2)
declare @Tal smallint


-- Select statements for procedure here
select Orderid=@OrderId, Productid=@ProductId,Verkoopprijs=@Verkoopprijs,Korting=@Korting, Tal=@Tal from Orderdetail where OrderdetailId = @OrderdetailId
-- Insert statements for procedure here

insert into Controldetail(ordertailid,orderid,productid,verkoopprijs,korting,tal) values
(@OrderdetailId,@OrderId,@ProductId,@Verkoopprijs,@Korting,@Tal)
-- Delete statement for procedure here
Delete From Orderdetail where OrderdetailId = @OrderdetailId


It doesn't give an error, but it doesn't insert the values. It deletes the record. So that's ok. But the insert doesn't work.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/30/2013 :  03:02:08  Show Profile  Reply with Quote
quote:
Originally posted by JohnDW

I've worked on it and came to the following SP:

ALTER PROCEDURE [dbo].[DelOrderdetailId]
-- Add the parameters for the stored procedure here
@OrderdetailId int


AS
SET NOCOUNT ON;
declare @OrderId int
declare @ProductId int
declare @Verkoopprijs decimal
declare @Korting nvarchar(2)
declare @Tal smallint


-- Select statements for procedure here
select Orderid=@OrderId, Productid=@ProductId,Verkoopprijs=@Verkoopprijs,Korting=@Korting, Tal=@Tal from Orderdetail where OrderdetailId = @OrderdetailId
-- Insert statements for procedure here

insert into Controldetail(ordertailid,orderid,productid,verkoopprijs,korting,tal) values
(@OrderdetailId,@OrderId,@ProductId,@Verkoopprijs,@Korting,@Tal)
-- Delete statement for procedure here
Delete From Orderdetail where OrderdetailId = @OrderdetailId


It doesn't give an error, but it doesn't insert the values. It deletes the record. So that's ok. But the insert doesn't work.


you're still not using it as i sugested
see my posted code its variable first followed by value

ie
@OrderId = Orderid, @ProductId = Productid etc and not like what you've above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 11/30/2013 03:02:27
Go to Top of Page

JohnDW
Starting Member

Belgium
25 Posts

Posted - 11/30/2013 :  03:12:30  Show Profile  Reply with Quote
muchas gracias!

It works and I'm glad!

Txs!, visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/30/2013 :  03:20:36  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000