September 5, 2008 at 7:07 am
HI,
I migrated Sp from 2000 , it was using the Temp table for search option...
In ms sql 2005, the search option was not working properly because of temp table.
can any one guide how to remove the temp table and handle the below sp in sql server 2005?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE dp_GetTest
(
@title varchar(255),
@keyword varchar(255),
@develop int,
@publish_date varchar(10),
@page_index int,
@page_size int,
@rec_count int = 0 OUTPUT,
@status_code int = 0 OUTPUT,
@error_msg varchar(500) = NULL OUTPUT
)
AS
DECLARE
@strQuery nvarchar(2048),
@strCondition nvarchar(2048),
@strCountQuery nvarchar(2000),
@news_mon int,
@news_year int,
@fromDate datetime,
@first_rec int,
@last_rec int,
@andFlag bit
SET @first_rec = (@page_index - 1) * @page_size
SET @last_rec = (@page_index * @page_size + 1)
SET @status_code = @@Error
SET @error_msg = "Success"
SET @andFlag = 0
IF @title <> NULL AND @title <> ''
BEGIN
SET @strCondition = " WHERE [fasttrack].[Headline] like '" + RTRIM(CAST(@title AS VARCHAR))+ "%' "
SET @andFlag = 1
END
IF @keyword <> NULL AND @keyword <> ''
BEGIN
IF @andFlag = 0
BEGIN
SET @strCondition = " WHERE [fasttrack].[Article] like '" + RTRIM(CAST(@keyword AS VARCHAR))+ "%' "
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND [fasttrack].[Article] like '" + RTRIM(CAST(@keyword AS VARCHAR))+ "%' "
SET @andFlag = 1
END
END
IF @develop <> 0
BEGIN
SELECT @fromDate = DATEADD(DAY,-CONVERT(INT,@develop,3),GetDate())
IF @andFlag =0
BEGIN
SET @strCondition = " WHERE PublishDate >= '" + CONVERT(VARCHAR, @fromDate, 120) + "' "
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND PublishDate >= '" + CONVERT(VARCHAR, @fromDate, 120) + "' "
SET @andFlag = 1
END
END
ELSE
BEGIN
IF @publish_date <> NULL AND @publish_date <> ''
BEGIN
SELECT @news_mon = DATEPART(MONTH,CONVERT(DATETIME,@publish_date,112))
SELECT @news_year = DATEPART(YEAR,CONVERT(DATETIME,@publish_date,112))
IF @andFlag = 0
BEGIN
SET @strCondition = " WHERE DATEPART(month, PublishDate) = " + CAST(@news_mon AS VARCHAR)
SET @strCondition = @strCondition + " AND DATEPART(year, PublishDate) = " + CAST(@news_year AS VARCHAR)
SET @andFlag = 1
END
ELSE
BEGIN
SET @strCondition = @strCondition + " AND DATEPART(month, PublishDate) = " + CAST(@news_mon AS VARCHAR)
SET @strCondition = @strCondition + " AND DATEPART(year, PublishDate) = " + CAST(@news_year AS VARCHAR)
SET @andFlag = 1
END
END
END
PRINT @strCondition
IF @status_code = 0
BEGIN
SET ROWCOUNT 0
BEGIN TRANSACTION GetfasttrackTran
IF exists (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[#Temp_fasttrack]')
DROP TABLE [dbo].[#Temp_fasttrack]
CREATE TABLE #Temp_fasttrack
(
Temp_ID int IDENTITY,
Counter float,
PublishDate smalldatetime,
Headline nvarchar(255),
SET @status_code = @@Error
IF @status_code = 0
BEGIN
SET @strQuery = "INSERT INTO #Temp_fasttrack (Counter, PublishDate, Headline) " +
"SELECT Counter, PublishDate, Headline " +
"FROM fasttrack " + @strCondition +
" ORDER BY fasttrack.PublishDate DESC"
EXECUTE sp_executesql @strQuery
SET @rec_count = @@ROWCOUNT
SET @status_code = @@Error
END
IF @status_code = 0
BEGIN
SELECT Counter,convert(char(12), PublishDate, 3),Headline
FROM #Temp_fasttrack
WHERE Temp_ID > @first_rec
AND Temp_ID < @last_rec
SET @status_code = @@Error
END
IF exists (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[#Temp_fasttrack]')
DROP TABLE [dbo].[#Temp_fasttrack]
IF @status_code <> 0
ROLLBACK TRANSACTION GetfasttrackTran
ELSE
COMMIT TRANSACTION GetfasttrackTran
END
IF @status_code <> 0
SELECT @error_msg = description from master..sysmessages WHERE error = @status_code
RETURN @@Error
September 5, 2008 at 9:16 am
Your drop command looks incorrect to me, but otherwise it looks like the temp table should work.
What about it isn't working? Are you getting an error message, or just not getting the results you expect, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2008 at 11:41 am
hi,
i am not getting any error message.
But the result was not proper.
September 5, 2008 at 11:58 am
Please see this link for instructions on how to get better answers from this forum: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 12:15 pm
The above sp is working fine in ms sql 2000 but behaves different in ms sql 2005.
i am getting the unexpected result set in case of ms sql 2005.
But the result set is proper in ms sql 2000.
Can any one direct this problem..
September 5, 2008 at 12:29 pm
We probably can, if you do as I asked and follow the instructions in the linked article.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 1:54 pm
At the very least, post the results from each (2000 and 2005) and let us know what's wrong with the ones from 2005.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply