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  SQL Server 2000 Forums  SQL Server Development (2000)  @sqlstring

Author  Topic  martinfa Starting Member 1 Post Posted - 2001-06-21 : 12:48:28 anyone tell me why this doesn't work please. old code: CREATE PROCEDURE PUBLICdailyInstrumentSearch @SearchData varchar(1000), @searchtype char(4) AS begin --wildcard replacement select @searchData = replace(@searchdata, '*', '%') declare @today datetime, @tomorrow datetime, @chartoday varchar(100), @chartomorrow varchar(100) if @searchtype = 'name' begin --we are always going to need to return the most current date --get the first most recent date that matches the search select top 1 @today = d.[date] from daily d, staticdata s where s.fullname like @searchdata and d.[ticker symbol] = s.epic order by [date] desc --what is tommorrows date select @tomorrow = dateadd(day, 1, @today) select @chartoday = cast(datepart(month,@today) as varchar(20)) + '/' + cast(datepart(day,@today) as varchar(20)) + '/' + cast(datepart(yyyy,@today) as varchar(20)) select @chartomorrow = cast(datepart(month,@tomorrow) as varchar(20)) + '/' + cast(datepart(day,@tomorrow) as varchar(20)) + '/' + cast(datepart(yyyy,@tomorrow) as varchar(20)) --now we have a pukka date range, return all the results select distinct d.[ticker symbol], d.[date], s.fullname, s.type from daily d, staticdata s where s.fullname like @searchdata and d.[ticker symbol] = s.epic and d.[date] between @chartoday and @chartomorrow end else begin --we are always going to need to return the most current date --get the first most recent date that matches the search select top 1 @today = d.[date] from daily d, staticdata s where s.epic like @searchdata and d.[ticker symbol] = s.epic order by [date] desc --what is tommorrows date select @tomorrow = dateadd(day, 1, @today) select @chartoday = cast(datepart(month,@today) as varchar(20)) + '/' + cast(datepart(day,@today) as varchar(20)) + '/' + cast(datepart(yyyy,@today) as varchar(20)) select @chartomorrow = cast(datepart(month,@tomorrow) as varchar(20)) + '/' + cast(datepart(day,@tomorrow) as varchar(20)) + '/' + cast(datepart(yyyy,@tomorrow) as varchar(20)) select distinct d.[ticker symbol], d.[date], s.fullname, s.type from daily d, staticdata s where s.epic like @searchdata and d.[ticker symbol] = s.epic and d.[date] between @chartoday and @chartomorrow end end GO New Code: CREATE PROCEDURE PUBLICdailyInstrumentSearch @SearchData varchar(1000), @searchtype char(4) AS begin --wildcard replacement --select @searchData = replace(@searchdata, '*', '%') declare @sqlstring NVARCHAR(1000) declare @today datetime, @tomorrow datetime, @chartoday varchar(100), @chartomorrow varchar(100), @runString nvarchar(1000) set @searchData = replace(@searchdata, ' ', '%'' or s.fullname like ''%') if @searchtype = 'name' begin --we are always going to need to return the most current date --get the first most recent date that matches the search select @sqlstring = N'select top 1 ' + cast(@today as varchar(20)) + ' = d.[date] from daily d, staticdata s where s.fullname like ''%' + @searchData + '%''' select @sqlstring = @sqlstring + ' and d.[ticker symbol] = s.epic order by [date] desc' EXEC sp_executesql @sqlstring --what is tommorrows date select @tomorrow = dateadd(day, 1, @today) select @chartoday = cast(datepart(month,@today) as varchar(20)) + '/' + cast(datepart(day,@today) as varchar(20)) + '/' + cast(datepart(yyyy,@today) as varchar(20)) select @chartomorrow = cast(datepart(month,@tomorrow) as varchar(20)) + '/' + cast(datepart(day,@tomorrow) as varchar(20)) + '/' + cast(datepart(yyyy,@tomorrow) as varchar(20)) select @sqlstring=N'select distinct d.[ticker symbol], d.[date], s.fullname, s.type from daily d, staticdata s where s.fullname like ''%' + @searchData + '%''' select @sqlstring = @sqlstring + ' and d.[ticker symbol] = s.epic and d.[date] between ' + @chartoday + ' and ' + @chartomorrow EXEC sp_executesql @sqlstring end else begin --we are always going to need to return the most current date --get the first most recent date that matches the search select top 1 @today = d.[date] from daily d, staticdata s where s.epic like @searchdata and d.[ticker symbol] = s.epic order by [date] desc --what is tommorrows date select @tomorrow = dateadd(day, 1, @today) select @chartoday = cast(datepart(month,@today) as varchar(20)) + '/' + cast(datepart(day,@today) as varchar(20)) + '/' + cast(datepart(yyyy,@today) as varchar(20)) select @chartomorrow = cast(datepart(month,@tomorrow) as varchar(20)) + '/' + cast(datepart(day,@tomorrow) as varchar(20)) + '/' + cast(datepart(yyyy,@tomorrow) as varchar(20)) select distinct d.[ticker symbol], d.[date], s.fullname, s.type from daily d, staticdata s where s.epic like @searchdata and d.[ticker symbol] = s.epic and d.[date] between @chartoday and @chartomorrow end end GO In case you can't see what i'm trying to achieve, the user will enter a list of words and i want all of those words searched for without the user having to use wildcards, also the first method does not have the desired effect as if i enter british systems i want all the records containing british or/and systems. Many thanks