Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

Need return values from a stored procedure

In SQL Server I call the following sproc, referenced by an SSRS report:

exec dailyFinalStatsReport '01/01/2017', '03/16/2017', 'All'

Open in new window


and get back a response of:

Command(s) completed successfully.

I don't get an returned rows.

Suggestions?
Avatar of Aneesh
Aneesh
Flag of Canada image

Probably it didnt find any matched rows..
You need to post the contents of the sp here if you are expecting a recordset
Post the Stored Procedure's T-SQL into this question.

There's a good chance you have multiple statements in this SP that return things like '(100 rows updated)', that SSRS expects as a set, and throws an error.  To prevent this add SET NOCOUNT ON right below your CREATE PROC statement.

Either that or the SP does not return a set.
Avatar of curiouswebster

ASKER

Rows are being returned when I look at the report on my test site. That internal QA site hits the same database as the one I am connected to from SSMS, where I execute the sproc (at my top post) which has the same params as selected on the QA site.

Here is a stripped down version of the sproc

USE [MyDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dailyFinalStatsReport]
	@startDate datetime,
	@endDate datetime,
	@reportType varchar(30)
AS
BEGIN

if @reportType = ' All'
Begin
select	
	hfs.DateOfChange as HFCurrStatusDate
	,hfs.substatus as HFCurrSubStatus
	,hmf.Sales_Id
from  
	dbo.HFMadeFinal hmf
        -- various joins, including the definition of hfs
where 
	CAST(FLOOR(CAST(hmf.AddedDate as float)) as datetime) between @startDate and @endDate
	and isNull(hmf.FellThrough, 'false') = 'false' 
End

END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was using an incorrect string input.

Thanks.
>I was using an incorrect string input.
So that means that this comment was the only correct answer, which is different from the split I see here.
I had not seen your comment when I approved it last time, but this indeed was the error.

Thanks for the help.