insert values into specific column

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 2000 Forums

Transact-SQL (2000)

insert values into specific column

Author

Topic

rutendo
Starting Member

1 Post

Posted - 2008-04-30 : 05:30:07

i`m having trouble to insert a value into a table before updating my records for same table. the table needs to ensure that before update is complete, an endDate is entered for old stock before new dates are entered for new stock with same id. check my procedure code below

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'sp_PriceUpDate')
DROP PROCEDURE sp_PriceUpDate
GO
USE Consultancy
GO
CREATE Procedure sp_PriceUpDate
@olddate smalldatetime,
@productid INT,
@startdate smalldatetime,
@endDate smalldatetime,
@price MONEY
AS
BEGIN
FOR SELECT FROM PRICE
WHERE PRICE.prodID = @prodID AND PRICE.endDate IS NULL
BEGIN
INSERT PRICE(endDate)
VALUES (@olddate)
INSERT PRICE(prodID, startDate, endDate, Price)
VALUES (@productid, @startdate, @endDate, @price)
GO.

this code give me error of Incorrect syntax near the keyword 'FOR'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 05:48:21

Can you post what you are trying to achieve here? You cant use FOR clause like this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-30 : 08:52:29

<<
FOR SELECT FROM PRICE
WHERE PRICE.prodID = @prodID AND PRICE.endDate IS NULL
>>

What do you want to select?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-01 : 00:24:26

I think this is what you need:-

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'sp_PriceUpDate')
DROP PROCEDURE sp_PriceUpDate
GO
USE Consultancy
GO
CREATE Procedure sp_PriceUpDate
@olddate smalldatetime,
@productid INT,
@startdate smalldatetime,
@endDate smalldatetime,
@price MONEY
AS
BEGIN
UPDATE PRICE
SET endDate=@olddate
WHERE prodID = @prodID 
AND endDate IS NULL

INSERT PRICE(prodID, startDate, endDate, Price)
VALUES (@productid, @startdate, @endDate, @price)
GO.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources