[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Dynamic SQL and sp_executesql

marckus

3/28/2007 7:29:00 AM

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!
16 Answers

Uri Dimant

3/28/2007 7:39:00 AM

0

create table #test (c varchar(20))
insert into #test values ('it is my first word')
insert into #test values ('word')
insert into #test values ('hello word2')
insert into #test values ('greate word')
insert into #test values ('heloo')
insert into #test values ('john smith')


select * from #test where c ='%word%' or c like '%word2%'
--I got 1 row


If it does not help, please post ddl+ sample data +an expected result to be
tested


<marckus@gmail.com> wrote in message
news:1175066942.231256.236790@r56g2000hsd.googlegroups.com...
> 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!
>


marckus

3/28/2007 7:47:00 AM

0

On Mar 28, 9:39 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> create table #test (c varchar(20))
> insert into #test values ('it is my first word')
> insert into #test values ('word')
> insert into #test values ('hello word2')
> insert into #test values ('greate word')
> insert into #test values ('heloo')
> insert into #test values ('john smith')
>
> select * from #test where c ='%word%' or c like '%word2%'
> --I got 1 row
>
> If it does not help, please post ddl+ sample data +an expected result to be
> tested
>
> <marc...@gmail.com> wrote in message

Hi, Thanks for the quick reply, what i want is all results that
contains either word or word2 .. your select is wrong,

the select i am after is like this
SELECT f.name, f.path, f.title, c.designBody, c.lastModify
FROM files AS f INNER JOIN
contents AS c ON c.fileId = f.id
WHERE (f.status = 2) AND (c.designBody LIKE '%word%' OR
c.designBody LIKE '%word2%')

That returns all the results correctly, but when i am making it into a
SP with dynamic SQL build (the LIKE part with OR is dynamic)
then i am only getting one result

Uri Dimant

3/28/2007 8:36:00 AM

0

Hi
Use PRINT command to get script that run dynamicaly. I'm certain there are
two different SELECTs







<marckus@gmail.com> wrote in message
news:1175068027.436704.178590@n76g2000hsh.googlegroups.com...
> On Mar 28, 9:39 am, "Uri Dimant" <u...@iscar.co.il> wrote:
>> create table #test (c varchar(20))
>> insert into #test values ('it is my first word')
>> insert into #test values ('word')
>> insert into #test values ('hello word2')
>> insert into #test values ('greate word')
>> insert into #test values ('heloo')
>> insert into #test values ('john smith')
>>
>> select * from #test where c ='%word%' or c like '%word2%'
>> --I got 1 row
>>
>> If it does not help, please post ddl+ sample data +an expected result to
>> be
>> tested
>>
>> <marc...@gmail.com> wrote in message
>
> Hi, Thanks for the quick reply, what i want is all results that
> contains either word or word2 .. your select is wrong,
>
> the select i am after is like this
> SELECT f.name, f.path, f.title, c.designBody, c.lastModify
> FROM files AS f INNER JOIN
> contents AS c ON c.fileId = f.id
> WHERE (f.status = 2) AND (c.designBody LIKE '%word%' OR
> c.designBody LIKE '%word2%')
>
> That returns all the results correctly, but when i am making it into a
> SP with dynamic SQL build (the LIKE part with OR is dynamic)
> then i am only getting one result
>


marckus

3/28/2007 11:48:00 AM

0

On Mar 28, 10:35 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> Hi
> Use PRINT command to get script that run dynamicaly. I'm certain there are
> two different SELECTs

PRINT command on what part?

Uri Dimant

3/28/2007 11:59:00 AM

0

PRINT @sql




<marckus@gmail.com> wrote in message
news:1175082506.950454.18150@b75g2000hsg.googlegroups.com...
> On Mar 28, 10:35 am, "Uri Dimant" <u...@iscar.co.il> wrote:
>> Hi
>> Use PRINT command to get script that run dynamicaly. I'm certain there
>> are
>> two different SELECTs
>
> PRINT command on what part?
>
>


marckus

3/28/2007 12:31:00 PM

0

On Mar 28, 1:58 pm, "Uri Dimant" <u...@iscar.co.il> wrote:
> PRINT @sql
>
> <marc...@gmail.com> wrote in message
>
> news:1175082506.950454.18150@b75g2000hsg.googlegroups.com...
>
> > On Mar 28, 10:35 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> >> Hi
> >> Use PRINT command to get script that run dynamicaly. I'm certain there
> >> are
> >> two different SELECTs
>
> > PRINT command on what part?

Doesn't get anything at all when i do PRINT @sql

Uri Dimant

3/28/2007 12:33:00 PM

0

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';

PRINT @sql

Do you have nothing?




<marckus@gmail.com> wrote in message
news:1175085057.921073.243120@r56g2000hsd.googlegroups.com...
> On Mar 28, 1:58 pm, "Uri Dimant" <u...@iscar.co.il> wrote:
>> PRINT @sql
>>
>> <marc...@gmail.com> wrote in message
>>
>> news:1175082506.950454.18150@b75g2000hsg.googlegroups.com...
>>
>> > On Mar 28, 10:35 am, "Uri Dimant" <u...@iscar.co.il> wrote:
>> >> Hi
>> >> Use PRINT command to get script that run dynamicaly. I'm certain there
>> >> are
>> >> two different SELECTs
>>
>> > PRINT command on what part?
>
> Doesn't get anything at all when i do PRINT @sql
>


marckus

3/28/2007 12:40:00 PM

0

On Mar 28, 2:33 pm, "Uri Dimant" <u...@iscar.co.il> 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';
>
> PRINT @sql
>
> Do you have nothing?

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;

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;
PRINT @sql
END

That is the Stored Procedure and the content in the searchword is
(that i am using to test with)

USE [test]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[doSearch]
@searchWord = N'%word% OR c.designBody LIKE %word1%'

SELECT 'Return Value' = @return_value

GO

marckus

3/28/2007 12:43:00 PM

0

On Mar 28, 2:40 pm, marc...@gmail.com wrote:
> On Mar 28, 2:33 pm, "Uri Dimant" <u...@iscar.co.il> 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';
>
> > PRINT @sql
>
> > Do you have nothing?
>
> 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;
>
> 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;
> PRINT @sql
> END
>
> That is the Stored Procedure and the content in the searchword is
> (that i am using to test with)
>
> USE [test]
> GO
>
> DECLARE @return_value int
>
> EXEC @return_value = [dbo].[doSearch]
> @searchWord = N'%word% OR c.designBody LIKE %word1%'
>
> SELECT 'Return Value' = @return_value
>
> GO

If i skip the SP and build the SQL query directly in the code, then it
can execute the query fine and give me the right results.

Uri Dimant

3/28/2007 12:53:00 PM

0

Again
As you said if you build SQL 'directly' it works fine, OK now just PRINT
@sql (part of building) and compare with 'directly' build SQL, that's all.





<marckus@gmail.com> wrote in message
news:1175085785.370251.233540@o5g2000hsb.googlegroups.com...
> On Mar 28, 2:40 pm, marc...@gmail.com wrote:
>> On Mar 28, 2:33 pm, "Uri Dimant" <u...@iscar.co.il> 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';
>>
>> > PRINT @sql
>>
>> > Do you have nothing?
>>
>> 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;
>>
>> 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;
>> PRINT @sql
>> END
>>
>> That is the Stored Procedure and the content in the searchword is
>> (that i am using to test with)
>>
>> USE [test]
>> GO
>>
>> DECLARE @return_value int
>>
>> EXEC @return_value = [dbo].[doSearch]
>> @searchWord = N'%word% OR c.designBody LIKE %word1%'
>>
>> SELECT 'Return Value' = @return_value
>>
>> GO
>
> If i skip the SP and build the SQL query directly in the code, then it
> can execute the query fine and give me the right results.
>