Dynamic SQL and sp_executesql

Forser


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!

Arnie Rowland

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.

You cannot pass in (as a parameter) programming syntax language. [ OR body LIKE ]. You need to pass in two separate parameters and have the [OR body LIKE ] previously established as part of the @sql.

Forser

So i can't pass OR LIKE AND in the @searchWord parameter

Arnie Rowland

That is correct.

Have the [ OR LIKE ] in your code, and pass in both search strings as parameters. You could optionally build the @sql string to include [ OR LIKE ] IF the second parameter is not null.

Forser

How do you mean pass both
The searchword can change for each search since it is an search in a application, so i have no clue if the user writes word1 OR word2 .. or maybe word2 AND word5 OR word6

ManiD

You have to chnage the following statements..

Forser wrote:

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 @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 ' + Isnull(@sWord,' 1=1') + '
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, @sTitle = @searchTitle OUTPUT, @sName = @searchName OUTPUT, @sPath = @searchPath OUTPUT, @sBody = @searchBody OUTPUT, @sDate = @searchDate OUTPUT;

Forser

Didn't work ManiD, getting an error on that.
complains on the %word% OR c.designBody LIKE %word2% part

Says Incorrect syntax near 'word'

ManiD

Yes.

You have to pass your argument something like this..

'c.designBody LIKE ''%word2%'' part'