variable must not be combined with data-retrieval operations.
-
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROC zCM_SP_GETSUBZCMSBAA(
@zcmSBA_V_SUBSTATIONID varchar(15),
@zcmSBA_V_WONO varchar(6),
@zcmSBA_N_JONO numeric,
@zcmSBA_I_COSTTYPE Int
)
as
declare @err int
DECLARE @retItems VARCHAR(1000)
DECLARE @retAllocAmts VARCHAR(1000)
SET @retItems =''
SET @retAllocAmts=''
SELECT
zcmSBA_V_TRXNO [TRX No],
zcmSBA_V_TRXDESC [Description],
zcmSBA_D_TRXDATE [Date],
zcmSBA_N_ACTUALAMT [Actual Amount],
A.zcmSBA_V_SUBSTATIONID=B.zcmSBA_V_SUBSTATIONID ANDA.zcmSBA_V_WONO=B.zcmSBA_V_WONO AND
A.zcmSBA_N_JONO=B.zcmSBA_N_JONO AND
A.zcmSBA_V_TRXNO=B.zcmSBA_V_TRXNO
)
FROM
ZCM_SBA B
WHERE
zcmSBA_V_SUBSTATIONID = @zcmSBA_V_SUBSTATIONID and
zcmSBA_V_WONO = @zcmSBA_V_WONO and
zcmSBA_N_JONO= @zcmSBA_N_JONO
set @err =@@error
if @err<>0
return 1
else
return 0
IT GIVES
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
THIS ERROR
PLZ SUGGEST
-
- Your problem, I believe, is with the following section of code...
- A.zcmSBA_V_SUBSTATIONID=B.zcmSBA_V_SUBSTATIONID AND
A.zcmSBA_V_WONO=B.zcmSBA_V_WONO AND
A.zcmSBA_N_JONO=B.zcmSBA_N_JONO AND
A.zcmSBA_V_TRXNO=B.zcmSBA_V_TRXNO
- Looks more like part of a WHERE clause... anyway, THAT's what is being interpreted as "assigns a value to a variable"... it does not follow the rules for "data-retrieval operations". If that code is necessary for something, you should turn it into a CASE statement.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
The syntax of the whole query is wrong.
No join operators and no "AND" kaywords are allowed in the SELECT clause.
In addition there is no table in the FROM clause which has the alias A.
In the SELECT clause there is a ")" which has no corresponding "("
So reformulate your query as a whole
Bye
Gabor -
Get both your query output and return success with @@error as a parameter output:
SET
QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROC CM_SP_GETSUBZCMSBAA
@zcmSBA_V_SUBSTATIONID varchar(15),
@zcmSBA_V_WONO varchar(6),
@zcmSBA_N_JONO numeric,
@zcmSBA_I_COSTTYPE Integer,
@err integer OUTPUT
AS
DECLARE @err int,
@retItems VARCHAR(1000),
@retAllocAmts VARCHAR(1000)
SET @retItems ='',
@retAllocAmts=''
SELECT
zcmSBA_V_TRXNO [TRX No],
zcmSBA_V_TRXDESC [Description],
zcmSBA_D_TRXDATE [Date],
zcmSBA_N_ACTUALAMT [Actual Amount],
FROM ZCM_SBA
WHERE zcmSBA_V_SUBSTATIONID = @zcmSBA_V_SUBSTATIONID
and zcmSBA_V_WONO = @zcmSBA_V_WONO
and zcmSBA_N_JONO= @zcmSBA_N_JONO
set @err = @@error
Regards,Coach James
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply