multi search procedure - 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

multi search procedure

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-03 : 05:06:12

ALTER PROCEDURE usp_r_search_consultant
@head_line varchar(150)
,@func_area varchar(150)
,@loc varchar(50)
,@exp varchar(50)
,@k_skills varchar(50)
,@operator char(1)
--select * from RPO.dbo.REQUIREMENT_RESOURCE
AS

if(@operator='A')
BEGIN
declare @sql varchar(1000)
select @sql= --'select * from RPO.dbo.REQUIREMENT_RESOURCE where 1=1'
'''SELECT Resume_head_line,Employer,CTC,Qualification1'' + '','' + ''Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1'' '
print @sql

IF @head_line !=''
begin
SELECT @sql = @sql + ' and head_line='''+@head_line+''''

print'1'
print @sql
exec sp_executesql @sql
end

END
whats wrong in the procedure

Malathi Rao

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:11:49

it's using dynamic sql??

tell us what is the error you get and we'll be able to help you more.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-03 : 05:13:29

what is problem u have...any errors or didn't get the result???????

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-03 : 05:14:54

quote:
Originally posted by spirit1

it's using dynamic sql??

tell us what is the error you get and we'll be able to help you more.
error i get when i run above procs

'SELECT Resume_head_line,Employer,CTC,Qualification1' + ',' + 'Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1'
1
'SELECT Resume_head_line,Employer,CTC,Qualification1' + ',' + 'Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1' and head_line='QWEE'
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
No rows affected.
(0 row(s) returned)
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Malathi Rao

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:16:31

it should be:
declare @sql nvarchar(1000)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-03 : 05:22:00

quote:
Originally posted by spirit1

it should be:
declare @sql nvarchar(1000)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

after declaring @sql nvarchar(1000)
i got the following error
'SELECT Resume_head_line,Employer,CTC,Qualification1' + ',' + 'Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1'
1
'SELECT Resume_head_line,Employer,CTC,Qualification1' + ',' + 'Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1' and Resume_head_line='QWEE'
Incorrect syntax near 'SELECT Resume_head_line,Employer,CTC,Qualification1'.
No rows affected.

Malathi Rao

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-03 : 05:27:49

alter PROCEDURE usp_r_search_consultant
@head_line varchar(150)
,@func_area varchar(150)
,@loc varchar(50)
,@exp varchar(50)
,@k_skills varchar(50)
,@operator char(1)
--select * from RPO.dbo.REQUIREMENT_RESOURCE
AS

if(@operator='A')
BEGIN
declare @sql varchar(1000)
select @sql= --'select * from RPO.dbo.REQUIREMENT_RESOURCE where 1=1'
'SELECT Resume_head_line,Employer,CTC,Qualification1' + ',' + 'Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1'
print @sql

IF @head_line !=''
begin
SELECT @sql = @sql + ' and head_line=' +@head_line

print'1'
print @sql
exec (@sql )
end

END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:29:28

i thought that you were using double qutes there but it looks like you're using 2 single quotes every time.
why?

declare @sql varchar(1000)
select @sql=
'SELECT Resume_head_line,Employer,CTC,Qualification1,Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1 '
print @sql

IF @head_line != ''
begin
SELECT @sql = @sql + ' and head_line = ''' + @head_line + ''''

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-03 : 05:33:18

quote:
Originally posted by spirit1

i thought that you were using double qutes there but it looks like you're using 2 single quotes every time.
why?

declare @sql varchar(1000)
select @sql=
'SELECT Resume_head_line,Employer,CTC,Qualification1,Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1 '
print @sql

IF @head_line != ''
begin
SELECT @sql = @sql + ' and head_line = ''' + @head_line + ''''

sorry i want to concate two fields data Qualification1,Qualification2 to single field as Education
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Malathi Rao

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-03 : 05:42:09

quote:
Originally posted by jogin malathi

quote:
Originally posted by spirit1

i thought that you were using double qutes there but it looks like you're using 2 single quotes every time.
why?

declare @sql varchar(1000)
select @sql=
'SELECT Resume_head_line,Employer,CTC,Qualification1,Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1 '
print @sql

IF @head_line != ''
begin
SELECT @sql = @sql + ' and head_line = ''' + @head_line + ''''

sorry i want to concate two fields data Qualification1,Qualification2 to single field as Education
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Malathi Rao

sorry i want to concate two fields data Qualification1,Qualification2 to single field as Education

Malathi Rao

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:50:56

'... CTC,Qualification1 + '','' + Qualification2 AS Education ... '

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-03 : 06:37:04

quote:
Originally posted by spirit1

'... CTC,Qualification1 + '','' + Qualification2 AS Education ... '

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Thanks its working

Malathi Rao

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 08:36:24

Another classic example of something short, simple and easy made complex and confusing with unnecessary dynamic sql ....

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 04:05:20

See if you really need Dynamic SQL
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources