Why do I have to declare these stored proc variables multiple time

microsoft.public.sqlserver.programming

12 May 2005 1:00 PM

Joe Palm

The following stored procedure works perfectly, but it's my first attempt at
writing one that generates and executes a dynamic SQL call to the DB and I'm
a little annoyed that I had to declare variables multiple times.
The way it is now, if I remove some declarations ("remit_vendor_out", for
e.g.), it won't pass the syntax checker. But if I remove the other
declaration of this variable, it errs out at run-time.
Can someone please fill me in on how I should change the stored proc so
variables are only declared once (as per the examples I tried to follow from
the MSFT KB), and it's thus less verbose and easier to maintain?

Thanks.

Here's the code:

CREATE PROCEDURE SSP_GET_PMT_INFO3
@bank_setid char(5),
@bank_cd char(5),
@bank_acct_key char(4),
@pymnt_id_ref char(10),
@remit_vendor char(10) OUTPUT,
@pmt_day int OUTPUT,
@pmt_month int OUTPUT,
@pmt_year int OUTPUT,
@bus_unit char(5) OUTPUT,
@voucher_id char(8) OUTPUT,
@sched_pay_day int OUTPUT,
@sched_pay_month int OUTPUT,
@sched_pay_year int OUTPUT,
@pymnt_cnt int OUTPUT,
@pymnt_id char(10) OUTPUT,
@eft_func_code char(6) OUTPUT,
@agreement_id char(15) OUTPUT,
@claim_id char(13) OUTPUT,
@loan_num char(15) OUTPUT,
@certificate_num char(15) OUTPUT,
@vendor_name_short char(11) OUTPUT,
@vendor_class char(1) OUTPUT,
@ret_cd char(1) OUTPUT
AS
BEGIN

DECLARE @err_cd smallint,
@rowcount int,
@error int,
@SqlString nvarchar(4000),
@ParmDefinition nvarchar(4000),
@bank_setid_prm char(5),
@bank_cd_prm char(5),
@bank_acct_key_prm char(4),
@pymnt_id_ref_prm char(10),
@remit_vendor_out char(10),
@pmt_day_out int,
@pmt_month_out int,
@pmt_year_out int,
@bus_unit_out char(5),
@voucher_id_out char(8),
@sched_pay_day_out int,
@sched_pay_month_out int,
@sched_pay_year_out int,
@pymnt_cnt_out int,
@pymnt_id_out char(10),
@eft_func_code_out char(6),
@agreement_id_out char(15),
@claim_id_out char(13),
@loan_num_out char(15),
@certificate_num_out char(15),
@vendor_name_short_out char(11),
@vendor_class_out char(1)

SET @SqlString = N'SELECT @remit_vendor_out = A.REMIT_VENDOR
    , @pmt_day_out = DAY(A.PYMNT_DT)
    , @pmt_month_out = MONTH(A.PYMNT_DT)
    , @pmt_year_out = YEAR(A.PYMNT_DT)
    , @bus_unit_out = B.BUSINESS_UNIT
    , @voucher_id_out = B.VOUCHER_ID
    , @sched_pay_day_out = DAY(B.SCHEDULED_PAY_DT)
    , @sched_pay_month_out = MONTH(B.SCHEDULED_PAY_DT)
    , @sched_pay_year_out = YEAR(B.SCHEDULED_PAY_DT)
    , @pymnt_cnt_out = B.PYMNT_CNT
    , @pymnt_id_out = B.PYMNT_ID
    , @eft_func_code_out = E.YX_EFT_PROD_FUNC
    , @agreement_id_out = F.YX_AGREEMENT_ID
    , @claim_id_out = F.YX_CLAIM_ID
    , @loan_num_out = F.YX_LOAN_NUM
    , @certificate_num_out = F.YX_CERTIFICATE_NUM
    , @vendor_name_short_out = SUBSTRING(G.VENDOR_NAME_SHORT,1,11)
    , @vendor_class_out = G.VENDOR_CLASS
    FROM dbo.PS_PAYMENT_TBL A
    , dbo.PS_PYMNT_VCHR_XREF B
    , dbo.PS_YX_PYMNT_VCHR E
    , dbo.PS_YX_VOUCHER F
    , dbo.PS_VENDOR G
    WHERE A.BANK_SETID = @bank_setid_prm
    AND A.BANK_CD = @bank_cd_prm
    AND A.BANK_ACCT_KEY = @bank_acct_key_prm
    AND A.PYMNT_ID_REF = @pymnt_id_ref_prm
    AND A.PYMNT_METHOD = ''EFT''
    AND B.PYMNT_ID = A.PYMNT_ID
    AND B.BANK_ACCT_KEY = A.BANK_ACCT_KEY
    AND B.BANK_CD = A.BANK_CD
    AND B.BANK_SETID = A.BANK_SETID
    AND B.PYMNT_CNT = (SELECT MIN(C.PYMNT_CNT)
    FROM dbo.PS_PYMNT_VCHR_XREF C
    WHERE C.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND C.VOUCHER_ID = B.VOUCHER_ID
    AND C.PYMNT_ID = B.PYMNT_ID
    AND C.BANK_SETID = B.BANK_SETID
    AND C.BANK_CD = B.BANK_CD
    AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY)
    AND B.VOUCHER_ID = (SELECT MIN(VOUCHER_ID)
    FROM dbo.PS_PYMNT_VCHR_XREF D
    WHERE D.PYMNT_ID = A.PYMNT_ID
    AND D.BANK_ACCT_KEY = A.BANK_ACCT_KEY
    AND D.BANK_CD = A.BANK_CD
    AND D.BANK_SETID = A.BANK_SETID)
    AND E.VOUCHER_ID = B.VOUCHER_ID
    AND E.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND E.PYMNT_CNT = B.PYMNT_CNT
    AND F.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND F.VOUCHER_ID = B.VOUCHER_ID
    AND G.SETID = A.REMIT_SETID
    AND G.VENDOR_ID = A.REMIT_VENDOR'

SET @ParmDefinition =
N'@bank_setid_prm char(5),
@bank_cd_prm char(5),
@bank_acct_key_prm char(4),
@pymnt_id_ref_prm char(10),
@remit_vendor_out char(10) OUTPUT,
@pmt_day_out int OUTPUT,
@pmt_month_out int OUTPUT,
@pmt_year_out int OUTPUT,
@bus_unit_out char(5) OUTPUT,
@voucher_id_out char(8) OUTPUT,
@sched_pay_day_out int OUTPUT,
@sched_pay_month_out int OUTPUT,
@sched_pay_year_out int OUTPUT,
@pymnt_cnt_out int OUTPUT,
@pymnt_id_out char(10) OUTPUT,
@eft_func_code_out char(6) OUTPUT,
@agreement_id_out char(15) OUTPUT,
@claim_id_out char(13) OUTPUT,
@loan_num_out char(15) OUTPUT,
@certificate_num_out char(15) OUTPUT,
@vendor_name_short_out char(11) OUTPUT,
@vendor_class_out char(1) OUTPUT'

SET @ret_cd = 0

Set @bank_setid_prm = @bank_setid
Set @bank_cd_prm = @bank_cd
Set @bank_acct_key_prm = @bank_acct_key
Set @pymnt_id_ref_prm = @pymnt_id_ref

EXECUTE sp_executesql
@SqlString,
@ParmDefinition,
@bank_setid_prm,
@bank_cd_prm,
@bank_acct_key_prm,
@pymnt_id_ref_prm,
@remit_vendor_out = @remit_vendor_out OUTPUT,
@pmt_day_out = @pmt_day_out OUTPUT,
@pmt_month_out = @pmt_month_out OUTPUT,
@pmt_year_out = @pmt_year_out OUTPUT,
@bus_unit_out = @bus_unit_out OUTPUT,
@voucher_id_out = @voucher_id_out OUTPUT,
@sched_pay_day_out = @sched_pay_day_out OUTPUT,
@sched_pay_month_out = @sched_pay_month_out OUTPUT,
@sched_pay_year_out = @sched_pay_year_out OUTPUT,
@pymnt_cnt_out = @pymnt_cnt_out OUTPUT,
@pymnt_id_out = @pymnt_id_out OUTPUT,
@eft_func_code_out = @eft_func_code_out OUTPUT,
@agreement_id_out = @agreement_id_out OUTPUT,
@claim_id_out = @claim_id_out OUTPUT,
@loan_num_out = @loan_num_out OUTPUT,
@certificate_num_out = @certificate_num_out OUTPUT,
@vendor_name_short_out = @vendor_name_short_out OUTPUT,
@vendor_class_out = @vendor_class_out OUTPUT

SELECT @remit_vendor_out, @pmt_day_out, @pmt_month_out, @pmt_year_out,
@bus_unit_out, @voucher_id_out, @sched_pay_day_out,
    @sched_pay_month_out, @sched_pay_year_out, @pymnt_cnt_out, @pymnt_id_out,
@eft_func_code_out, @agreement_id_out,
    @claim_id_out, @loan_num_out, @certificate_num_out, @vendor_name_short_out,
@vendor_class_out

Select @error=@@error, @rowcount=@@rowcount

IF @error <> 0
BEGIN
    RAISERROR('Read of database failed ',16,1)
    SET @ret_cd = 1
    RETURN 1
END

IF @rowcount = 1
BEGIN
    SET @ret_cd = 0
    RETURN 0
END

IF @rowcount > 1
BEGIN
    RAISERROR('Multiple rows returned - DB read failed ',16,1)
    SET @ret_cd = 811
    RETURN 1
END
ELSE
BEGIN
    RAISERROR('No rows returned - DB read failed ',16,1)
    SET @ret_cd = 100
    RETURN 1
END

RETURN 0

END
GO

--
Joe Palm
Senior Technical Developer
Madison, WI

12 May 2005 1:16 PM

John Bell

Hi

There does not seem to be anything dynamic in this SQL!!!

You can use the stored procedure's parameters as the values for the
parameters used in the sp_executeSQL statement.
e.g.
@remit_vendor_out = @remit_vendor OUTPUT,
so there is no need to redeclare them within the procedure.

As you are using output parameters there should be no need to return a
record set. This is not using the output parameters for the stored procedure.

SELECT @remit_vendor_out, @pmt_day_out, @pmt_month_out, @pmt_year_out,
@bus_unit_out, @voucher_id_out, @sched_pay_day_out,
@sched_pay_month_out, @sched_pay_year_out, @pymnt_cnt_out, @pymnt_id_out,
@eft_func_code_out, @agreement_id_out,
@claim_id_out, @loan_num_out, @certificate_num_out, @vendor_name_short_out,
@vendor_class_out

You may also want to check the status returned by sp_executesql. Your checks
on @@ROWCOUNT does not seem to be correct.

John

Show quote

"Joe Palm" wrote:

> The following stored procedure works perfectly, but it's my first attempt at
> writing one that generates and executes a dynamic SQL call to the DB and I'm
> a little annoyed that I had to declare variables multiple times.
> The way it is now, if I remove some declarations ("remit_vendor_out", for
> e.g.), it won't pass the syntax checker. But if I remove the other
> declaration of this variable, it errs out at run-time.
> Can someone please fill me in on how I should change the stored proc so
> variables are only declared once (as per the examples I tried to follow from
> the MSFT KB), and it's thus less verbose and easier to maintain?
>
> Thanks.
>
> Here's the code:
>
> CREATE PROCEDURE SSP_GET_PMT_INFO3
> @bank_setid char(5),
> @bank_cd char(5),
> @bank_acct_key char(4),
> @pymnt_id_ref char(10),
> @remit_vendor char(10) OUTPUT,
> @pmt_day int OUTPUT,
> @pmt_month int OUTPUT,
> @pmt_year int OUTPUT,
> @bus_unit char(5) OUTPUT,
> @voucher_id char(8) OUTPUT,
> @sched_pay_day int OUTPUT,
> @sched_pay_month int OUTPUT,
> @sched_pay_year int OUTPUT,
> @pymnt_cnt int OUTPUT,
> @pymnt_id char(10) OUTPUT,
> @eft_func_code char(6) OUTPUT,
> @agreement_id char(15) OUTPUT,
> @claim_id char(13) OUTPUT,
> @loan_num char(15) OUTPUT,
> @certificate_num char(15) OUTPUT,
> @vendor_name_short char(11) OUTPUT,
> @vendor_class char(1) OUTPUT,
> @ret_cd char(1) OUTPUT
> AS
> BEGIN
>
> DECLARE @err_cd smallint,
> @rowcount int,
> @error int,
> @SqlString nvarchar(4000),
> @ParmDefinition nvarchar(4000),
> @bank_setid_prm char(5),
> @bank_cd_prm char(5),
> @bank_acct_key_prm char(4),
> @pymnt_id_ref_prm char(10),
> @remit_vendor_out char(10),
> @pmt_day_out int,
> @pmt_month_out int,
> @pmt_year_out int,
> @bus_unit_out char(5),
> @voucher_id_out char(8),
> @sched_pay_day_out int,
> @sched_pay_month_out int,
> @sched_pay_year_out int,
> @pymnt_cnt_out int,
> @pymnt_id_out char(10),
> @eft_func_code_out char(6),
> @agreement_id_out char(15),
> @claim_id_out char(13),
> @loan_num_out char(15),
> @certificate_num_out char(15),
> @vendor_name_short_out char(11),
> @vendor_class_out char(1)
>
> SET @SqlString = N'SELECT @remit_vendor_out = A.REMIT_VENDOR
>       , @pmt_day_out = DAY(A.PYMNT_DT)
>     , @pmt_month_out = MONTH(A.PYMNT_DT)
>     , @pmt_year_out = YEAR(A.PYMNT_DT)
>     , @bus_unit_out = B.BUSINESS_UNIT
>     , @voucher_id_out = B.VOUCHER_ID
>     , @sched_pay_day_out = DAY(B.SCHEDULED_PAY_DT)
>     , @sched_pay_month_out = MONTH(B.SCHEDULED_PAY_DT)
>     , @sched_pay_year_out = YEAR(B.SCHEDULED_PAY_DT)
>     , @pymnt_cnt_out = B.PYMNT_CNT
>     , @pymnt_id_out = B.PYMNT_ID
>     , @eft_func_code_out = E.YX_EFT_PROD_FUNC
>     , @agreement_id_out = F.YX_AGREEMENT_ID
>     , @claim_id_out = F.YX_CLAIM_ID
>     , @loan_num_out = F.YX_LOAN_NUM
>     , @certificate_num_out = F.YX_CERTIFICATE_NUM
>     , @vendor_name_short_out = SUBSTRING(G.VENDOR_NAME_SHORT,1,11)
>     , @vendor_class_out = G.VENDOR_CLASS
>     FROM dbo.PS_PAYMENT_TBL A
>     , dbo.PS_PYMNT_VCHR_XREF B
>     , dbo.PS_YX_PYMNT_VCHR E
>     , dbo.PS_YX_VOUCHER F
>     , dbo.PS_VENDOR G
>     WHERE A.BANK_SETID = @bank_setid_prm
>     AND A.BANK_CD = @bank_cd_prm
>     AND A.BANK_ACCT_KEY = @bank_acct_key_prm
>     AND A.PYMNT_ID_REF = @pymnt_id_ref_prm
>     AND A.PYMNT_METHOD = ''EFT''
>     AND B.PYMNT_ID = A.PYMNT_ID
>     AND B.BANK_ACCT_KEY = A.BANK_ACCT_KEY
>     AND B.BANK_CD = A.BANK_CD
>     AND B.BANK_SETID = A.BANK_SETID
>     AND B.PYMNT_CNT = (SELECT MIN(C.PYMNT_CNT)
>     FROM dbo.PS_PYMNT_VCHR_XREF C
>     WHERE C.BUSINESS_UNIT = B.BUSINESS_UNIT
>     AND C.VOUCHER_ID = B.VOUCHER_ID
>     AND C.PYMNT_ID = B.PYMNT_ID
>     AND C.BANK_SETID = B.BANK_SETID
>     AND C.BANK_CD = B.BANK_CD
>     AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY)
>     AND B.VOUCHER_ID = (SELECT MIN(VOUCHER_ID)
>     FROM dbo.PS_PYMNT_VCHR_XREF D
>     WHERE D.PYMNT_ID = A.PYMNT_ID
>     AND D.BANK_ACCT_KEY = A.BANK_ACCT_KEY
>     AND D.BANK_CD = A.BANK_CD
>     AND D.BANK_SETID = A.BANK_SETID)
>     AND E.VOUCHER_ID = B.VOUCHER_ID
>     AND E.BUSINESS_UNIT = B.BUSINESS_UNIT
>     AND E.PYMNT_CNT = B.PYMNT_CNT
>     AND F.BUSINESS_UNIT = B.BUSINESS_UNIT
>     AND F.VOUCHER_ID = B.VOUCHER_ID
>     AND G.SETID = A.REMIT_SETID
>     AND G.VENDOR_ID = A.REMIT_VENDOR'
>
> SET @ParmDefinition =
> N'@bank_setid_prm char(5),
> @bank_cd_prm char(5),
> @bank_acct_key_prm char(4),
> @pymnt_id_ref_prm char(10),
> @remit_vendor_out char(10) OUTPUT,
> @pmt_day_out int OUTPUT,
> @pmt_month_out int OUTPUT,
> @pmt_year_out int OUTPUT,
> @bus_unit_out char(5) OUTPUT,
> @voucher_id_out char(8) OUTPUT,
> @sched_pay_day_out int OUTPUT,
> @sched_pay_month_out int OUTPUT,
> @sched_pay_year_out int OUTPUT,
> @pymnt_cnt_out int OUTPUT,
> @pymnt_id_out char(10) OUTPUT,
> @eft_func_code_out char(6) OUTPUT,
> @agreement_id_out char(15) OUTPUT,
> @claim_id_out char(13) OUTPUT,
> @loan_num_out char(15) OUTPUT,
> @certificate_num_out char(15) OUTPUT,
> @vendor_name_short_out char(11) OUTPUT,
> @vendor_class_out char(1) OUTPUT'
>
> SET @ret_cd = 0
>
> Set @bank_setid_prm = @bank_setid
> Set @bank_cd_prm = @bank_cd
> Set @bank_acct_key_prm = @bank_acct_key
> Set @pymnt_id_ref_prm = @pymnt_id_ref
>
> EXECUTE sp_executesql
> @SqlString,
> @ParmDefinition,
> @bank_setid_prm,
> @bank_cd_prm,
> @bank_acct_key_prm,
> @pymnt_id_ref_prm,
> @remit_vendor_out = @remit_vendor_out OUTPUT,
> @pmt_day_out = @pmt_day_out OUTPUT,
> @pmt_month_out = @pmt_month_out OUTPUT,
> @pmt_year_out = @pmt_year_out OUTPUT,
> @bus_unit_out = @bus_unit_out OUTPUT,
> @voucher_id_out = @voucher_id_out OUTPUT,
> @sched_pay_day_out = @sched_pay_day_out OUTPUT,
> @sched_pay_month_out = @sched_pay_month_out OUTPUT,
> @sched_pay_year_out = @sched_pay_year_out OUTPUT,
> @pymnt_cnt_out = @pymnt_cnt_out OUTPUT,
> @pymnt_id_out = @pymnt_id_out OUTPUT,
> @eft_func_code_out = @eft_func_code_out OUTPUT,
> @agreement_id_out = @agreement_id_out OUTPUT,
> @claim_id_out = @claim_id_out OUTPUT,
> @loan_num_out = @loan_num_out OUTPUT,
> @certificate_num_out = @certificate_num_out OUTPUT,
> @vendor_name_short_out = @vendor_name_short_out OUTPUT,
> @vendor_class_out = @vendor_class_out OUTPUT
>
> SELECT @remit_vendor_out, @pmt_day_out, @pmt_month_out, @pmt_year_out,
> @bus_unit_out, @voucher_id_out, @sched_pay_day_out,
>     @sched_pay_month_out, @sched_pay_year_out, @pymnt_cnt_out, @pymnt_id_out,
> @eft_func_code_out, @agreement_id_out,
>     @claim_id_out, @loan_num_out, @certificate_num_out, @vendor_name_short_out,
> @vendor_class_out
>
> Select @error=@@error, @rowcount=@@rowcount
>
> IF @error <> 0
> BEGIN
>     RAISERROR('Read of database failed ',16,1)
>     SET @ret_cd = 1
>     RETURN 1
> END
>
> IF @rowcount = 1
> BEGIN
>     SET @ret_cd = 0
>     RETURN 0
> END
>
> IF @rowcount > 1
> BEGIN
>     RAISERROR('Multiple rows returned - DB read failed ',16,1)
>     SET @ret_cd = 811
>     RETURN 1
> END
> ELSE
> BEGIN
>     RAISERROR('No rows returned - DB read failed ',16,1)
>     SET @ret_cd = 100
>     RETURN 1
> END
>
> RETURN 0
>
> END
> GO
>
> --
> Joe Palm
> Senior Technical Developer
> Madison, WI

12 May 2005 2:01 PM

Joe Palm

Thanks for the assistance, John...it was a great help!

I've fixed the stored proc as you recommended, and it works great now
without the redundant declaration of variables.

But you had a couple other comments I'd like to explore:

1) I ran the proc in Query Analyzer, and it does in fact return the rowcount
and error code for the DB call, which are valuable to return to the calling
COBOL pgm. So I'm not sure what you meant specifically by saying that the
"@@ROWCOUNT does not seem to be correct". Could you explain this a bit?

2) "Nothing dynamic in this SQL". Just for background, the whole reason
we've developed this proc was for performance reasons. We were told that
utilizing a stored proc with a dynamic SQL statement in it would help our
situation. Is there something else I need to do to make this proc's SQL
dynamic? If so, what? (Examples would be helpful)

Thanks again!

--
Joe Palm
Senior Technical Developer
Madison, WI

Show quote

"John Bell" wrote:

> Hi
>
> There does not seem to be anything dynamic in this SQL!!!
>
> You can use the stored procedure's parameters as the values for the
> parameters used in the sp_executeSQL statement.
> e.g.
> @remit_vendor_out = @remit_vendor OUTPUT,
> so there is no need to redeclare them within the procedure.
>
> As you are using output parameters there should be no need to return a
> record set. This is not using the output parameters for the stored procedure.
>
> SELECT @remit_vendor_out, @pmt_day_out, @pmt_month_out, @pmt_year_out,
> @bus_unit_out, @voucher_id_out, @sched_pay_day_out,
>     @sched_pay_month_out, @sched_pay_year_out, @pymnt_cnt_out, @pymnt_id_out,
> @eft_func_code_out, @agreement_id_out,
>     @claim_id_out, @loan_num_out, @certificate_num_out, @vendor_name_short_out,
> @vendor_class_out
>
> You may also want to check the status returned by sp_executesql. Your checks
> on @@ROWCOUNT does not seem to be correct.
>
> John
>
> "Joe Palm" wrote:
>
> > The following stored procedure works perfectly, but it's my first attempt at
> > writing one that generates and executes a dynamic SQL call to the DB and I'm
> > a little annoyed that I had to declare variables multiple times.
> > The way it is now, if I remove some declarations ("remit_vendor_out", for
> > e.g.), it won't pass the syntax checker. But if I remove the other
> > declaration of this variable, it errs out at run-time.
> > Can someone please fill me in on how I should change the stored proc so
> > variables are only declared once (as per the examples I tried to follow from
> > the MSFT KB), and it's thus less verbose and easier to maintain?
> >
> > Thanks.
> >
> > Here's the code:
> >
> > CREATE PROCEDURE SSP_GET_PMT_INFO3
> > @bank_setid char(5),
> > @bank_cd char(5),
> > @bank_acct_key char(4),
> > @pymnt_id_ref char(10),
> > @remit_vendor char(10) OUTPUT,
> > @pmt_day int OUTPUT,
> > @pmt_month int OUTPUT,
> > @pmt_year int OUTPUT,
> > @bus_unit char(5) OUTPUT,
> > @voucher_id char(8) OUTPUT,
> > @sched_pay_day int OUTPUT,
> > @sched_pay_month int OUTPUT,
> > @sched_pay_year int OUTPUT,
> > @pymnt_cnt int OUTPUT,
> > @pymnt_id char(10) OUTPUT,
> > @eft_func_code char(6) OUTPUT,
> > @agreement_id char(15) OUTPUT,
> > @claim_id char(13) OUTPUT,
> > @loan_num char(15) OUTPUT,
> > @certificate_num char(15) OUTPUT,
> > @vendor_name_short char(11) OUTPUT,
> > @vendor_class char(1) OUTPUT,
> > @ret_cd char(1) OUTPUT
> > AS
> > BEGIN
> >
> > DECLARE @err_cd smallint,
> > @rowcount int,
> > @error int,
> > @SqlString nvarchar(4000),
> > @ParmDefinition nvarchar(4000),
> > @bank_setid_prm char(5),
> > @bank_cd_prm char(5),
> > @bank_acct_key_prm char(4),
> > @pymnt_id_ref_prm char(10),
> > @remit_vendor_out char(10),
> > @pmt_day_out int,
> > @pmt_month_out int,
> > @pmt_year_out int,
> > @bus_unit_out char(5),
> > @voucher_id_out char(8),
> > @sched_pay_day_out int,
> > @sched_pay_month_out int,
> > @sched_pay_year_out int,
> > @pymnt_cnt_out int,
> > @pymnt_id_out char(10),
> > @eft_func_code_out char(6),
> > @agreement_id_out char(15),
> > @claim_id_out char(13),
> > @loan_num_out char(15),
> > @certificate_num_out char(15),
> > @vendor_name_short_out char(11),
> > @vendor_class_out char(1)
> >
> > SET @SqlString = N'SELECT @remit_vendor_out = A.REMIT_VENDOR
> >       , @pmt_day_out = DAY(A.PYMNT_DT)
> >     , @pmt_month_out = MONTH(A.PYMNT_DT)
> >     , @pmt_year_out = YEAR(A.PYMNT_DT)
> >     , @bus_unit_out = B.BUSINESS_UNIT
> >     , @voucher_id_out = B.VOUCHER_ID
> >     , @sched_pay_day_out = DAY(B.SCHEDULED_PAY_DT)
> >     , @sched_pay_month_out = MONTH(B.SCHEDULED_PAY_DT)
> >     , @sched_pay_year_out = YEAR(B.SCHEDULED_PAY_DT)
> >     , @pymnt_cnt_out = B.PYMNT_CNT
> >     , @pymnt_id_out = B.PYMNT_ID
> >     , @eft_func_code_out = E.YX_EFT_PROD_FUNC
> >     , @agreement_id_out = F.YX_AGREEMENT_ID
> >     , @claim_id_out = F.YX_CLAIM_ID
> >     , @loan_num_out = F.YX_LOAN_NUM
> >     , @certificate_num_out = F.YX_CERTIFICATE_NUM
> >     , @vendor_name_short_out = SUBSTRING(G.VENDOR_NAME_SHORT,1,11)
> >     , @vendor_class_out = G.VENDOR_CLASS
> >     FROM dbo.PS_PAYMENT_TBL A
> >     , dbo.PS_PYMNT_VCHR_XREF B
> >     , dbo.PS_YX_PYMNT_VCHR E
> >     , dbo.PS_YX_VOUCHER F
> >     , dbo.PS_VENDOR G
> >     WHERE A.BANK_SETID = @bank_setid_prm
> >     AND A.BANK_CD = @bank_cd_prm
> >     AND A.BANK_ACCT_KEY = @bank_acct_key_prm
> >     AND A.PYMNT_ID_REF = @pymnt_id_ref_prm
> >     AND A.PYMNT_METHOD = ''EFT''
> >     AND B.PYMNT_ID = A.PYMNT_ID
> >     AND B.BANK_ACCT_KEY = A.BANK_ACCT_KEY
> >     AND B.BANK_CD = A.BANK_CD
> >     AND B.BANK_SETID = A.BANK_SETID
> >     AND B.PYMNT_CNT = (SELECT MIN(C.PYMNT_CNT)
> >     FROM dbo.PS_PYMNT_VCHR_XREF C
> >     WHERE C.BUSINESS_UNIT = B.BUSINESS_UNIT
> >     AND C.VOUCHER_ID = B.VOUCHER_ID
> >     AND C.PYMNT_ID = B.PYMNT_ID
> >     AND C.BANK_SETID = B.BANK_SETID
> >     AND C.BANK_CD = B.BANK_CD
> >     AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY)
> >     AND B.VOUCHER_ID = (SELECT MIN(VOUCHER_ID)
> >     FROM dbo.PS_PYMNT_VCHR_XREF D
> >     WHERE D.PYMNT_ID = A.PYMNT_ID
> >     AND D.BANK_ACCT_KEY = A.BANK_ACCT_KEY
> >     AND D.BANK_CD = A.BANK_CD
> >     AND D.BANK_SETID = A.BANK_SETID)
> >     AND E.VOUCHER_ID = B.VOUCHER_ID
> >     AND E.BUSINESS_UNIT = B.BUSINESS_UNIT
> >     AND E.PYMNT_CNT = B.PYMNT_CNT
> >     AND F.BUSINESS_UNIT = B.BUSINESS_UNIT
> >     AND F.VOUCHER_ID = B.VOUCHER_ID
> >     AND G.SETID = A.REMIT_SETID
> >     AND G.VENDOR_ID = A.REMIT_VENDOR'
> >
> > SET @ParmDefinition =
> > N'@bank_setid_prm char(5),
> > @bank_cd_prm char(5),
> > @bank_acct_key_prm char(4),
> > @pymnt_id_ref_prm char(10),
> > @remit_vendor_out char(10) OUTPUT,
> > @pmt_day_out int OUTPUT,
> > @pmt_month_out int OUTPUT,
> > @pmt_year_out int OUTPUT,
> > @bus_unit_out char(5) OUTPUT,
> > @voucher_id_out char(8) OUTPUT,
> > @sched_pay_day_out int OUTPUT,
> > @sched_pay_month_out int OUTPUT,
> > @sched_pay_year_out int OUTPUT,
> > @pymnt_cnt_out int OUTPUT,
> > @pymnt_id_out char(10) OUTPUT,
> > @eft_func_code_out char(6) OUTPUT,
> > @agreement_id_out char(15) OUTPUT,
> > @claim_id_out char(13) OUTPUT,
> > @loan_num_out char(15) OUTPUT,
> > @certificate_num_out char(15) OUTPUT,
> > @vendor_name_short_out char(11) OUTPUT,
> > @vendor_class_out char(1) OUTPUT'
> >
> > SET @ret_cd = 0
> >
> > Set @bank_setid_prm = @bank_setid
> > Set @bank_cd_prm = @bank_cd
> > Set @bank_acct_key_prm = @bank_acct_key
> > Set @pymnt_id_ref_prm = @pymnt_id_ref
> >
> > EXECUTE sp_executesql
> > @SqlString,
> > @ParmDefinition,
> > @bank_setid_prm,
> > @bank_cd_prm,
> > @bank_acct_key_prm,
> > @pymnt_id_ref_prm,
> > @remit_vendor_out = @remit_vendor_out OUTPUT,
> > @pmt_day_out = @pmt_day_out OUTPUT,
> > @pmt_month_out = @pmt_month_out OUTPUT,
> > @pmt_year_out = @pmt_year_out OUTPUT,
> > @bus_unit_out = @bus_unit_out OUTPUT,
> > @voucher_id_out = @voucher_id_out OUTPUT,
> > @sched_pay_day_out = @sched_pay_day_out OUTPUT,
> > @sched_pay_month_out = @sched_pay_month_out OUTPUT,
> > @sched_pay_year_out = @sched_pay_year_out OUTPUT,
> > @pymnt_cnt_out = @pymnt_cnt_out OUTPUT,
> > @pymnt_id_out = @pymnt_id_out OUTPUT,
> > @eft_func_code_out = @eft_func_code_out OUTPUT,
> > @agreement_id_out = @agreement_id_out OUTPUT,
> > @claim_id_out = @claim_id_out OUTPUT,
> > @loan_num_out = @loan_num_out OUTPUT,
> > @certificate_num_out = @certificate_num_out OUTPUT,
> > @vendor_name_short_out = @vendor_name_short_out OUTPUT,
> > @vendor_class_out = @vendor_class_out OUTPUT
> >
> > SELECT @remit_vendor_out, @pmt_day_out, @pmt_month_out, @pmt_year_out,
> > @bus_unit_out, @voucher_id_out, @sched_pay_day_out,
> >     @sched_pay_month_out, @sched_pay_year_out, @pymnt_cnt_out, @pymnt_id_out,
> > @eft_func_code_out, @agreement_id_out,
> >     @claim_id_out, @loan_num_out, @certificate_num_out, @vendor_name_short_out,
> > @vendor_class_out
> >
> > Select @error=@@error, @rowcount=@@rowcount
> >
> > IF @error <> 0
> > BEGIN
> >     RAISERROR('Read of database failed ',16,1)
> >     SET @ret_cd = 1
> >     RETURN 1
> > END
> >
> > IF @rowcount = 1
> > BEGIN
> >     SET @ret_cd = 0
> >     RETURN 0
> > END
> >
> > IF @rowcount > 1
> > BEGIN
> >     RAISERROR('Multiple rows returned - DB read failed ',16,1)
> >     SET @ret_cd = 811
> >     RETURN 1
> > END
> > ELSE
> > BEGIN
> >     RAISERROR('No rows returned - DB read failed ',16,1)
> >     SET @ret_cd = 100
> >     RETURN 1
> > END
> >
> > RETURN 0
> >
> > END
> > GO
> >
> > --
> > Joe Palm
> > Senior Technical Developer
> > Madison, WI

13 May 2005 6:48 AM

John Bell

Hi Joe

Check out Alejandro's reply and read Erland's article that Hugo
references. Your rowcount is incorrect because it is reset by the
subsequent select. e.g.

DECLARE @val int
SET @val = 9

SELECT * FROM Pubs..authors
SELECT @@ROWCOUNT

SELECT @val
SELECT @@ROWCOUNT

If you looked at Erlands article you will see examples of dynamic SQL.
If your parameters were optional then you may want to use dynamic SQL,
but you always test them. For instance if you had

A.BANK_SETID = ISNULL(@bank_setid_prm,A.BANK_SETID) in your query then
if you were using Dynamic SQL the alternative would be to test IF
@bank_setid_prm IS NULL in the code and only add A.BANK_SETID =
@bank_setid_prm when your dynamic SQL statementif it had a value.

My preference is to use an ON clause for JOINS as I think it tends to
be clearer. You can see examples in books online.

HTH

John

12 May 2005 9:36 PM

Hugo Kornelis

On Thu, 12 May 2005 07:01:02 -0700, Joe Palm wrote:

(snip)

>We were told that
>utilizing a stored proc with a dynamic SQL statement in it would help our
>situation.

Hi Joe,

The curse and blessings of dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)