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
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  00:48:25  Show Profile  Reply with Quote
ALTER procedure [search_fields]
(
@drpSelectValue VARCHAR(30) = null, -- this is column name of employees table
@txtValue as varchar(10)=null -- value to be searched in corresponding column data
)
As
begin

if @drpSelectValue = 'last_name'
begin

select *from employees where
@drpSelectValue like (case when @txtValue is not null then @txtValue+'%' else @drpSelectValue end)
end
else
Begin
select * from employees where
@drpSelectValue=(case when @txtValue is not null then @txtValue else @drpSelectValue end)
End
End

How to make that red part as a column type

--
Chandu

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 11/28/2012 :  01:11:29  Show Profile  Reply with Quote
Dynamic SQL is the only way
http://www.sommarskog.se/dynamic_sql.html


KH
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  01:24:09  Show Profile  Reply with Quote
Thank you Khtan... I rectified this problem with dynamic query...

ALTER procedure [search_fields]
(
@drpSelectValue VARCHAR(50) = null,
@txtValue as varchar(10)=null
)
As 
begin
DECLARE @SQL varchar(max) =''
if @drpSelectValue = 'username'
begin
      SET @SQL = 'select * from Userdetails  where  ' + @drpSelectValue +' like (case when '''+@txtValue +''' is not null then '''+ @txtValue+ '%'' else ' + @drpSelectValue +' end)'
end 
else 
Begin
      SET @SQL = 'select * from Userdetails  where  ' + @drpSelectValue +' = (case when '''+@txtValue +''' is not null then '''+ @txtValue+ ''' else ' + @drpSelectValue +' end)'
End
      EXEC (@SQL)
End
GO

exec search_fields 'username', 'king'
exec search_fields 'username', null -- Problem

There is one more problem when i pass NULL to @txtValue..



--
Chandu
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 11/28/2012 :  01:53:23  Show Profile  Reply with Quote
what do you expect the result to be when you pass in NULL value to the @txtValue ?

you have to handle that logic when you generate the dynamic sql statement


KH
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  02:23:48  Show Profile  Reply with Quote
quote:
Originally posted by khtan

what do you expect the result to be when you pass in NULL value to the @txtValue ?

you have to handle that logic when you generate the dynamic sql statement


Ya i knew that. If @txtValue is null then all records sholud be in output..

For that i changed my query as follows:

if @drpSelectValue = 'last_name' AND @txtValue IS NOT NULL 
begin
	SET @SQL = 'select * from employees where  ' + @drpSelectValue +' like '''+ @txtValue + '%'' '
end 
ELSE 
	SET @SQL = 'select * from employees'


But am eager to know other solutions..
Any way Thank you very much for ur suggestion

--
Chandu
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 11/28/2012 :  02:55:50  Show Profile  Reply with Quote
or you can use sp_executesql and pass in the @txtValue as a parameter

SET @SQL =
'
    select * from employees where @txtValue is NULL or ' + @drpSelectValue ' = @txtValue'
'



KH
Time is always against us

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