I have no programming background and recently found myself in a job where I need to learn SQL and crystal reports. What I have learned so far, I taught myself. I have a stored procedure that I used to create a report in Crystal, but it is not returning any data. I was told that I need to pass the jobid from crystal to the stored procedure, but have no idea how to do that. Can anyone help??
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
passing parameter from Crystal to stored procedure
- Thread starter radio16
- Start date
CoSpringsGuy
IS-IT--Management
indicate your Crystal version and paste your current SQL command and I know someone on here will trouble shoot it for you.
_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection
_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection
- Thread starter
- #3
I am using Crystal Reports 8.5 My SQL:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.RWJ_PhysicianGroupMembers_SP
(
@JobID int
)
AS
BEGIN
Declare @ReportUserGUID numeric(16,0)
SET NOCOUNT ON
Select @ReportUserGUID = HVCRequestedReport.UserGUID
from HVCRequestedReport
where HVCRequestedReport.Jobid = @jobid
Select sg.Code as SecurityGroupCode,
sg.Description SecurityGroupDesc,
u.LastName as UserLastName,
u.FirstName as UserFirstName
from CV3UserSecurityGroup usg
join CV3SecurityGroup sg on
sg.GUID = usg.SecurityGroupGUID
and sg.active = 1
join CV3User u on
u.GUID = usg.UserGUID
and u.active = 1
where sg.code in
( Select sg2.Code
from CV3UserSecurityGroup usg2
join CV3SecurityGroup sg2
on sg2.GUID = usg2.SecurityGroupGUID
and sg2.Code like 'NB-PG%'
and sg2.active = 1
where usg2.UserGUID = @ReportUserGUID
and (usg2.ExpiryDtm is null or usg2.ExpiryDtm > getdate())
and usg2.active = 1
)
and (usg.ExpiryDtm is null or usg.ExpiryDtm > getdate())
order by sg.code, u.LastName, u.FirstName
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.RWJ_PhysicianGroupMembers_SP
(
@JobID int
)
AS
BEGIN
Declare @ReportUserGUID numeric(16,0)
SET NOCOUNT ON
Select @ReportUserGUID = HVCRequestedReport.UserGUID
from HVCRequestedReport
where HVCRequestedReport.Jobid = @jobid
Select sg.Code as SecurityGroupCode,
sg.Description SecurityGroupDesc,
u.LastName as UserLastName,
u.FirstName as UserFirstName
from CV3UserSecurityGroup usg
join CV3SecurityGroup sg on
sg.GUID = usg.SecurityGroupGUID
and sg.active = 1
join CV3User u on
u.GUID = usg.UserGUID
and u.active = 1
where sg.code in
( Select sg2.Code
from CV3UserSecurityGroup usg2
join CV3SecurityGroup sg2
on sg2.GUID = usg2.SecurityGroupGUID
and sg2.Code like 'NB-PG%'
and sg2.active = 1
where usg2.UserGUID = @ReportUserGUID
and (usg2.ExpiryDtm is null or usg2.ExpiryDtm > getdate())
and usg2.active = 1
)
and (usg.ExpiryDtm is null or usg.ExpiryDtm > getdate())
order by sg.code, u.LastName, u.FirstName
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
BobHartung
MIS
When you run the report in Crystal, do you get prompted for JobID? Also, I noticed that @JobID and @ReportUserGUID are different data types. This probably isn't your issue, but it would probably be best to make them the same data type.
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question