By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,117 Members | 2,097 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,117 IT Pros & Developers. It's quick & easy.

Access Data Project using procedure with IF statement, condition

P: n/a
I have a situation in which I want to dynamically build a SQL
statement based upon criteria. The statement would contain different
fields depending upon the criteria. I created a Stored Procedure that
uses If...Else logic to return the resultset. What I am finding
perplexing is that the procedure works just fine when ran from some
SQL Editor, but when run through ADP the fields from the first SELECT
statement are returned even though the condition was not met, but the
where clause follows the condition rules.
CREATE PROC ConditionalProc
@ID INT
AS

IF @ID = 1
select emp_id, lname + ', ' + fname from dbo.employee
Else If @ID = 0
select emp_id, fname, lname from dbo.employee
Else
select * from dbo.employee
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"mr_mach7" <mr******@yahoo.com> wrote in message
news:48**************************@posting.google.c om...
I have a situation in which I want to dynamically build a SQL
statement based upon criteria. The statement would contain different
fields depending upon the criteria. I created a Stored Procedure that
uses If...Else logic to return the resultset. What I am finding
perplexing is that the procedure works just fine when ran from some
SQL Editor, but when run through ADP the fields from the first SELECT
statement are returned even though the condition was not met, but the
where clause follows the condition rules.
CREATE PROC ConditionalProc
@ID INT
AS

IF @ID = 1
select emp_id, lname + ', ' + fname from dbo.employee
Else If @ID = 0
select emp_id, fname, lname from dbo.employee
Else
select * from dbo.employee


How are you passing the parameters? Run a trace using Profiler and verify
that the param values are correct. Also, you should have a SET NOCOUNT ON
statement at the start of the procedure - but that shouldn't affect the
outcone here. Anyway, verify the parameter values first.
Nov 13 '05 #2

P: n/a
"John Winterbottom" <as******@hotmail.com> wrote in message news:<30*************@uni-berlin.de>...
"mr_mach7" <mr******@yahoo.com> wrote in message
news:48**************************@posting.google.c om... How are you passing the parameters? Run a trace using Profiler and verify
that the param values are correct. Also, you should have a SET NOCOUNT ON
statement at the start of the procedure - but that shouldn't affect the
outcone here. Anyway, verify the parameter values first.

This procedure is correct and does return the correct resultset if I
run this procedure from some query editor. The problem is when I run
this from the Microsoft Access Project it does not return the same
resultsets as the other query editors do.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.