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' beginINSERT INTO orders (orderno, orderdate, shipname, shipcity, shiparea, shipaddress, member)VALUES (@orderno,@orderdate,@shipname,@shipcity,@shiparea,@shipaddress,@member) end if @check='u'beginUPDATE ordersSET orderdate = @orderdate, shipname = @shipname, shipcity = @shipcity, shiparea = @shiparea, shipaddress = @shipaddress, member = @member, orderno =WHERE (orderno = @orderno) end if @check='d' beginDELETE FROM ordersWHERE (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) ASSET NOCOUNT ONMERGE dbo.Orders AS tgtUSING ( VALUES (@Check, @OrderNo, @OrderDate, @ShipName, @ShipCity, @ShipArea, @ShipAddress, @Member) ) AS src([Check], OrderNo, OrderDate, ShipName, ShipCity, ShipArea, ShipAddress, Member) ON src.OrderNo = tgt.OrderNoWHEN 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.MemberWHEN MATCHED AND src.[Check] = 'd' THEN DELETEWHEN 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 thisCREATE 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) ASif @check='a' beginINSERT INTO orders(orderno, orderdate, shipname, shipcity, shiparea, shipaddress, member)VALUES (@orderno,@orderdate,@shipname,@shipcity,@shiparea,@shipaddress,@member)endif @check='u'beginUPDATE ordersSET orderdate = @orderdate, shipname = @shipname, shipcity = @shipcity, shiparea = @shiparea, shipaddress = @shipaddress, member = @memberWHERE (orderno = @orderno)endif @check='d' beginDELETE FROM ordersWHERE (orderno = @orderno)endRETURN |
|
|
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'sThis 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) ASSET 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 |
|
|
|
|
|