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)
 problem in Stored Procedure ,please
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yamoo
Starting Member

Egypt
11 Posts

Posted - 07/27/2013 :  11:43:18  Show Profile  Reply with Quote
CREATE PROCEDURE dbo.manageorders

(
@check nchar(1),
@orderno nvarchar(3),
@orderdate datetime=null,
@shipname nvarchar(60)=null,
@shipcity nvarchar(50)=null,
@shiparea nvarchar(60)=null,
@shipaddress nvarchar(150)=null,
@member nvarchar(30)=null

)
AS
if @check='a' begin
INSERT INTO orders
(orderno, orderdate, shipname, shipcity, shiparea, shipaddress, member)
VALUES (@orderno,@orderdate,@shipname,@shipcity,@shiparea,@shipaddress,@member)
end
if @check='u'begin
UPDATE orders
SET orderdate = @orderdate, shipname = @shipname, shipcity = @shipcity, shiparea = @shiparea, shipaddress = @shipaddress, member = @member,
orderno =
WHERE (orderno = @orderno)
end
if @check='d' begin
DELETE FROM orders
WHERE (orderno = @orderno)
end
RETURN





when i trying save it , this message appear "Incorrect syntax near the keyword 'WHERE'"



thanks

Edited by - yamoo on 07/27/2013 11:55:25

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/27/2013 :  12:01:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Check your UPDATE-statement. Remove ", orderno = ".



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/27/2013 :  12:09:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or use the MERGE operator?
CREATE PROCEDURE dbo.ManageOrders
(
	@Check NCHAR(1),
	@OrderNo NVARCHAR(3),
	@OrderDate DATETIME = NULL,
	@ShipName NVARCHAR(60) = NULL,
	@ShipCity NVARCHAR(50) = NULL,
	@ShipArea NVARCHAR(60) = NULL,
	@ShipAddress NVARCHAR(150) = NULL,
	@Member NVARCHAR(30) = NULL
)	
AS

SET NOCOUNT ON

MERGE	dbo.Orders AS tgt
USING	(
		VALUES	(@Check, @OrderNo, @OrderDate, @ShipName, @ShipCity, @ShipArea, @ShipAddress, @Member)
	) AS src([Check], OrderNo, OrderDate, ShipName, ShipCity, ShipArea, ShipAddress, Member) ON src.OrderNo = tgt.OrderNo
WHEN	MATCHED AND src.[Check] = 'u'
		THEN	UPDATE
			SET	tgt.OrderDate = src.OrderDate,
				tgt.ShipName = src.ShipName,
				tgt.ShipCity = src.ShipCity, 
				tgt.ShipArea = src.ShipArea, 
				tgt.ShipAddress = src.ShipAddress, 
				tgt.Member = src.Member
WHEN	MATCHED AND src.[Check] = 'd'
		THEN	DELETE
WHEN	NOT MATCHED BY TARGET AND src.[Check] = 'a'
		THEN	INSERT	(
					OrderNo, 
					OrderDate, 
					ShipName, 
					ShipCity, 
					ShipArea, 

					ShipAddress, 
					Member
				)
			VALUES	(
					src.OrderNo, 
					src.OrderDate, 
					src.ShipName, 
					src.ShipCity, 
					src.ShipArea, 
					src.ShipAddress, 
					src.Member
				);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

shantheguy
Starting Member

India
22 Posts

Posted - 07/30/2013 :  05:41:34  Show Profile  Reply with Quote
Try this

CREATE PROCEDURE dbo.manageorders

(
@check nchar(1),
@orderno nvarchar(3),
@orderdate datetime=null,
@shipname nvarchar(60)=null,
@shipcity nvarchar(50)=null,
@shiparea nvarchar(60)=null,
@shipaddress nvarchar(150)=null,
@member nvarchar(30)=null

)
AS
if @check='a' begin
INSERT INTO orders
(orderno, orderdate, shipname, shipcity, shiparea, shipaddress, member)
VALUES (@orderno,@orderdate,@shipname,@shipcity,@shiparea,@shipaddress,@member)
end
if @check='u'begin
UPDATE orders
SET orderdate = @orderdate, shipname = @shipname, shipcity = @shipcity, shiparea = @shiparea, shipaddress = @shipaddress, member = @member
WHERE (orderno = @orderno)
end
if @check='d' begin
DELETE FROM orders
WHERE (orderno = @orderno)
end
RETURN
Go to Top of Page

yamoo
Starting Member

Egypt
11 Posts

Posted - 07/30/2013 :  09:21:26  Show Profile  Reply with Quote
thanks for all
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/28/2013 :  10:23:53  Show Profile  Reply with Quote
It would be better to use TRANSACTION and TRY CATCH blocks for this kind of SP's
This may work fine for you...


CREATE PROCEDURE dbo.manageorders

(
@check nchar(1),
@orderno nvarchar(3),
@orderdate datetime=null,
@shipname nvarchar(60)=null,
@shipcity nvarchar(50)=null,
@shiparea nvarchar(60)=null,
@shipaddress nvarchar(150)=null,
@member nvarchar(30)=null

)
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION

IF@check='a'
BEGIN
INSERT INTO orders
(orderno, orderdate, shipname, shipcity, shiparea, shipaddress, member)
VALUES (@orderno,@orderdate,@shipname,@shipcity,@shiparea,@shipaddress,@member)
END
IF @check='u'
BEGIN
UPDATE
orders
SET orderdate = @orderdate, shipname = @shipname, shipcity = @shipcity, shiparea = @shiparea, shipaddress = @shipaddress,
member = @member
WHERE (orderno = @orderno)
END

IF @check='d'
BEGIN
DELETE
FROM orders
WHERE
(orderno = @orderno)
END
COMMIT TRANSACTION
END TRY

BEGIN CATCH
@errorid = ERROR_NUMBER(),
@errormessage = ERROR_MESSAGE()
END CATCH


P.Kameswara rao
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.09 seconds. Powered By: Snitz Forums 2000