Turning a SQL Query into A Stored Procedure for Crystal Reports 2011

[MBresnan]

I wrote a query in SQL that i'd like to turn into a crystal report for Workplace (it also reads data from some GP Tables). However, going off the Stored Procedures that are out there for current reports, they seem incredibly complex for what i'm trying to do. I've never written a stored procedure before, however, so i'm not sure if that is the norm.

Here's my query:
Select D.[POPRCTNM],A.[edfPOnumber],A.[edfitem],A.[edfitemdesc],A.[edfamtextended],C.[actnumst], E.[vendname], F.[DSCRIPTN]


From [CA].[dbo].[rqdetail] A inner join [CA].[dbo].[RQHeader] B
on A.idfrqheaderkey = b.idfrqheaderkey
left outer join [CA].[dbo].[GL00105] C
on A.edfgl=c.actindx
left outer join [ca].[dbo].[gl40200] F
on C.[actnumbr_3] = F.[sgmntid]
left outer join [CA].[dbo].[POP10310] D
on A.[edfPOnumber]=D.[POnumber] and a.[edfpoline]=d.[rcptlnnm]
left outer join [ca].[dbo].[pm00200] E
on A.[edfvendor] = E.[vendorID]
where f.[sgmtnumb] = 3

it's pretty basic, aside from hitting quite a few tables. I hope somebody can help!

 

[briangriffin]

Is your query returning data that has been validated? If so, will you be basing multiple reports on the same query?

 

[MBresnan]

I've manually validated the data this query returns, is that what you mean?

There will only be one report based on this data.

 

[briangriffin]

Yes, you would only want to compile the stored procedure if you're 100% confident that it's returning the data you want. To do this you would just add:

create procedure yourprocedurename as

[then your query]

However, there are a few reasons why you would not want to create a stored procedure, which is why I asked about multiple reports using the same query.

Instead, use your existing query as a SQL command within your report. Create a connection as usual and you'll see the first item is 'Add Command'. Double click on that and paste in your query. Assuming there are no syntax errors, the command will now appear as a table. The fields will appear in the Field Explorer as usual.