select, insert and delete in T-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select, insert and delete in T-SQL

Author

Topic

JohnDW
Starting Member

45 Posts

Posted - 2013-11-29 : 18:10:35

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

52326 Posts

Posted - 2013-11-30 : 01:37:58

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

JohnDW
Starting Member

45 Posts

Posted - 2013-11-30 : 02:55:03

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 03:02:08

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

JohnDW
Starting Member

45 Posts

Posted - 2013-11-30 : 03:12:30

muchas gracias!

It works and I'm glad!

Txs!, visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 03:20:36

you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources