Stored Proc only returns one column

[ProfReynolds]

Okay, here's what I'm looking at:

[ul]
[li] I am running the version of Crystal included in VS 2005.[/li]
[li] I am connecting via ODBC to an MSSQL database locally.[/li]
[li] Crystal found 3 parameter fields (this is good)[/li]
[li] BUT it only found one output column (this is bad)[/li]
[/ul]

This is the stored procedure I am referencing:

USE [TDSW]
GO
/****** Object:  StoredProcedure [dbo].[CrystalTest]    Script Date: 09/14/2007 12:11:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joe
-- Create date: 09/13/2007
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[CrystalTest] 
	-- Add the parameters for the stored procedure here

	@Custno nvarchar(max) = Null,
	@CustComp nvarchar(max) = Null,
	@debug int = 0

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @query nvarchar(max),
		@wherestr nvarchar(max)

SELECT	@query = 'SELECT Custno, CustComp FROM rpt_dummy_102',
		@wherestr = ''

If @Custno is not null
	SET @wherestr	=	@wherestr + ' Custno = '
 					+	@Custno + ' AND'
IF @CustComp is not null
	SET @wherestr	=	@wherestr + ' CustComp LIKE ''%'
 					+	@CustComp + '%'' AND'

If @debug <>0
	SELECT @wherestr wherestr

--Remove ' AND' from end of string
BEGIN TRY
	IF SUBSTRING(@wherestr, LEN(@wherestr) - 3, 4) = ' AND'
		SET @wherestr = SUBSTRING(@wherestr, 1, LEN(@wherestr)-3)
END TRY

BEGIN CATCH
	RAISERROR ('Unable to remove last AND', 16, 1)
	RETURN
END CATCH

IF @debug <>0 
	select @wherestr wherestr

BEGIN TRY
	If LEN(@wherestr)>0
		SET @query = @query + ' WHERE ' + @wherestr

	if @debug <> 0
	select @query query

	-- Get Dataset
	EXEC (@query)
END TRY
BEGIN CATCH
	Declare @s varchar(max)
	set @s = 'Unable to Execute new query: ' + @query
	RAISERROR (@s, 16, 2)
	RETURN
END CATCH

RETURN


    -- Insert statements for procedure here
SELECT     CustNo, CustComp
FROM         rpt_dummy_102

END

Running this procedure in MSSQL Management Studio yields the following Dataset:

1235	OIL WORKS INC,
1234	SON TEX RATHOLE
1236	AMERADA HESS CORP.
1234	SON TEX RATHOLE

However, when I attempt to access this SPROC in Crystal, the query string generated looks like this:

{CALL "TDSW"."dbo"."CrystalTest";1(NULL, N'Oil', NULL)}

and only returns the one column (CustNo).

I need for this to return all of the columns, and for it to allow me to "drag and drop" said columns into my report.

I appreciate any help I can get. Let me know if anything is not clear.

 

[synapsevampire]

Try:

-- Insert statements for procedure here
SELECT CustNo, cast(CustComp as varchar(100))
FROM rpt_dummy_102

It may be that the data type for CustComp isn't recognized by Crystal.

-k

 

[ProfReynolds]

That wasn't it

 

[synapsevampire]

No, I guess not.

I don't understand why that final select is there?

In theory you're constructing dynamic sql and executing it.

Try changing this:

SELECT @query = 'SELECT Custno, CustComp FROM rpt_dummy_102',
@wherestr = ''


to this:

SELECT @query = 'SELECT Custno, 'Blah' as CustComp FROM rpt_dummy_102',
@wherestr = ''

And what is the structure of rpt_dummy_102, and why use it???

-k

 

[ProfReynolds]

Here is the SPROC as it stands now, after having tried a couple of edits suggested here...

USE [TDSW]
GO
/****** Object:  StoredProcedure [dbo].[CrystalTest]    Script Date: 09/15/2007 13:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joe
-- Create date: 09/13/2007
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[CrystalTest] 
	-- Add the parameters for the stored procedure here

	@Custno nvarchar(max) = Null,
	@CustComp nvarchar(max) = Null,
	@debug int = 0

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @query nvarchar(max),
		@wherestr nvarchar(max)

SELECT	@query = 'SELECT Custno, ''blah'' AS CustComp FROM rpt_dummy_102',
		@wherestr = ''

If @Custno is not null
	SET @wherestr	=	@wherestr + ' Custno = '
 					+	@Custno + ' AND'
IF @CustComp is not null
	SET @wherestr	=	@wherestr + ' CustComp LIKE ''%'
 					+	@CustComp + '%'' AND'

If @debug <>0
	SELECT @wherestr wherestr

--Remove ' AND' from end of string
BEGIN TRY
	IF SUBSTRING(@wherestr, LEN(@wherestr) - 3, 4) = ' AND'
		SET @wherestr = SUBSTRING(@wherestr, 1, LEN(@wherestr)-3)
END TRY

BEGIN CATCH
	RAISERROR ('Unable to remove last AND', 16, 1)
	RETURN
END CATCH

IF @debug <>0 
	select @wherestr wherestr

BEGIN TRY
	If LEN(@wherestr)>0
		SET @query = @query + ' WHERE ' + @wherestr

	if @debug <> 0
	select @query query

	-- Get Dataset
	EXEC (@query)
END TRY
BEGIN CATCH
	Declare @s varchar(max)
	set @s = 'Unable to Execute new query: ' + @query
	RAISERROR (@s, 16, 2)
	RETURN
END CATCH

RETURN

END

rpt_dummy_102 was a view designed for testing purposes. The code for the view joins several tables as follows:

SELECT     dbo.INVSTK.InvStkID, dbo.INVSTK.CustRefNo, dbo.INVSTK.MaterialID, dbo.INVSTK.CustNo, dbo.INVSTK.EndUser, dbo.INVSTK.Supplier, 
                      dbo.INVSTK.RackNo, dbo.INVSTK.Status, dbo.INVSTK.Quantity, dbo.INVSTK.Length, dbo.INVSTK.ReleaseNo, dbo.INVSTK.ReleaseByFeet, 
                      dbo.INVSTK.Type, dbo.INVSTK.NewUsed, dbo.INVSTK.InternalCondition, dbo.INVSTK.SizeWeight, dbo.INVSTK.Thread, dbo.INVSTK.Grade, 
                      dbo.INVSTK.Connection, dbo.INVSTK.Range, dbo.INVSTK.Manufacture, dbo.INVSTK.Design, dbo.INVSTK.Color, dbo.INVSTK.Class, dbo.INVSTK.Notation, 
                      dbo.INVSTK.FFDesc, dbo.Customer.CompanyName AS CustComp, EndUser.CompanyName AS EUComp, Supplier.CompanyName AS SUComp
FROM         dbo.INVSTK INNER JOIN
                      dbo.InvCom ON dbo.INVSTK.MaterialID = dbo.InvCom.MaterialID INNER JOIN
                      dbo.Customer ON dbo.INVSTK.CustNo = dbo.Customer.CUSTID INNER JOIN
                      dbo.Customer AS EndUser ON dbo.INVSTK.EndUser = EndUser.CUSTID INNER JOIN
                      dbo.Customer AS Supplier ON dbo.INVSTK.Supplier = Supplier.CUSTID

Having used the "blah" suggested by synapsevampire, here is a screenshot from Crystal:

When I go in to the report preview, I receive the following error message:

Thanks for your help so far!

 

[synapsevampire]

Right, you must always use Database->Verify Database when you make a change to the underlying data structure, perhaps your problem all along.

-k