June 23, 2010 at 2:56 pm
I have a situation where I have to create a dynamic where clause dependant on params passed into sproc. I have tried creating a sproc where I declare a table variable and then depending on parameters creating an insert statement to load the table variable. The dynamic insert is held in a variable as well. I'm getting an error and the only thing I can think of would be a scope issue.
Here is how to run the sproc:
use cpr_prod
exec tempNew_usp_selClinicDepartmentFields @AssetType=N'ALL',@StatusID=N'All',@StartDate=NULL,@EndDate=NULL,@Required=N'No',@ClinicName=N'ALL',@SPM=N'ALL',@DOO=N'ALL',@Region=N'ALL'
Here is my Error:
Error #: 203 Error Severity: 16 Error State: 2 Error Line: 238 Error Message: The name 'Insert @ResultsTable
select c.SPMID
, sm.SPM
, r.RegionName
, do.podname as DOO
, sc.Category
, cm.ClinicID
,c.ClinicName as ClinicName
,CONVERT(varchar(15), cp.MoveDate, 110) as ClinicStartDate
,d.DeptName
,CASE
WHEN cp.Required = 1 THEN 'Yes'
ELSE 'No'
END AS Required
, q.Question
, CASE
When q.AnswerTypeID in (1,2,3) then cp.AnswerValue
When q.AnswerTypeID in (4,5) and cp.AnswerValue = 1 then 'Yes'
When q.AnswerTypeID in (4,5) and cp.AnswerValue = 2 then 'No'
Else cp.AnswerValue
END as Answer
--, p.' is not a valid identifier.
Below is my Sproc
USE [CPR_PROD]
GO
/****** Object: StoredProcedure [dbo].[usp_selClinicDepartmentFields] Script Date: 06/22/2010 09:16:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================================
-- Author:Jim Huffer
-- Create date: October 6, 2009
-- Description:This stored procedure provides information for a SQL Query
--report of Required fields by Department.
-- ============================================================================
--
-- ==================================================================================
-- Automatically Generated by Visual SourceSafe:
-- $Author: Jim Huffer $
-- $Date: 10/19/09 4:53p $
-- $Revision: 4 $
-- $Archive: /App Dev/SQL/MedAppsDB/CPR_PROD/StoredProcedures/usp_selClinicDepartmentFields.sql $
--
-- Module Notes:
-- ==================================================================================
--
alter PROCEDURE [dbo].[tempNew_usp_selClinicDepartmentFields]
(
@AssetType VARCHAR(25) = 'Clinic',
@StatusID VARCHAR(25) = 'Clinic Startup',
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@Required VARCHAR(10) = 'Yes',
@ClinicName VARCHAR(100) = 'All',
@spm VARCHAR(50) = 'All',
@Doo VARCHAR(50) = 'All',
@Region VARCHAR(50) = 'All',
@Debug CHAR(1) = 'N'
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ProcName nvarchar(128)
DECLARE @DBName as varchar(100)
Declare @ErrReport as varchar(8000)
set @DBName = @@Servername + '.' + DB_name()
set @ProcName = Object_Name(@@procid)
print @DBName
print @ProcName
BEGIN TRY
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='#ResultsTable')
SELECT 'tablename exists.'
ELSE
Declare @ResultsTable Table
(
SPMID bigint
,smSPM varchar(50)
,RegionName varchar(50)
,DOO varchar(50)
,Category varchar(50)
,ClinicID bigint
,ClinicName varchar(50)
,ClinicStartDate varchar(15)
,DeptName varchar(50)
,[Required] varchar(10)
,Question varchar(500)
,Answer varchar(500)
)
-- Get DOO Temporary information
SELECT DOOID, podname
INTO #tmpDOO
FROM MedBiData.mededgeods.dbo.Doo with (nolock)
WHERE DOOEmail <> 'tgee@med-edge.com'
and PodName not like '%Inactive%'
-- Get Region Temporary information
Select regionid,RegionName
INTO #tmpRegion
from MedBiData.mededgeods.dbo.Region with (nolock)
order by regionname
DECLARE @StatusInt VARCHAR(25)
IF @StatusID <> 'All'
select @StatusInt = CAST(StatusID AS VARCHAR(25))
from dbo.sys_Categories
where Category = @StatusID
ELSE
SET @StatusInt = '1,2,3,4,5'
DECLARE @ClinicSQLWhere VARCHAR(4000)
DECLARE @ProviderSQLWhere VARCHAR(4000)
DECLARE @AssetTypeIdx VARCHAR(25)
SELECT @AssetTypeIdx =
CASE
WHEN @AssetType = 'All' THEN '1,2'
WHEN @AssetType='1' THEN '1' --Clinic
WHEN @AssetType='2' THEN '2' --Provider
ELSE '1,2'
END
--PRINT @AssetType
--PRINT @AssetTypeIdx
SET @ClinicSQLWhere = 'where cm.AssetTypeID = 1 '
SET @ProviderSQLWhere = 'where cm.AssetTypeID = 2 '
SET @ClinicSQLWhere = @ClinicSQLWhere + ' and cm.AssetStatusID in (' + @StatusInt + ') '
SET @ProviderSQLWhere = @ProviderSQLWhere + ' and cm.AssetStatusID in (' + @StatusInt + ') '
-- Just Required Fields ?
If(@Required = 'Yes')
BEGIN
SET @ClinicSQLWhere = @ClinicSQLWhere + ' and cp.Required = 1 and (cp.AnswerValue is null or len(cp.AnswerValue) < 1 ) '
SET @ProviderSQLWhere = @ProviderSQLWhere + ' and cp.Required = 1 and (cp.AnswerValue is null or len(cp.AnswerValue) < 1 ) '
END
-- Just one Clinic or All
SET @ClinicSQLWhere = @ClinicSQLWhere +
CASE
WHEN @ClinicName = 'All' OR len(@ClinicName) < 1 THEN ''
WHEN len(@ClinicName) > 0 THEN '
and c.ClinicName = ' + CHAR(39) + @ClinicName + CHAR(39) + ' '
ELSE ''
END
-- If there is a date, then it is greater than or equal to that date
SET @ClinicSQLWhere = @ClinicSQLWhere +
CASE
WHEN IsNull(@StartDate,'') = ''
THEN ''
ELSECase
When IsNull(@EndDate,'') <> ''
Then' and c.ClinicStartDate >= ' +
CHAR(39) +
SUBSTRING(CONVERT(VARCHAR(25), @StartDate,120),1,10) +
CHAR(39) +
' and c.ClinicStartDate <= ' +
CHAR(39) +
SUBSTRING(CONVERT(VARCHAR(25), @EndDate,120),1,10) +
CHAR(39) +
' '
Else' and c.ClinicStartDate >= ' +
CHAR(39) +
SUBSTRING(CONVERT(VARCHAR(25), @StartDate,120),1,10) +
CHAR(39) +
' '
End
END
SET @ProviderSQLWhere = @ProviderSQLWhere +
CASE
WHEN IsNull(@StartDate,'') = ''
THEN ''
ELSECase
When IsNull(@EndDate,'') <> ''
Then' and cp.MoveDate >= ' +
CHAR(39) +
SUBSTRING(CONVERT(VARCHAR(25), @StartDate,120),1,10) +
CHAR(39) +
' and cp.MoveDate <= ' +
CHAR(39) +
SUBSTRING(CONVERT(VARCHAR(25), @EndDate,120),1,10) +
CHAR(39) +
' '
Else' and cp.MoveDate >= ' +
CHAR(39) +
SUBSTRING(CONVERT(VARCHAR(25), @StartDate,120),1,10) +
CHAR(39) +
' '
End
END
declare @SQL varchar(Max)
If (charindex('1',@AssetTypeIdx,0) > 0)
Begin
set @SQL = 'Insert @ResultsTable
select c.SPMID
, sm.SPM
, r.RegionName
, do.podname as DOO
, sc.Category
, cm.ClinicID
,c.ClinicName as ClinicName
,CONVERT(varchar(15), cp.MoveDate, 110) as ClinicStartDate
,d.DeptName
,CASE
WHEN cp.Required = 1 THEN ' + CHAR(39) + 'Yes' + CHAR(39) + '
ELSE ' + CHAR(39) + 'No' + CHAR(39) + '
END AS Required
, q.Question
, CASE
When q.AnswerTypeID in (1,2,3) then cp.AnswerValue
When q.AnswerTypeID in (4,5) and cp.AnswerValue = 1 then ' + CHAR(39) + 'Yes' + CHAR(39) + '
When q.AnswerTypeID in (4,5) and cp.AnswerValue = 2 then ' + CHAR(39) + 'No' + CHAR(39) + '
Else cp.AnswerValue
END as Answer
--, p.FirstName + ' + CHAR(39) + ' ' + CHAR(39) + ' + p.LastName as Provider_Name
from dbo.CPR_Detail (nolock) cp
inner join dbo.CPR_Master (nolock) cm on cp.CPR_ID = cm.CPR_ID
inner join dbo.Clinics (nolock) c on cm.ClinicID = c.ClinicID
left outer join dbo.SeniorPracticeManagers (nolock) sm on sm.SPMID = c.SPMID
left outer join #tmpDOO (nolock) do on do.DOOID = c.DOOID
left outer join #tmpRegion (nolock) r on r.regionid = c.RegionID
left outer join dbo.Question_MAP (nolock) qm on cp.Question_MAP_ID = qm.Question_MAP_ID and cm.AssetTypeID = qm.AssetTypeID
left outer join dbo.Departments (nolock) d on d.DeptID = qm.DeptID
left outer join dbo.Questions (nolock) q on qm.QuestionID = q.QuestionID
left outer join dbo.sys_Categories (nolock) sc on sc.StatusID = cm.AssetStatusID and cm.AssetTypeID = sc.AssetTypeID
left outer join dbo.Providers p on cm.ProviderID = p.ProviderID
' + @ClinicSQLWhere + '
' +
'order BY sm.SPM, sc.Category, r.RegionName, c.ClinicName, d.DeptName, cm.CPR_ID '
Exec @SQL
--print @SQL
End
If (charindex('2',@AssetTypeIdx,0) > 0)
Begin
set @SQL = 'Insert @ResultsTable
select c.SPMID
,sm.SPM
,r.RegionName
,do.podname as DOO
,sc.Category
,cm.ClinicID
,p.FirstName + ' + CHAR(39) + ' ' + CHAR(39) + ' + p.LastName as ClinicName
,CONVERT(varchar(15), c.ClinicStartDate, 110) as ClinicStartDate
,d.DeptName
,CASE
WHEN cp.Required = 1 THEN ' + CHAR(39) + 'Yes' + CHAR(39) + '
ELSE ' + CHAR(39) + 'No' + CHAR(39) + '
END AS Required
,q.Question
,CASE
When q.AnswerTypeID in (1,2,3) then cp.AnswerValue
When q.AnswerTypeID in (4,5) and cp.AnswerValue = 1 then ' + CHAR(39) + 'Yes' + CHAR(39) + '
When q.AnswerTypeID in (4,5) and cp.AnswerValue = 2 then ' + CHAR(39) + 'No' + CHAR(39) + '
Else cp.AnswerValue
END as Answer
from dbo.CPR_Detail (nolock) cp
inner join dbo.CPR_Master (nolock) cm on cp.CPR_ID = cm.CPR_ID
inner join dbo.Clinics (nolock) c on cm.ClinicID = c.ClinicID
left outer join dbo.SeniorPracticeManagers (nolock) sm on sm.SPMID = c.SPMID
left outer join #tmpDOO (nolock) do on do.DOOID = c.DOOID
left outer join #tmpRegion (nolock) r on r.regionid = c.RegionID
left outer join dbo.Question_MAP (nolock) qm on cp.Question_MAP_ID = qm.Question_MAP_ID and cm.AssetTypeID = qm.AssetTypeID
left outer join dbo.Departments (nolock) d on d.DeptID = qm.DeptID
left outer join dbo.Questions (nolock) q on qm.QuestionID = q.QuestionID
left outer join dbo.sys_Categories (nolock) sc on sc.StatusID = cm.AssetStatusID and cm.AssetTypeID = sc.AssetTypeID
left outer join dbo.Providers p on cm.ProviderID = p.ProviderID
' + @ProviderSQLWhere + '
' +
'order BY sm.SPM, sc.Category, r.RegionName, c.ClinicName, d.DeptName, cm.CPR_ID '
Exec @SQL
--print @SQL
End
Select * from @ResultsTable
END TRY
BEGIN CATCH
Set @ErrReport = 'Error #: ' + Convert(varchar(50),ERROR_NUMBER())
Set @ErrReport = @ErrReport + ' Error Severity: ' + Convert(varchar(50),ERROR_SEVERITY())
Set @ErrReport = @ErrReport + ' Error State: ' + Convert(varchar(50),ERROR_STATE())
Set @ErrReport = @ErrReport + ' Error Line: ' + Convert(varchar(50),ERROR_LINE())
Set @ErrReport = @ErrReport + ' Error Message: ' + Convert(varchar(7000),ERROR_MESSAGE())
--Exec [ApplicationsMaster].[dbo].[usp_writeMessage] 'SQL Server', @DBName, @ProcName,'Error',@ErrReport
print @ErrReport
END CATCH
END
June 24, 2010 at 1:03 am
This is a scope problem.The table variable declared is outside the scope of the dynamic SQL.
Either declare the table variable inside the dynamic SQL or if functionality requires it outside,
use #temp.
June 28, 2010 at 9:17 am
I agree its a scope issue, but just out of curiousity, why is the procedure inserting into a table variable or #temp table at all?
All the procedure does is return the contents of the table with a SELECT at the end. If that's all it is intended to do, then dynamic SQL can can be executed without the INSERT.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 28, 2010 at 10:38 pm
dndaughtery (6/23/2010)
I
set @SQL = 'Insert @ResultsTable
select c.SPMID
, sm.SPM
, r.RegionName
, do.podname as DOO
, sc.Category
..... ....... ............
Select * from @ResultsTable
Instead of trying to insert into the table variable inside the dynamic query,you can try using
INSERT INTO @ResultsTable exec(@SqlQuery)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply