|
|
Posted by Sandesh on 10/02/89 11:49
Hello All,
Me saying " has any body come across such error would be
underestimating".
Well I am getting a very peculiar and unique error "Line 1: Incorrect
syntax near 'Actions'."
Explaining you the scene is the following Stored Proc.
This stored proc is execute from a VB code in the .net application as
like: -
{Try
Connection.Init_Variables()
cn.ConnectionString = Connection.gstrConnection
ResDb.ConnectionString = Connection.gresConnection
cn.Open()
With sqlCmd
..Connection = cn
..CommandText = "DSP_Get_Required"
.CommandType = CommandType.StoredProcedure
..Parameters.Add("@ActionId", SqlDbType.Int, 9).Value = ActionID
..Parameters("@ActionId").Direction = ParameterDirection.InputOutput
..Parameters.Add("@PersonID", SqlDbType.Int, 9).Value = PersonID
..Parameters("@PersonID").Direction = ParameterDirection.InputOutput
..Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 1000).Value =
ReturnMessage.ToString
..Parameters("@ReturnMessage").Direction =
ParameterDirection.InputOutput
..Parameters.Add("@Exists", SqlDbType.Bit, 1).Value = Exists
..Parameters("@Exists").Direction = ParameterDirection.InputOutput
..Parameters.Add("@Days", SqlDbType.Int, 9).Value = 0
..Parameters("@Days").Direction = ParameterDirection.InputOutput
..Parameters.Add("@StartDate", SqlDbType.DateTime).Value = Now()
..Parameters("@StartDate").Direction = ParameterDirection.InputOutput
..Parameters.Add("@EndDate", SqlDbType.DateTime).Value = Now()
..Parameters("@EndDate").Direction = ParameterDirection.InputOutput
..Parameters.Add("@OutCome", SqlDbType.VarChar, 20).Value = "Stop"
..Parameters("@OutCome").Direction = ParameterDirection.InputOutput
..Parameters.Add("@Evaluate", SqlDbType.Int, 9).Value = 0
..Parameters("@Evaluate").Direction = ParameterDirection.InputOutput
..Parameters.Add("@DbName", SqlDbType.VarChar, 100).Value =
ResDb.Database.ToString
..Parameters("@DbName").Direction = ParameterDirection.InputOutput
..ExecuteReader(CommandBehavior.Default)
}
On Execution I get the subjected Error "Line 1: Incorrect syntax near
'Actions'."
Any Ideas from your all experience to get away from this error will be
helpful. Look forward to read somebody soon.
Stored Proc:-
{SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =
OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id,
N'ISPROCEDURE') = 1)
DROP PROCEDURE dbo.DSP_Get_Required_ActionS
GO
CREATE PROCEDURE DSP_Get_Required_ActionS
@ActionID INT OUTPUT,
@PersonID INT OUTPUT,
@ReturnMessage Varchar(1000) OUTPUT,
@Exists BIT OUTPUT,
@Days INT OUTPUT,
@StartDate DATETIME OUTPUT,
@EndDate DATETIME OUTPUT,
@OutCome VARCHAR(20) OUTPUT,
@Evaluate INT OUTPUT,
@DbName VARCHAR(100) OUTPUT
AS
SET NOCOUNT ON
--DECLARE @PopulateSQL AS NVarchar(4000)
DECLARE @Rule_ID AS NUMERIC(9)
DECLARE @Curr_ActionSubType AS VARCHAR(20)
DECLARE @Eval_SubType AS VARCHAR(20)
-- DECLARE @OutCome AS VARCHAR(20)
-- DECLARE @Evaluate AS INT
-- DECLARE @Days AS INT
DECLARE @Message AS VARCHAR(1000)
DECLARE @Mandatory AS BIT
-- This is the variable used to interpret the Precedant subtype
DECLARE @Prec_Subtype AS VARCHAR(20)
-- DECLARE @Exists AS BIT --this is supposed to be the deceision maker
variable to be used within the precedant check.
DECLARE @Precedant_SubTypes_Cnt AS INT --This is the variable used to
recordcount the Precedant Subtypes to be checked
DECLARE @Counter AS INT -- Counter used to loop through the Table of
precedant Subtypes.
DECLARE @ROWCOUNT AS INT -- Temporary ROWCOUNT
SET @Counter = 1
--Process to retrive @Curr_ActionSubType Variable
CREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100))
EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @DbName
+'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+
@ActionID+' AND ' +@DbName+'.resadm.action.status =''A''')
SET @Curr_ActionSubType = (Select ActionSubType from
#Curr_ActionSubType)
DROP TABLE #Curr_ActionSubType
--Process to retrive @StartDate Variable
CREATE TABLE #StartDate(StartDate DATETIME)
EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME,
'+@DbName+'.Resadm.Action.DateofAction + '' ''+
'+@DbName+'.Resadm.Action.TimeOfAction)
FROM '+@DbName+'.resadm.action
WHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID +' AND
'+@DbName+'.resadm.action.status =''A'''
)
SET @StartDate = (Select StartDate from #StartDate)
DROP TABLE #StartDate
SET @Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY RULE_ID)
SET @Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERE
Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY
Evaluate_Subtype)
SET @OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY OutCome)
SET @Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY Evaluate)
SET @Days = (SELECT Days FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY Days)
SET @Message = (SELECT Message FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY Message)
SET @Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERE
Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY
Optional_Mandatory_Precedant)
-- create the temporary table for the Subtypes to be evaluated
CREATE TABLE #Preceding_SubTypes_Details
( SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL,
RULE_ID NUMERIC(9),
SubType VARCHAR(20),
)
-- insert the current subtype that needs to be evaluated.
INSERT INTO #Preceding_SubTypes_Details
SELECT Rule_ID, Prec_Subtype
FROM Rules_Details
WHERE Rule_ID = @Rule_ID
-- create the History table for Reference
--sk/* Modified to accomodatethe need ot dynamic database name to
retrive from the different Resman databases
CREATE TABLE #dsHistory ( ActionID INT,
PersonID INT,
ActionTypeID VARCHAR(1),
DateofAction DATETIME,
Status VARCHAR(1),
Subtype VARCHAR(6),
ActionTypeName VARCHAR(30),
ActionSubtypeID VARCHAR(6),
EffectCandidateCurrentState VARCHAR(10),
TaxCode VARCHAR(6)
)
EXEC ('INSERT INTO #dsHistory SELECT
'+@DbName+'.Resadm.Action.ActionID, '
+ @DbName+'.Resadm.Action.PersonID,
'+@DbName+'.Resadm.Action.ActionTypeID, '
+ 'CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+
'+@DbName+'.Resadm.Action.TimeOfAction)DateofAction, '
+ @DbName+'.Resadm.Action.Status, '+@DbName+'.Resadm.Action.Subtype, '
+ @DbName+'.ResAdm.Action_Types.ActionTypeName,
'+@DbName+'.Resadm.Action_subtypes.ActionSubtypeID, '
+ @DbName+'.Resadm.Action_subtypes.EffectCandidateCurrentState,
'+@DbName+'.Resadm.Person.TaxCode '
+ ' FROM '+@DbName+'.Resadm.Action '
+ ' INNER JOIN '+@DbName+'.ResAdm.Action_Types WITH(NOLOCK) '
+ ' ON '+@DbName+'.ResAdm.Action_Types.ActionTypeID =
'+@DbName+'.Resadm.Action.ActionTypeID '
+ ' INNER JOIN '+@DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) '
+ ' ON '+@DbName+'.Resadm.Action.subtype =
'+@DbName+'.ResAdm.Action_SubTypes.actionsubtypeid '
+ ' INNER JOIN '+@DbName+'.Resadm.Person WITH(NOLOCK) '
+ ' ON '+@DbName+'.Resadm.Person.PersonID =
'+@DbName+'.Resadm.Action.PersonID '
+ ' WHERE '+@DbName+'.Resadm.Action.actionID <>
CONVERT(VARCHAR,'+@ActionID+')'
+ ' AND '+@DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@PersonID+')'
+ ' AND '+@DbName+'.Resadm.Action.Status =''A'' '
+ 'AND (CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction +''
''+ '+@DbName+'.Resadm.Action.TimeOfAction) > '
+ ' ISNULL(( SELECT
MAX(CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+ '
+ @DbName+'.Resadm.Action.TimeOfAction)) '
+ ' FROM '+@DbName+'.Resadm.Action '
+ ' WHERE ('+@DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@PersonID+')) AND '
+ ' ('+@DbName+'.Resadm.Action.Subtype =''ZERO'') '
+ ' AND ('+@DbName+'.Resadm.Action.Status=''A'')),0)) '
+ ' ORDER BY CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction +
'' ''+ '+@DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')
--sk*/
SET @EndDate = (SELECT ISNULL((SELECT DateOfAction
FROM #dsHistory
WHERE SubType = @Eval_SubType), getdate()))
-- set the rowcount to retrieve the number of check to be carried out
SET @Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM
#Preceding_SubTypes_Details)
WHILE @Counter <= @Precedant_SubTypes_Cnt
BEGIN
SET @Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_Details
WHERE SubTypes_LIST_ID = @Counter)
SET @ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype =
@Prec_Subtype)
IF @ROWCOUNT > 0
BEGIN
SET @Exists = 1
END
IF @ROWCOUNT = 0
BEGIN
IF @Mandatory = 1
BEGIN
SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; '
SET @Counter = @Precedant_SubTypes_Cnt
SET @Exists = 0
END
ELSE IF @Mandatory = 0
BEGIN
SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; '
SET @Exists = @Exists
END
END
SET @Counter = @Counter+1
END
IF @Exists = 0
BEGIN
EXEC(
' UPDATE '+@DbName+'.Resadm.Action '
+ ' SET '+@DbName+'.Resadm.Action.Status = ''I'' '
+ ' WHERE '+@DbName+'.Resadm.Action.ActionID = '+@ActionID+' SET
@ReturnMessage = '+@Message
)
END
ELSE
IF @Exists = 1
BEGIN
SET @ReturnMessage = @Message
END
IF @Rule_ID = Null
BEGIN
SET @ReturnMessage = 'Validation Rule Not Present'
END
-- Select 'Exist value : ', @Exists, 'Return message is : ',
@ReturnMessage
DROP TABLE #Preceding_SubTypes_Details
DROP TABLE #dshistory
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO}
Navigation:
[Reply to this message]
|