SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yaman
Posting Yak Master

India
213 Posts

Posted - 07/20/2010 :  18:22:50  Show Profile  Reply with Quote
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
8764 Posts

Posted - 07/21/2010 :  01:24:38  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Go to Top of Page

sql-programmers
Posting Yak Master

USA
190 Posts

Posted - 07/21/2010 :  01:24:59  Show Profile  Visit sql-programmers's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000