PDA

View Full Version : سوال در مورد Stored procedures ؟



assimar42
چهارشنبه 25 اسفند 1389, 08:35 صبح
ایا این Stored procedures درست نوشته شده؟ از نظر syntax؟

ولیerror زیر را می دهد
Incorrect syntax near the keyword 'AS'
--------------------------------------------------------------------------------------------------------
این Stored procedures که من نوشتم

USE [sah]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[darsad]@tari1 char(10),@tari61 char(6)


-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
As
declare @tempcode int(10)
declare @getcode int(10)
declare @getmaxcode int(10)
declare @gettedadA int(10)
declare @gettemptedad int(10)
declare @gettedadB int(10)
declare @gettSarmpkID int(10)
declare @i int(10)
declare @j int(10)
declare @darsad int(10)
declare @gettarikhA nvarchar(10)
declare @gettarikhB nvarchar(10)
AS
BEGIN
set @getmaxcode=(select max(code) from tblsarm)
set @getmaxcode=(@getmaxcode+1)
while @getcode<@getmaxcode BEGIN
set @tempcode =(select count(code) from tblsarm where code=@getcode)
set @gettedadA=(select top 1 tedad FROM tblsarm WHERE code=@getcode ORDER BY tarikh_m DESC)
set @gettedadA=ISNULL( @gettedadA,0)
set @gettarikhA=(select top 1 tarikh_m FROM tblsarm WHERE code=@getcode ORDER BY tarikh_m DESC)
set @j=@tempcode
set @i=1
while @i<@j BEGIN
set @gettedadB=(select top 1 tedad FROM tblsarm WHERE code= @getcode AND tarikh_m< @gettarikhA ORDER BY tarikh_m DESC)
set @gettedadB=ISNUll(@gettedadB,0)
set @gettarikhB=(select top 1 tarikh_m FROM tblsarm WHERE code= @getcode AND tarikh_m< @gettarikhA ORDER BY tarikh_m DESC)
set @darsad=( @gettedadA - @gettedadB )/(@gettedadB)*100
UPDATE tblsarm SET tSarmFNvc1= @darsad WHERE code= @getcode AND tarikh_m= @gettarikhA
set @gettedadA=@gettedadB
set @gettarikhA=@gettarikhB
set @j=@j+1
END
set @darsad=0
UPDATE tblsarm SET tSarmFNvc1= @darsad WHERE code= @getcode AND tarikh_m= @gettarikhA
END
END
GO

BehjatManesh
پنج شنبه 26 اسفند 1389, 09:37 صبح
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[darsad]
@tari1 char(10),
@tari61 char(6)
AS
BEGIN
declare @tempcode int,
@getcode int,
@getmaxcode int,
@gettedadA int,
@gettemptedad int,
@gettedadB int,
@gettSarmpkID int,
@i int,
@j int,
@darsad int,
@gettarikhA nvarchar(10),
@gettarikhB nvarchar(10)



set @getmaxcode=(select max(code) from tblsarm)
set @getmaxcode=(@getmaxcode+1)

while @getcode<@getmaxcode
BEGIN
set @tempcode =(select count(code) from tblsarm where code=@getcode)
set @gettedadA=(select top 1 tedad FROM tblsarm WHERE code=@getcode ORDER BY tarikh_m DESC)
set @gettedadA=ISNULL( @gettedadA,0)
set @gettarikhA=(select top 1 tarikh_m FROM tblsarm WHERE code=@getcode ORDER BY tarikh_m DESC)
set @j=@tempcode
set @i=1
while @i<@j
BEGIN
set @gettedadB=(select top 1 tedad FROM tblsarm WHERE code= @getcode AND tarikh_m< @gettarikhA ORDER BY tarikh_m DESC)
set @gettedadB=ISNUll(@gettedadB,0)
set @gettarikhB=(select top 1 tarikh_m FROM tblsarm WHERE code= @getcode AND tarikh_m< @gettarikhA ORDER BY tarikh_m DESC)
set @darsad=( @gettedadA - @gettedadB )/(@gettedadB)*100
UPDATE tblsarm SET tSarmFNvc1= @darsad WHERE code= @getcode AND tarikh_m= @gettarikhA


set @gettedadA=@gettedadB
set @gettarikhA=@gettarikhB
set @j=@j+1
END
set @darsad=0
UPDATE tblsarm SET tSarmFNvc1= @darsad WHERE code= @getcode AND tarikh_m= @gettarikhA


end
GO

ehsanara
یک شنبه 29 اسفند 1389, 15:57 عصر
-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
As


این رو حذف کن

assimar42
دوشنبه 08 فروردین 1390, 08:37 صبح
باتشکر ازشما دوست عزیز BehjatManesh
من برنامه شما را ران کردم ولی error زیر را داد

Msg 102, Level 15, State 1, Procedure darsad, Line 57
Incorrect syntax near 'end'.

assimar42
دوشنبه 08 فروردین 1390, 08:43 صبح
باتشکر ازشما دوست عزیز ehsanara
من اون قسمت راحذف کردم ولی همان error قبلی راداد

حمیدرضاصادقیان
سه شنبه 09 فروردین 1390, 11:46 صبح
سلام دوست عزیز.
این مورد رو تست کنید.

USE [sah]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[darsad] @tari1 char(10),@tari61 char(6)
As
declare @tempcode int
declare @getcode int
declare @getmaxcode int
declare @gettedadA int
declare @gettemptedad int
declare @gettedadB int
declare @gettSarmpkID int
declare @i int
declare @j int
declare @darsad int
declare @gettarikhA nvarchar(10)
declare @gettarikhB nvarchar(10)

BEGIN
set @getmaxcode=(select Isnull(max(code),0)+1 from tblsarm)
while @getcode<@getmaxcode
BEGIN
set @tempcode =(select count(code) from tblsarm where code=@getcode)
set @gettedadA=(select top 1 tedad FROM tblsarm WHERE code=@getcode ORDER BY tarikh_m DESC)
set @gettedadA=ISNULL( @gettedadA,0)
set @gettarikhA=(select top 1 tarikh_m FROM tblsarm WHERE code=@getcode ORDER BY tarikh_m DESC)
set @j=@tempcode
set @i=1
while @i<@j
BEGIN
set @gettedadB=(select top 1 tedad FROM tblsarm WHERE code= @getcode AND tarikh_m< @gettarikhA ORDER BY tarikh_m DESC)
set @gettedadB=ISNUll(@gettedadB,0)
set @gettarikhB=(select top 1 tarikh_m FROM tblsarm WHERE code= @getcode AND tarikh_m< @gettarikhA ORDER BY tarikh_m DESC)
set @darsad=( @gettedadA - @gettedadB )/(@gettedadB)*100
UPDATE tblsarm SET tSarmFNvc1= @darsad WHERE code= @getcode AND tarikh_m= @gettarikhA
set @gettedadA=@gettedadB
set @gettarikhA=@gettarikhB
set @j=@j+1
END
set @darsad=0
UPDATE tblsarm SET tSarmFNvc1= @darsad WHERE code= @getcode AND tarikh_m= @gettarikhA
END
END
GO