April 14, 2011 at 7:31 am
Is it possible to use a stored procedure containing dynamic SQL, such as the one below, as a dataset for an SSRS report? I can't seem to get this to work... I get an error when I try to preview the report -
"Report Item expressions can only refer to fields within the current dataset scope..."
Thanks.
[font="Courier New"]SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[spc_FindCurrentDoctors]
(
@FirstName varchar(50) = NULL
,@Surname varchar(50) = NULL
,@JobTitle varchar(50)= NULL
,@StartedEmployment varchar(10) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql =
"SELECT e.Firstname
,e.Surname
--,[job_id]
,j.[Employee_id]
,COALESCE([job_title],'Missing Job title') AS Job_Title -- this is null in two cases
--,[company]
,CAST([date_employment_commenced] AS DATE) AS Started_Employment
FROM [SessionalDoctorsMIS].[dbo].[Job_details] j
inner join dbo.employee e on e.employee_id = j.employee_id
where j.[date_employment_commenced] is not null -- this is null in some cases
and j.[date_employment_finished] is null"
IF @FirstName IS NOT NULL
SELECT @sql = @sql + " and e.Firstname like '" + @Firstname + "%'"
IF @Surname IS NOT NULL
SELECT @sql = @sql + " and e.Surname like '" + @Surname + "%'"
IF @JobTitle IS NOT NULL
SELECT @sql = @sql + " and COALESCE([job_title],'Missing Job title') like '" + @JobTitle + "%'"
IF @StartedEmployment IS NOT NULL
SELECT @sql = @sql + " and CAST([date_employment_commenced] AS DATE) >= '" + @StartedEmployment + "'"
SELECT @paramlist = '@FirstName nvarchar(50),@Surname nvarchar(50),@JobTitle nvarchar(50),@StartedEmployment date'
exec sp_executesql @sql,@paramlist,@Firstname,@Surname,@JobTitle,@StartedEmployment
SET QUOTED_IDENTIFIER ON
SET NOCOUNT OFF
END
GO[/font]
April 14, 2011 at 7:43 am
Fixed it... I've inserted the output into a table variable.
Thanks.
April 26, 2011 at 2:51 pm
If you're able, avoid the dynamic sql .... try something like this in your where clause.
WHERE ...
...
AND CASEWHEN NOT(@FirstName) IS NULL
AND e.FirstName LIKE @Firstname THEN 1
END=1
ANDCASEWHEN NOT(@Surname) IS NULL
AND e.Surname LIKE @SurnameTHEN 1
END=1
April 28, 2011 at 5:05 am
Thanks Scott.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply