Stored Procedure and Subreport Issue

[gaishan]

I have a Crystal report with one subreport. The main report and the subreport are linked to the same stored proc. The stored proc is basically the following at the end.

When I link the parameter fields from the main report to the subreport (so the user doesn't have to fill them in twice), I receive a query engine error message, Query Engine Error 42000 "incorrect syntax near ')'. If they are unlinked and I send the parameter field info twice, once for the main report and once for the subreport, I receive no error. The stored procedure works fine in query analyzer as well.

Anyone have any ideas on this error and how to fix it?

I'm using Crystal Reports 9 and SQL Server 2k.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].generic(@BeginDate varchar(10),
@EndDate varchar(10),
@IDs varchar(50))

AS

set nocount on

DECLARE @SQLString NVARCHAR(4000)

SET @SQLString = N'SELECT *

FROM table1 As a
join table2 As b ON a.key = b.key

WHERE a.ID in (' + @IDs + ')
AND a.BeginDate >= convert(datetime,''' + @BeginDate + ''',102)+ ''00:00:00.000''
AND a.EndDate <= convert(datetime,''' + @EndDate + ''',102) + ''23:59:59.999''

GROUP BY b.Type

ORDER BY b.Type'

EXEC sp_executesql @SQLString

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[synapsevampire]

Why are you using the sp_executesql command instead of just letting the SQL run?

I would think that would slow down the SP as it won't optimize properly.

Anyway, I don't see anything blatantly wrong with the SQL, try changing connectivity types.

-k

 

[gaishan]

I'm using sp_executesql because the ID field is a security field which could include several different numbers 1,2,3,4,5 for example so the client only sees information specific to them. The only way I know of to be able to send that range in as a parameter, is to do it the way I am currently doing it (since the ID field is an int), by sending it in as a string and creating the SQL string on the fly. It would be easier if they made a table for me that had one number that would pull back all the id's specific to a client but currently they tell me there is no time for that.

I get the same error with OLEDB.

 

[synapsevampire]

Try:

Set @ID = '''+@IDS+'''

then just execute the select.

Something like that, then you won't need dynamic SQL (I don't have a SQL2K box to test on right now.

Might not make a difference, but Crystal might be getting confused because of this.

-k

 

[gaishan]

Thanks for the help. Your comments put me on a different track so now I've chosen a completely different method that doesn't use the dynamic sql and doesn't give me an error message.