sp_executesql and dynamic sql - SQL Server Forums

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

Transact-SQL (2000)

sp_executesql and dynamic sql

Author

Topic

Forser
Starting Member

4 Posts

Posted - 2007-03-28 : 03:35:23

Hi

I have a SP where i search for values in a column and returns the results.

Problem is, if i send in %word% OR body LIKE %word2% i get no results but if i just send in %word% then i get results (i know there are results since i ran a regular query (not SP one) and got the results.

The SP looks like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[doSearch]
-- Add the parameters for the stored procedure here
@searchWord nvarchar(4000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @sql nvarchar(4000);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @searchTitle nvarchar(500);
DECLARE @searchName nvarchar(500);
DECLARE @searchPath nvarchar(500);
DECLARE @searchBody nvarchar(4000);
DECLARE @searchDate DateTime;

-- Insert statements for procedure here
SELECT @sql = 'SELECT @sTitle = f.title, @sName = f.name, @sPath = f.path, @sBody = c.designBody, @sDate = c.lastModify
FROM files AS f INNER JOIN contents AS c ON c.fileId = f.id WHERE (f.status = 2) AND (c.designBody LIKE @sWord)
ORDER BY c.lastmodify DESC';
SET @ParmDefinition = N'@sWord nvarchar(4000), @sTitle nvarchar(500) OUTPUT,
@sName nvarchar(500) OUTPUT, @sPath nvarchar(500) OUTPUT,
@sBody nvarchar(4000) OUTPUT, @sDate DateTime OUTPUT';

EXEC sp_executesql @sql, @ParmDefinition, @sWord = @searchWord, @sTitle = @searchTitle OUTPUT, @sName = @searchName OUTPUT, @sPath = @searchPath OUTPUT, @sBody = @searchBody OUTPUT, @sDate = @searchDate OUTPUT;
SELECT @searchTitle AS title, @searchName AS name, @searchPath AS path, @searchBody AS body, @searchDate AS lastmodify;
END

I am pretty sure that i have messed up somewhere.
Thanks for the help in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 03:40:15

Is there a need for D-SQL here? I don't see a reason to use it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Forser
Starting Member

4 Posts

Posted - 2007-03-28 : 03:41:37

the searchword is dynamic.. the user can enter word OR word2 OR word3 AND WORD4

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 03:52:51

That can be done with static sql as well:

declare @t table
(
	w varchar(50)
)

insert @t
select 'word' union all
select 'word2' union all
select 'word3' union all
select 'word4'

declare @s varchar(50)

set @s = 'word'

select * from @t where w like '%' + @s + '%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Forser
Starting Member

4 Posts

Posted - 2007-03-28 : 03:54:14

Problem is, i don't know the searchword ahead of time, it is a localsearch in application so i have no clue if the user writes word3 OR word5 and similar

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 03:56:28

The solution I gave doesn't require you to know what user enters in advance. You can pass word he entered as a parameter to the query and get the results.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Forser
Starting Member

4 Posts

Posted - 2007-03-28 : 03:57:49

Okey..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources