Problem w/ dynamic query w/ error

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 2000 Forums

Transact-SQL (2000)

Problem w/ dynamic query w/ error

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2005-09-05 : 09:44:30

I'm having some trouble building my dynamic stored procedure. I have the following parameters which I may need to filter for on my query. I'm basically wanting to search for a specific customer (CustID) or All customers.

My error is posted below.

-----Query Code----
DECLARE
@intErrorCode int,
@PID int,
@PActive bit,
@CustID int,
@PrjPhaseID int,
@PMemID int,
@StartDate datetime,
@EndDate datetime,
@SQL nvarchar(4000),
@WHR nvarchar(4000),
@debug int

SET @debug = 0
-- THESE VALUES ARE FOR TESTING
SET @PID = 1000
SET @PActive = 1
SET @CustID = NULL
SET @PrjPhaseID = NULL
SET @PMemID = NULL
SET @StartDate = '7/1/2005'
SET @EndDate = '7/8/2005'
SET NOCOUNT ON

Select @intErrorCode = @@Error,
@SQL = 'SET QUOTED_IDENTIFIER OFF SELECT
T.TimeLogID, p.ProjectID, p.ProjectName, c.CustomerID, c.CustomerName,
T.ProjectPhaseID, pp.PhaseName, pp.PhaseAbbr, T.ProjectMemberID, u.UserID,
u.usrDisplayName, pm.Rate, T.TimeLogEntryDate, T.TimeLogWork, T.TimeLogDetail

FROM
tbl_ProjectPhases pp INNER JOIN
tbl_TimeLog T INNER JOIN
tbl_ProjectMembers pm ON T.ProjectMemberID = pm.ProjectMemberID INNER JOIN
tbl_Users u ON pm.UserID = u.UserID ON pp.PhaseID = T.ProjectPhaseID INNER JOIN
tbl_Projects p ON pp.ProjectID = p.ProjectID INNER JOIN
tbl_Customers c ON p.CustomerID = c.CustomerID',
@WHR = ''

IF @debug <> 1 select @SQL SQL

--Project ID
IF @intErrorCode = 0 AND @PID is NOT NULL
Begin
SET @WHR = @WHR + ' p.ProjectID = ' + @PID + ' AND'
Select @intErrorCode = @@Error
End

... and the query goes on from here

Error:
Server: Msg 245, Level 16, State 1, Line 45
Syntax error converting the nvarchar value ' p.ProjectID = ' to a column of data type int.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 10:33:09

your @PID is of int datatype and therefore you must convert it to varchar:

SET @WHR = @WHR + ' p.ProjectID = ' + convert(varchar(20), @PID) + ' AND'

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources