CODEWITHSUNDEEP
sqlsql-server
Steve Staple
Steve Staple

Reputation: 3279

How do I prevent SQL Server returning 3 sets of results?

I have a Stored Procedure that calls on 2 other stored procedures to obtain some values.

ALTER PROCEDURE [dbo].[Delivery_SelectByJobID] 
(
    @job_ID int
)
as
-- if possible - if count(pods) > 0 return true
-- and           if count(photos) > 0 return true
declare @podCount int = 0;
declare @photoCount int = 0;

EXEC @podCount=countPodsForJob @job_ID
EXEC @photoCount =countPhotosForJob @job_ID

declare @podsBit bit=0
declare @photosBit bit=0

if @podCount > 0
    begin
set @podsBit = 1
    end

if @photoCount > 0
    begin
    set @photosBit = 1
    end

SELECT     Job_Reference_No, Job_Start_Date, Job_POD_Filename, Job_Photo_Filename, 
Job_Signed_For_Name, Job_ID, Job_Status, Job_Delivery_Notes, @podsBit as Job_POD_Supplied, 
                      @photosBit as Job_Photo_Supplied
FROM         Jobs
WHERE     (Job_ID = @Job_ID) AND (Job_Status <> 7)

When I run this it returns 3 sets of results, i.e. Count_pods, Count_Photos, and the results of the select. I only want to return the results of the select. How can I stop the other results being returned?

Upvotes: 3

Views: 1719

Answers (3)

Steve Staple
Steve Staple

Reputation: 3279

Solved this.

I needed to convert countPodsForJob and countPhotosForJob to functions that returned values rather than results set.

Upvotes: 1

suff trek
suff trek

Reputation: 39777

Your stored procedures countPodsForJob and countPhotosForJob return resultsets of their own. You can either rewrite them not to execute SELECTs if a specific parameter is passed or (a better option) create their alternatives as scalar UDFs.

Another alternative (if you don't have control over existing SPs and cannot create new functions) is simple to ignore other resultsets in caller app. (e.g. in ADO.NET DataSet you can simple take the 3rd DataTable).

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

You can use 

SET NOCOUNT ON;    ---- after the AS in your proc's Definition.

It will stop sending the message as 12 row(s) ......

Check the definition of other stored Procedures aswell and do the same with them .....
Infact I have just noticed that your Nested Stored Procs and returning values for the variables @prodCount and @PhotoCount Make Them OUTPUT parameters, That will store the value in them passed Parameters and then you can Pass them OUT parameters to you SELECT statement.

Once you changes the definition of the nested procs you can do something like this..

 ALTER PROCEDURE [dbo].[Delivery_SelectByJobID] 
    (
        @job_ID int
    )
    as
    SET NOCOUNT ON;
    -- if possible - if count(pods) > 0 return true
    -- and           if count(photos) > 0 return true
    declare @podCount int = 0;
    declare @photoCount int = 0;

    --EXEC @podCount=countPodsForJob @job_ID          --Your Code
    --EXEC @photoCount =countPhotosForJob @job_ID     --Your Code

    EXEC countPodsForJob @job_ID, @podCount OUTPUT         --My Suggestion
    EXEC countPhotosForJob @job_ID, @photoCount OUTPUT     --My Suggestion

    declare @podsBit bit=0
    declare @photosBit bit=0

    if @podCount > 0
        begin
    set @podsBit = 1
        end

    if @photoCount > 0
        begin
        set @photosBit = 1
        end

    SELECT     Job_Reference_No, Job_Start_Date, Job_POD_Filename, Job_Photo_Filename, 
    Job_Signed_For_Name, Job_ID, Job_Status, Job_Delivery_Notes, @podsBit as Job_POD_Supplied, 
                          @photosBit as Job_Photo_Supplied
    FROM         Jobs
    WHERE     (Job_ID = @Job_ID) AND (Job_Status <> 7)

Upvotes: 0

Related Questions