problem in Stored Procedure ,please

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)

problem in Stored Procedure ,please

Author

Topic

yamoo
Starting Member

11 Posts

Posted - 2013-07-27 : 11:43:18

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-27 : 12:01:03

Check your UPDATE-statement. Remove ", orderno = ".

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-27 : 12:09:44

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

shantheguy
Starting Member

22 Posts

Posted - 2013-07-30 : 05:41:34

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

yamoo
Starting Member

11 Posts

Posted - 2013-07-30 : 09:21:26

thanks for all

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 10:23:53

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources