Problem with dynamic stored procedure

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

Old Forums

CLOSED - General SQL Server

Problem with dynamic stored procedure

Author

Topic

maxim
Yak Posting Veteran

51 Posts

Posted - 2006-05-09 : 13:52:37

I made a class in asp.net with a search engine.
That class returns me one string consonant the words that I want to search and if it is for method “OR” or “AND”.

If i wanted to search de word "blabla" i will send the string:

*** WHERE TopiRep.Message LIKE '%blabla%' OR Topi.Subject LIKE '%blabla%' ***

If i wanted to search de TWO words ("blabla" & "bleble") i will send the string:

*** WHERE TopiRep.Message LIKE '%blabla%' OR Topi.Subject LIKE '%blabla%' OR TopiRep.Message LIKE '%bleble%' OR
Topi.Subject LIKE '%bleble%' ***

so... the procedure should be prepared for 2... 4... 6... words

so, there is my procedure:


CREATE PROCEDURE sp_Forums_SearchWord
@myStr        varchar(500),
@PageNumber    int,
@PageSize    int

AS

CREATE TABLE #TempSearchWord
(
    ID             int IDENTITY PRIMARY KEY,
    TopicID            int,
    Subject            varchar(100),
    AddedDate        datetime,
    TopicReplies        int
)

INSERT INTO #TempSearchWord
(
    TopicID,
    Subject,
    AddedDate,
    TopicReplies
)
SELECT 
    DISTINCT(a.TopicID),
    a.Subject,
    a.AddedDate,
    a.TopicReplies
FROM
v_Forums_Topics a INNER JOIN 
(SELECT DISTINCT(TopiRep.TopicID) 
	FROM 
		(SELECT Forums_Topics.TopicID, Forums_Topics.Message FROM Forums_Topics UNION ALL SELECT 		
Forums_Replies.TopicID, Forums_Replies.Message FROM Forums_Replies) TopiRep INNER JOIN 		Forums_Topics 
Topi ON Topi.TopicID = TopiRep.TopicID
@myStr   --WHERE TopiRep.Message LIKE '%blabla%' OR Topi.Subject LIKE '%blabla%' 
		) as Results on Results.TopicID = a.TOPICID

		ORDER BY AddedDate Desc

DECLARE @FromID int
DECLARE @ToID int
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
SELECT     TopicID,
        Subject, 
        AddedDate, 
        TopicReplies        
FROM #TempSearchWord WHERE ID >= @FromID AND ID <= @ToID
GO

Please help me to make that this procedure can receive the string of search for the some words that I to want to

search.
I already tried of everything without success! I get the error because the use of @myStr
I also tried to insert some "exec" but i still get error's !

Please, help me, it is very important to me!

Thanks,
Max, from Portugal

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-09 : 16:28:57

you need to use dynamic sql, but then that takes the temp table out of scope, unless you do the whole thing as dynamic sql

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

maxim
Yak Posting Veteran

51 Posts

Posted - 2006-05-09 : 18:03:51

Thanks for your time. I had put this question in some forums but nobody says anything... so... thanks!!

I've tried to make the whole thing in dynamic sql, like this:

CREATE PROCEDURE sp_Forums_SearchWord
@myStr        varchar(500),
@PageNumber    int,
@PageSize    int

AS

DECLARE @INITSQL varchar(1500)
DECLARE @ENDSQL varchar(800)

SELECT @INITSQL = 'CREATE TABLE #TempSearchWord
(
    ID             int IDENTITY PRIMARY KEY,
    TopicID            int,
    Subject            varchar(100),
    AddedDate        datetime,
    TopicReplies        int
)

INSERT INTO #TempSearchWord
(
    TopicID,
    Subject,
    AddedDate,
    TopicReplies
)
SELECT 
    DISTINCT(a.TopicID),
    a.Subject,
    a.AddedDate,
    a.TopicReplies
FROM
v_Forums_Topics a INNER JOIN 
(SELECT DISTINCT(TopiRep.TopicID) 
	FROM 
		(SELECT Forums_Topics.TopicID, Forums_Topics.Message FROM Forums_Topics UNION ALL SELECT 		
Forums_Replies.TopicID, Forums_Replies.Message FROM Forums_Replies) TopiRep INNER JOIN 		Forums_Topics 
Topi ON Topi.TopicID = TopiRep.TopicID WHERE'

-- TopiRep.Message LIKE '%blabla%' OR Topi.Subject LIKE '%blabla%' ) as Results on Results.TopicID = a.TOPICID 

SELECT @ENDSQL ='ORDER BY AddedDate Desc
DECLARE @FromID int
DECLARE @ToID int
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
SELECT     TopicID,
        Subject, 
        AddedDate, 
        TopicReplies        
FROM #TempSearchWord WHERE ID >= @FromID AND ID <= @ToID '

EXEC ( @INITSQL + @myStr + @ENDSQL )

GO

But i receive this error :

System.Data.SqlClient.SqlException: Must declare the variable '@CREATE'. Must declare the variable '@PageNumber'. Must declare the variable '@PageNumber'.

:( what can i do??

Thanks!
Max

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-10 : 03:15:11

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 06:04:54

Change

EXEC ( @INITSQL + @myStr + @ENDSQL )

to


EXEC sp_executesql @INITSQL + @myStr + @ENDSQL,
     '@PageNumber int, @PageSize int',
     @PageNumber, @PageSize

and then the query will be parameterised, which will be optimal for the Query Optimiser. (You ought to parameterise the @MyStr bit too, really)

It would probably be better to move @FromID and @ToID to be parameters to sp_ExecuteSQL too, and thus move them outside the dynamic SQL.

Kristen

maxim
Yak Posting Veteran

51 Posts

Posted - 2006-05-10 : 07:37:39

thanks

the sql doesn't allow to make
[CODE]
EXEC sp_executesql @INITSQL + ....
[/CODE]

so, i chanched to:


DECLARE @SQL varchar(3000)
...
EXEC sp_executesql @SQL,
     '@PageNumber int, @PageSize int',
     @PageNumber, @PageSize

But i got this error:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 07:49:12

I wouldn't expect it to be fussy on varchar / nvarchar

BoL: "stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext"

can you try:


DECLARE @SQL nvarchar(4000)
...
EXEC sp_executesql @SQL,
     N'@PageNumber int, @PageSize int',
     @PageNumber, @PageSize

Kristen

maxim
Yak Posting Veteran

51 Posts

Posted - 2006-05-10 : 18:46:38

Thank you!!!

It works!!

You all had been precious!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources