The command(s) completed successfully problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The command(s) completed successfully problem

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2006-08-01 : 09:54:40

i have a stored proc that contains an if statement. Rows should only be returned if the if statements condition is found to be true. If not then 0 rows should be returned so that i can use my recordset's eof property to display a suitable message or loopp through the recordset. however i never get 0 rows back. it wourks fine if the condition is true but not vice versa. I ran the proc in query analyzer and found that "The command(s) completed successfully problem" is returned. Would anybody know if this is is the problem, or is this a just a standard queary analyzer message. my code is below. Any help would be greatly appreciated.

declare @IsTasksAvailable bit

select @IsTasksAvailable = checked from Project_Setup_list inner join project_setup on project_setup.project_setup_id = Project_Setup_list.project_setup_id where project_setup.job_id = @jobID and document_id = 6

if @IsTasksAvailable = 1 begin
select
tasks.Task_Name
,tasks.Task_Reference
,reviews.Review_id
,reviews.Review_Type_id
,reviews.Review_Title
,documents.document_name
,reviews.created_date
,reviews.review_group_id
,document_Status.Status_Description
from
reviews
inner join tasks on tasks.jms_task_id = reviews.jms_task_id and task_id = (select max(task_id) from tasks where tasks.jms_task_id = reviews.jms_task_id)
inner join documents on reviews.review_type_id = documents.document_id
inner join Documents_Revision_And_Status on Documents_Revision_And_Status.Job_Id = reviews.Job_ID and Documents_Revision_And_Status.form_id = reviews.review_id and review_type_id = documents.document_id
inner join document_Status on Documents_Revision_And_Status.status = document_Status.status_id
where reviews.review_group_id = 2 and tasks.active = 1 and reviews.active = 1 and reviews.job_id = @jobID
order by tasks.Task_Name
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 09:59:16

Remove the IF statement and add a new WHERE clause like

AND ISNULL(@IsTasksAvailable, 0) = 1

If @IsTasksAvailable is 1, then you get all columns AND the data. If @IsTasksAvailable is 0 (or null), then you still get all the columns, but no data (ie no rows).

Hope this helps.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 10:01:31

[code]declare @IsTasksAvailable bit

select @IsTasksAvailable = checked
from Project_Setup_list
inner join project_setup on project_setup.project_setup_id = Project_Setup_list.project_setup_id
where project_setup.job_id = @jobID
and document_id = 6

select tasks.Task_Name,
tasks.Task_Reference,
reviews.Review_id,
reviews.Review_Type_id,
reviews.Review_Title,
documents.document_name,
reviews.created_date,
reviews.review_group_id,
document_Status.Status_Description
from reviews
inner join tasks on tasks.jms_task_id = reviews.jms_task_id
and task_id = (select max(task_id) from tasks where tasks.jms_task_id = reviews.jms_task_id)
inner join documents on reviews.review_type_id = documents.document_id
inner join Documents_Revision_And_Status on Documents_Revision_And_Status.Job_Id = reviews.Job_ID
and Documents_Revision_And_Status.form_id = reviews.review_id
and review_type_id = documents.document_id
inner join document_Status on Documents_Revision_And_Status.status = document_Status.status_id
where reviews.review_group_id = 2
and tasks.active = 1
and reviews.active = 1
and reviews.job_id = @jobID
and isnull(@IsTasksAvailable, 0) = 1
order by tasks.Task_Name[/code]
Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2006-08-01 : 10:06:05

worked a treat. thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources