| Author |
 Topic  |
|
|
yaman
Posting Yak Master
India
213 Posts |
 Posted - 07/20/2010 : 18:22:50
|
Sir ,
I am creating Dynamic query .
Variable are -
declare @v_Date datetime,@Experience INT,@RatePerHour INT, @AnnualSalary DECIMAL ,@RateType INT,@LastContacted INT
my query is -
('SELECT TC.ConsultantID,ISNULL(TC.FirstName,'' '') + '' '' +ISNULL(TC.LastName,'' '') AS ConsultantName
,TC.City, TC.Email1,TC.WorkStatus,TC.PrimarySkill,
TC.SecondarySkill,TC.RatePerHour,TC.TotalExperience,TC.CurrentZIP,TC.IsAvailable,
TC.HotListOwner,TU.NickName,TC.JobTitle,TC.AnnualSalary,TC.RateType,
TS.StateAbbrivation FROM
dbo.tbl_Consultant TC
LEFT JOIN tbl_User TU ON TU.UserID = TC.HotLIstOwner
LEFT JOIN tbl_State TS ON TS.StateID = TC.StateID
WHERE TC.PrimarySkill like ''%'+@Skills+'%''
AND TC.IsAvailable = COALESCE(cast('+@IsAvailable+' as nvarchar(10)),TC.IsAvailable)
AND CONVERT(NVARCHAR(100),cast(TC.TotalExperience as INT) ) > = CONVERT( NVARCHAR(100),Cast('+@Experience+' as INT ))
AND CONVERT(NVARCHAR(10),TC.RatePerHour) < = COALESCE(Convert(nvarchar(10),'+@RatePerHour+'),TC.RatePerHour)
AND TC.AnnualSalary < = COALESCE(CONVERT(NVARCHAR(10),'+@AnnualSalary+'),TC.AnnualSalary)
AND RateType = COALESCE(CONVERT(NVARCHAR(10),'+@RateType+'),TC.RateType)
AND TC.ActiveFlag = ''1''
ORDER BY CASE WHEN TC.HotListOwner = @UserID THEN 1
END DESC,
DATEDIFF(day,'+@v_Date+',COnvert(Varchar(10),ISNULL(TC.ModifiedDate,TC.CreatedDate),101)) ASC,
TC.HotListOwner DESC
')
Error is - Conversion failed when converting the nvarchar value 'SELECT TC.ConsultantID,ISNULL(TC.FirstName,' ') + ' ' +ISNULL(TC.LastName,' ') AS ConsultantName
,TC.City, TC.Email1,TC.WorkStatus,TC.PrimarySkill,
TC.SecondarySkill,TC.RatePerHour,TC.TotalExperience,TC.CurrentZIP,TC.IsAvailable,
TC.HotListOwner,TU.NickName,TC.JobTitle,TC.AnnualSalary,TC.RateType,
TS.StateAbbrivation FROM
dbo.tbl_Consultant TC
LEFT JOIN tbl_User TU ON TU.UserID = TC.HotLIstOwner
LEFT JOIN tbl_State TS ON TS.StateID = TC.StateID
WHERE TC.PrimarySkill like '%since%'
AND TC.IsAvailable = COALESCE(cast(1 as nvarchar(10)),TC.IsAvailable)
AND CONVERT(NVARCHAR(100),cast(TC.TotalExperience as INT) ) > = CONVERT( NVARCHAR(100),Cast(' to data type int.
Yaman |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 07/21/2010 : 01:24:38
|
Assign your dynamic code to a variable and then print it instead of trying to execute.
The printed code will show you where the single quotes are not set correctly.
No, you're never too old to Yak'n'Roll if you're too young to die. |
Edited by - webfred on 07/21/2010 01:25:05 |
 |
|
|
sql-programmers
Posting Yak Master
USA
189 Posts |
Posted - 07/21/2010 : 01:24:59
|
Try this
'SELECT TC.ConsultantID,ISNULL(TC.FirstName,'' '') + '' '' +ISNULL(TC.LastName,'' '') AS ConsultantName
,TC.City, TC.Email1,TC.WorkStatus,TC.PrimarySkill,
TC.SecondarySkill,TC.RatePerHour,TC.TotalExperience,TC.CurrentZIP,TC.IsAvailable,
TC.HotListOwner,TU.NickName,TC.JobTitle,TC.AnnualSalary,TC.RateType,
TS.StateAbbrivation FROM
dbo.tbl_Consultant TC
LEFT JOIN tbl_User TU ON TU.UserID = TC.HotLIstOwner
LEFT JOIN tbl_State TS ON TS.StateID = TC.StateID
WHERE TC.PrimarySkill like ''%'+@Skills+'%''
AND TC.IsAvailable = COALESCE(cast('+@IsAvailable+' as nvarchar(10)),TC.IsAvailable)
AND CONVERT(NVARCHAR(100),cast(TC.TotalExperience as INT) ) > = CONVERT( NVARCHAR(100),Cast('+CONVERT(VARCHAR,@Experience)+' as INT ))
AND CONVERT(NVARCHAR(10),TC.RatePerHour) < = COALESCE(Convert(nvarchar(10),'+CONVERT(VARCHAR,@RatePerHour)+'),TC.RatePerHour)
AND TC.AnnualSalary < = COALESCE(CONVERT(NVARCHAR(10),'+CONVERT(VARCHAR,@AnnualSalary)+'),TC.AnnualSalary)
AND RateType = COALESCE(CONVERT(NVARCHAR(10),'+CONVERT(VARCHAR,@RateType)+'),TC.RateType)
AND TC.ActiveFlag = ''1''
ORDER BY CASE WHEN TC.HotListOwner = @UserID THEN 1
END DESC,
DATEDIFF(day,'+CONVERT(VARCHAR,@v_Date,101)+',COnvert(Varchar(10),ISNULL(TC.ModifiedDate,TC.CreatedDate),101)) ASC,
TC.HotListOwner DESC
'
SQL Server Programmers and Consultants
http://www.sql-programmers.com/ |
|
|
| |
Topic |
|
|
|
Dynamic Query
