problem with dynamic SP - 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

problem with dynamic SP

Author

Topic

lirsari
Starting Member

2 Posts

Posted - 2010-04-07 : 05:17:28

hi,

i'm receiving this error:
Conversion failed when converting the varchar value 'select distinct u.id, u.firstname, replace(u.company,1,'yes') as company, r.Ethnicity_name, a.appliedDate, t.positionTitle, p.Position_Name, js.jobSpecialization_name, ve.*, o.salary, replace(negotiable,'off','Negotiable') as negotiable,
c.city, s.State_name from User u, jobApplication a, jobPost j, Career t, Education e, Others o, Contact c, Position p, jobSpecialization js, viewEducation ve, State s, Ethnicity r where u.id = a.id and a.jobPostID = j.jobPostID and u.id = t.userid and u.id = e.userid and u.id = o.userid and u.id = c.userid and t.Position_Id = p.Position_Id and t.jobSpecialization_Id = js.jobSpecialization_Id and e.userid = ve.id and u.Ethnicity = r.Ethnicity_id and c.state = s.State_id and ( u.firstname like '%muna%' or u.lastname like '%muna%' )
and j.jobPostID = ' to data type int.

when running this SP:
ALTER procedure [dbo].[Recruit]
(
@jid int,
@name varchar(200),
@ic varchar(200),
@race int,
@gender varchar(200)
)
as
declare @sql nvarchar(4000)

select @sql = 'select distinct u.id, u.firstname, replace(u.tm,1,''yes'') as tm, r.Ethnicity_name, a.appliedDate, t.positionTitle, p.Position_Name, js.jobSpecialization_name, ve.*, o.salary, replace(negotiable,''off'',''Negotiable'') as negotiable, c.city, s.State_name from Users u, jobApplication a, jobPost j, Career t, Education e, Others o, Contact c, Position p, jobSpecialization js, viewEducation ve, State s, Ethnicity r where u.id = a.id and a.jobPostID = j.jobPostID and u.id = t.userid and u.id = e.userid and u.id = o.userid and u.id = c.userid and t.Position_Id = p.Position_Id and t.jobSpecialization_Id = js.jobSpecialization_Id and e.userid = ve.id and u.Ethnicity = r.Ethnicity_id and c.state = s.State_id and ( u.firstname like ''%'+@name+'%'' or u.lastname like ''%'+@name+'%'' ) and j.jobPostID = '+ @jid

if (@race is null) and (@gender is null)
select @sql = @sql + ''
-- else
-- if @race is not null
-- select @sql = @sql + ' and u.Ethnicity = ' + convert(varchar(2),@race)

exec (@sql)

return @@error

it works fine if i don't put this on >> and j.jobPostID = '+ @jid

please help..

Kristen
Test

22859 Posts

Posted - 2010-04-07 : 05:21:17

select @sql = 'select distinct ... and j.jobPostID = '+ CONVERT(varchar(20), @jid)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 05:24:31

thats because you're trying to concatenate a string value (query string) to id variable (@jid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-07 : 05:28:15

Mske sure you read this article fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

lirsari
Starting Member

2 Posts

Posted - 2010-04-07 : 06:58:02

thank u so very much..the article really helps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources