curiouswebster
asked on
Need return values from a stored procedure
In SQL Server I call the following sproc, referenced by an SSRS report:
and get back a response of:
Command(s) completed successfully.
I don't get an returned rows.
Suggestions?
exec dailyFinalStatsReport '01/01/2017', '03/16/2017', 'All'
and get back a response of:
Command(s) completed successfully.
I don't get an returned rows.
Suggestions?
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was using an incorrect string input.
Thanks.
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.
So that means that this comment was the only correct answer, which is different from the split I see here.
ASKER
I had not seen your comment when I approved it last time, but this indeed was the error.
Thanks for the help.
Thanks for the help.
You need to post the contents of the sp here if you are expecting a recordset