Making a Specific Stored Procedure For Search Keyword

[ivadak]

Hi ! I want to create a stored procedure for Search keyword with UNION, Sub query. My DataBase includes same types of Board Tables. (Such as board_a , board_b ...) I tried to make as below.

CREATE PROCEDURE sp_Search
@page int = 1,
@pageSize int = 10,
@keyword varchar(50)

AS

Declare @Sql varchar(3000)
Set @Sql = "Select Top " + Str(@pageSize) + " no, subject, content from  "
Set @Sql = @Sql + " ( "
Set @Sql = @Sql + "			Select tb, no, subject, content from ( "
Set @Sql = @Sql + "				Select 'board\_a' as tb, no,subject,content from board\_a "
Set @Sql = @Sql + "				UNION "
Set @Sql = @Sql + "				select 'board\_b' as tb, no,subject,content from board\_b "
Set @Sql = @Sql + "			) As A "
Set @Sql = @Sql + " )   where no NOT IN "
Set @Sql = @Sql + " ( "
Set @Sql = @Sql + "		Select Top " + Str(@pageSize \* (@page-1)) + " no from ( "
Set @Sql = @Sql + "			Select tb, no, subject, content from ( "
Set @Sql = @Sql + "				Select 'board\_a' as tb, no,subject,content from board\_a "
Set @Sql = @Sql + "				UNION "
Set @Sql = @Sql + "				select 'board\_b' as tb, no,subject,content from board\_b "
Set @Sql = @Sql + "			) As A "
Set @Sql = @Sql + "     )  "
Set @Sql = @Sql + " ) where subject Like '%"+ @keyword +"%' or content Like '%"+ @keyword +"%' order by no ASC " 
Execute(@Sql)

But I got a Error Message. I am having extreme difficulty in working time. Please can someone help me? ;)

[Henry Minute]

I do not have the skills to help you, but it might help others to do so if you edit your post to include the full error message, rather than just say that you get one. Cut and paste if you can, to avoid typos. :)

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[Abhishek Sur]

what is the error message ?? :confused:

Abhishek Sur

---

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.

[Ashfield]

Try printing your sql (replace Execute(@Sql) with print @SQL), then paste it into query analyser and try running it from there to get more information on the error.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP