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

General SQL Server Forums

New to SQL Server Programming

Dynamic Query

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 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
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 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources