Dynamic Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Query

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-01 : 12:10:21

Hi There,
I am trying to create a dynamic sql Query in a sql procedure but I keep getting an error of "An item with the same key has already been added" This is my Query:

ALTER PROCEDURE [dbo].[sp_SearchtbMatTrackTest]
(
@LocationId INT = NULL,
@RespPersonId INT = NULL,
@ActioneeId INT = NULL,
@SourceId INT = NULL
)
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = N'SELECT'
+ ' tbMatTrack.ActionId,'
+ ' tbMatTrack.Description,'
+ ' tbMatTrack.ResponsibleId,'
+ ' tbMatOrg.LastName,'
+ ' tbMatTrack.ActioneeId,'
+ ' tbMatOrg1.LastName,'
+ ' tbMatTrack.TypeId,'
+ ' tbMatTypes.TypeDescription,'
+ ' tbMatTrack.Risk,'
+ ' tbMatRisk.Description As RiskDescription,'
+ ' tbMatTrack.DateRaised,'
+ ' tbMatTrack.PlannedStartDate,'
+ ' tbMatTrack.ActualStartDate,'
+ ' tbMatTrack.PlannedCompletionDate,'
+ ' tbMatTrack.ActualCompletionDate,'
+ ' tbMatTrack.EditorComments,'
+ ' tbMatTrack.EditorActions,'
+ ' tbMatTrack.SourceId,'
+ ' tbMatSource.SourceDescription,'
+ ' tbMatTrack.PerComplete,'
+ ' tbMatTrack.LocationId,'
+ ' tbMatLocation.Location,'
+ ' tbMatLocation.Abbriviation'
+ ' From'
+ ' tbMatTrack Inner Join'
+ ' tbMatOrg On tbMatTrack.ResponsibleId = tbMatOrg.PersonnelId Inner Join'
+ ' tbMatOrg tbMatOrg1 On tbMatTrack.ActioneeId = tbMatOrg1.PersonnelId Inner Join'
+ ' tbMatTypes On tbMatTrack.TypeId = tbMatTypes.TypeId Inner Join'
+ ' tbMatRisk On tbMatTrack.Risk = tbMatRisk.RiskId Inner Join'
+ ' tbMatSource On tbMatTrack.SourceId = tbMatSource.SourceId Inner Join'
+ ' tbMatLocation On tbMatTrack.LocationId = tbMatLocation.LocationId'
+ ' WHERE 1=1'

SET @SQL = @SQL + CASE WHEN @LocationID IS NOT NULL
THEN ' AND tbMatTrack.LocationId = ' + CAST(@LocationID AS NVARCHAR(10))
ELSE '' END
SET @SQL = @SQL + CASE WHEN @RespPersonId IS NOT NULL
THEN ' AND tbMatTrack.ResponsibleId = ' + CAST(@RespPersonId AS NVARCHAR(10))
ELSE '' END
SET @SQL = @SQL + CASE WHEN @ActioneeId IS NOT NULL
THEN ' AND tbMatTrack.ActioneeId = ' + CAST(@ActioneeId AS NVARCHAR(10))
ELSE '' END
SET @SQL = @SQL + CASE WHEN @SourceId IS NOT NULL
THEN ' AND tbMatTrack.SourceId = ' + CAST(@SourceId AS NVARCHAR(10))
ELSE '' END
EXECUTE (@SQL)
END

I have tested the code with a basic select statment into a single table and the dynamic part works well. My problems started when I needed to get info from additional tables and needed the inner join stuff.
If anyone can see what is wrong I would really appreciate their input.

Many thanks.

Best Regards,

Steve

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-01 : 12:18:27

This is the code that runs fine and dynamically selects based on what I have filled in for any of the parameters:

ALTER PROCEDURE [dbo].[sp_SearchtbMatTrackTest]
(
@LocationId INT = NULL,
@RespPersonId INT = NULL,
@ActioneeId INT = NULL,
@SourceId INT = NULL
)
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = N'SELECT'
+ ' tbMatTrack.ActionId,'
+ ' tbMatTrack.Description,'
+ ' tbMatTrack.ResponsibleId,'
+ ' tbMatTrack.ActioneeId,'
+ ' tbMatTrack.TypeId,'
+ ' tbMatTrack.DateRaised,'
+ ' tbMatTrack.PlannedStartDate,'
+ ' tbMatTrack.ActualStartDate,'
+ ' tbMatTrack.PlannedCompletionDate,'
+ ' tbMatTrack.ActualCompletionDate,'
+ ' tbMatTrack.EditorComments,'
+ ' tbMatTrack.EditorActions,'
+ ' tbMatTrack.SourceId,'
+ ' tbMatTrack.PerComplete,'
+ ' tbMatTrack.LocationId'
+ ' From'
+ ' tbMatTrack'
+ ' WHERE 1=1'

SET @SQL = @SQL + CASE WHEN @LocationID IS NOT NULL
THEN ' AND tbMatTrack.LocationId = ' + CAST(@LocationID AS NVARCHAR(10))
ELSE '' END
SET @SQL = @SQL + CASE WHEN @RespPersonId IS NOT NULL
THEN ' AND tbMatTrack.ResponsibleId = ' + CAST(@RespPersonId AS NVARCHAR(10))
ELSE '' END
SET @SQL = @SQL + CASE WHEN @ActioneeId IS NOT NULL
THEN ' AND tbMatTrack.ActioneeId = ' + CAST(@ActioneeId AS NVARCHAR(10))
ELSE '' END
SET @SQL = @SQL + CASE WHEN @SourceId IS NOT NULL
THEN ' AND tbMatTrack.SourceId = ' + CAST(@SourceId AS NVARCHAR(10))
ELSE '' END
EXECUTE (@SQL)
END

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources