Using stored procedure parameters in SQL queries

[Marchand Durpis]

I'm trying (and failing) to use parameters passed into stored
procedures within an IF statement in an ADP application fronting
SQL Server. Boiled down to the essence, the stored procedure code
looks like this:

Alter Procedure stpDocsList(@psOffice varchar(20), @psTypeRep varchar(20))
As

IF @psOffice = 'NYC'
SELECT Docname, docfilename, PertainsTo FROM DocsList WHERE
PertainsTo = @psTypeRep and UsedInNYC = 1
ELSE
SELECT PertainsTo FROM DocsList WHERE PertainsTo = @psTypeRep
return

The problem I'm having is that the IF portion never executes (the ELSE
always does), even when the passed parameter is 'NYC'. I've tried "
LIKE 'NYC' ", " LIKE '%NYC%' " and innumerable other variants, with
equal lack of success. So what syntax should I be using here to make
it work?

TIA,
Marchand

 

[BJ Freeman]

need to use
IF @psOffice = 'NYC'
Begin
end
else
Begin
end

Open the Query analyser and click on Help->TSQL

 

[Van T. Dinh]

Aren't the 2 SELECT statements are simply single
statements, not statement blocks and therefore BEGIN ...
END blocking is not necessary???

See my test SQL that works in my reply to Marchand.

HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Not sure what's wrong in your ADP as I created a test SP
with:

****
CREATE Procedure spDocsList
@psOffice varchar(20), @psTypeRep varchar(20)
As
IF @psOffice = 'NYC'
SELECT FactoryID, FactoryCode
FROM tblFactory
WHERE FactoryCode = @psTypeRep

ELSE
SELECT FactoryID, BFCode_Def_1, BFCode_Def_2
FROM tblFactory
WHERE FactoryCode = @psTypeRep
GO
****

(just to use one of my existing Tables)

and then I executed the following:

Exec spDocsList 'NYC', 'S3'

and

Exec spDocsList 'ABC', 'S3'

and both Execs worked correctly.

Notes:
1. I don't think the RETURN at the end is necessary.
2. I don't use parentheses around the arguments but I
don't think that matters either.
3. Tested in MS-SQL2K Query Analyzer.

HTH
Van T. Dinh
MVP (Access)

 

[Vadim Rapp]

MD> IF @psOffice = 'NYC'
MD> SELECT Docname, docfilename, PertainsTo FROM
MD> DocsList WHERE PertainsTo = @psTypeRep and
MD> UsedInNYC = 1
MD> ELSE
MD> SELECT PertainsTo FROM DocsList WHERE PertainsTo
MD> =
MD> @psTypeRepreturn

I think your #1 problem is that your 2 select statements return different
schema depending on the parameter. I wonder, how do you expect it to work?
even more interesting, how do you expect Access to expect it to work?

Vadim

 

[Marchand Durpis]

Thanks to all who have responded so far. I'm still stuck, but it's
nice to have the support.

I haven't found adding BEGIN/END blocks affects anything. In the full
application code, I do use that structure since there's a bunch more
VBA involved than just the SELECT. (On the other hand, unless I can
get the SELECT to work correctly, the rest is irrelevant).

Van, it looks like your code is identical in syntax to mine, in the
"IF @psOffice = 'NYC' " line, or am I missing something here?

To Vadim, yes, the two SELECT statements return different columns from
the database. In the real application, they return identical sets of
fields. In my cutting and pasting for the original post, I focused on
the "IF" statement and was obviously too casual in stripping out or
modifying the rest of the (much longer) original code. Sorry for the
confusion.

/Marchand/

 

[BJ Freeman]

Clarification
is this VBA code or Store procedure Code.
they are not the Same
I may have misunderstood you.

 

[Van T. Dinh]

It is exactly the same as yours. I modified the T-SQL sp simply to use one
of my Tables.

However, in view of what Vadim wrote, I should point out that I only tested
in the EM / Query Analyzer of MS-SQL Server 2K and not actually in an Access
ADP. I found designing or even executing sp through the Access ADP
sometimes give errors / weird results while the sp works fine in MS-SQL
Query Analyzer.

 

[Marchand Durpis]

In answer to BJ, my posted code was from a stored procedure.

Van, could you give more details to your statement: "I found designing
or even executing sp through the Access ADP sometimes give errors /
weird results while the sp works fine in MS-SQL Query Analyzer" This
is a spooky statement just there on its own.

Finally, could the SQL data type make a difference in using the
parameter in the "IF" statement? In the procedure header it's defined
as " @psOffice varchar(16)". Would other data types work better?

 

[Van T. Dinh]

I found a number of SPs working in MS-SQL Server without any problem and
when I re-wrote the SPs in Access ADP, they gave vague errors.

In fact I tested the SP I posted in SQL Query Analyzer without any problems
but when I modified it as the ADP SP, it worked most of the time but one or
twice, it gave me wrong results for some reason.

OTOH, it may be because I am not familiar with ADPs. I still prefer to use
MDBs.

No, the varchar works fine in SQL Query Analyzer.

 

[Marchand Durpis]

Van,

Thanks for the follow-up. I'm wondering now if I'm running into the
same ADP/SQL Server stored procedure oddities, as the problem still
persists. Grump!

/Marchand/