Author |
Topic |
yaman
Posting Yak Master
213 Posts |
Posted - 2010-07-20 : 18:22:50
|
Sir , I am creating Dynamic query .Variable are - declare @v_Date datetime,@Experience INT,@RatePerHour INT, @AnnualSalary DECIMAL ,@RateType INT,@LastContacted INTmy 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-21 : 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. |
|
|
sql-programmers
Posting Yak Master
190 Posts |
Posted - 2010-07-21 : 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 FROMdbo.tbl_Consultant TCLEFT JOIN tbl_User TU ON TU.UserID = TC.HotLIstOwnerLEFT JOIN tbl_State TS ON TS.StateID = TC.StateIDWHERE 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 1END 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 Consultantshttp://www.sql-programmers.com/ |
|
|
|
|
|