Crystal 2016 ans SQL Server Stored Procedure

[Former Member]

I have written a SP in SQL 2008 R2 which has input parameters and will return one scalar value as well as do some table updating. I can run the SP in the management console just fine. But, I need to incorporate this SP into a Crystal Report. I can add this SP in the Database Expert but I cannot click on the plus sign to expand the SP to get at the return value and add it to my report.

I've been working with computers for a long time but am new to SQL server and am not familiar with roles and security settings so it maybe something fundamental. Also, my last encounter with Crystal Reports was in 1997.

Anybody know what I may be doing wrong?

TIA - Chris

[Former Member]

How would I modify my SP to return a table of one column and one row?

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE test1

@WC varchar(4)

AS BEGIN

SET NOCOUNT ON .

(SELECT Units FROM WorkCenters WHERE WorkCenter = @WC)

END

GO

--exec test1 'S323'

[DellSC]

When Crystal connects to a stored procedure, it expects to have a table as the result and may not be able to handle a scalar value if you're just linking tables together. However, you might be able to get the value using a command in Crystal. A command is just a SQL Select statement. If your stored proc has parameters, you'll have to create the parameters in the Command Editor - NOT in the field explorer for the report - in order to use them. You can find more info about how to configure the parameters here:https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/.

-Dell