Wednesday, March 21, 2012

datetime problems with a searching query

hi;

i want to get some results for new my stored proce is below;

create Procedure HaberleriGetir

@.Kelime varchar(50),

@.tarih1 smalldatetime,

@.tarih2 smalldatetime,

@.KatID int,

@.lang char(5)

as

if @.lang = 'Hepsi'

select * from Haberler where HKatID = @.KatID and Metin like %@.Kelime% or Baslik like %@.Kelime% and Tarih between @.tarih1 and @.tarih2

else

select * from Haberler where HKatID = @.KatID and lang = @.lang and Metin like %@.Kelime% or Baslik like %@.Kelime% and Tarih between @.tarih1 and @.tarih2

incorrect near @.Kelime....

and i can't get a result between two dates like @.tarih1 & @.tarih2

Where are your quotes?

It should read

Metin like '%@.Kelime%'

|||You have to use Dynamic SQL like this: SET @.sqlstr= 'select * from Haberler where HKatID = ' + CONVERT(VARCHAR, @.KatID) + ' and Metin like ''%' + @.Kelime + '%'' or Baslik like ''%' + @.Kelime '%'' and Tarih between ''' + CONVERT(VARCHAR, @.tarih1) + ' and ' + CONVERT(VARCHAR, @.tarih2)........ then execute the sql statement by using the following SQL statement EXEC(@.sqlstr).... (NOTE: Declare the variable @.sqlstr before as VARCHAR(8000))

No comments:

Post a Comment