February 23, 2009 at 6:47 am
Hi All,
I have a problem when i using SP_EXECUTESQL.
I am using one Procedure called "ABCInfo" inside this procedure i call the SP_EXECUTESQL procedure.
In "ABCInfo" i paas four paramenter like
'P soni',
'P soni,
1,
20
when i pass the parameter with one alphabet then space it not working. it give no result. and if i use parameters 'Ra tata','Ra tata',1,20 then it is working fine.
here problem with only space after 1 alphabet.
Anybody know about this, please help me.
Thanks in advance.
February 23, 2009 at 7:16 am
Can you post the exact SQL you are executing? And the definition of the stored procedure being called?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 23, 2009 at 5:34 pm
... and maybe the exact text of the error message you are getting.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 23, 2009 at 10:45 pm
hi jack,
this is my exact procedure.
CREATE procedure [dbo].[Search_and_paging]
@art_Filter varchar(8000),
@sect_Filter varchar(8000),
@PageNumber int=1,
@pageSize int=20
as
begin
declare @strSQL nvarchar(4000), @art varchar(8000), @sect varchar(8000), @orderBy varchar(30),
@FIELDS varchar(100), @startRowNo int , @endRowNo int
--field to be selected
set @FIELDS=' sid , title , url, pdt, intro, intro2 '
-- first table as article selection
set @art=' from ( select art_id as sid , art_title as title, ''/article.aspx?artid=''+art_encryptid as url ,
art_displaydt as pdt,
case art_index_intro when '''' then art_intro else isnull(art_index_intro, art_intro) end as intro,
substring(art_article,1,300) as intro2
from us_article
where art_status = 3 and art_article_type!=5 ' + isnull(@art_Filter,'')
-- second table as section selection
set @sect=' union
select sect_id as sid , sect_title as title, sect_page+sect_encryptid as url,
sect_publishdt as pdt,
case sect_index_intro when '''' then sect_intro else isnull(sect_index_intro, sect_intro) end as intro,
substring(sect_dtl,1,300) as intro2
from us_section
where sect_status = 3 and sect_page is not null and sect_page <> '''' ' + isnull(@sect_Filter,'') + ' )a '
-- for order by
set @orderBy= ' order by pdt desc '
-- forming query
set @startRowNo =((@PageNumber * @pageSize) - (@pageSize -1) )
set @endRowNo=(@PageNumber * @pageSize)
set @strSQL= N' WITH Search AS (select ROW_NUMBER() OVER ('+ @orderBy+') AS ROWID, '+ @FIELDS + @art + @sect +' ) select * FROM Search where ROWID between '+ convert(varchar(50),@startRowNo) + ' and ' +convert(varchar(50),@endRowNo)
EXECUTE SP_EXECUTESQL @strSQL
-- for total selected records
-- forming query
set @strSQL= N' select Count(sid) ' + @art + @sect
EXECUTE SP_EXECUTESQL @strSQL
end
February 23, 2009 at 11:27 pm
Is this the same procedure to which you're passing parameters @art_Filter and @sect_Filter as 'P Soni' and 'Ra tata'?
From the code it looks like @art_Filter and @sect_Filter should be SQL statements...
For e.g: the SP code has:
and art_article_type!=5 ' + isnull(@art_Filter,'')
at this point if you pass in 'Ra tata' this part of the sql string will become:
and art_article_type!=5 Ra tata
which doesn't really make too much sense...
I'd assume that @art_Filter would be some SQL construct like ' AND art_id=100 '
February 24, 2009 at 6:08 am
When I copied your code and passed in parameters as you mention in your first post and did a Select @strSQL this is what I got:
WITH Search AS
(
select
ROW_NUMBER() OVER ( order by pdt desc ) AS ROWID,
sid ,
title ,
url,
pdt,
intro,
intro2
from
(
select
art_id as sid ,
art_title as title,
'/article.aspx?artid='+art_encryptid as url ,
art_displaydt as pdt,
case art_index_intro
when '' then art_intro
else isnull(art_index_intro, art_intro)
end as intro,
substring(art_article,1,300) as intro2
from
us_article
where
art_status = 3 and
art_article_type!=5
P Soni
union
select
sect_id as sid ,
sect_title as title,
sect_page+sect_encryptid as url,
sect_publishdt as pdt,
case sect_index_intro
when '' then sect_intro
else isnull(sect_index_intro, sect_intro)
end as intro,
substring(sect_dtl,1,300) as intro2
from
us_section
where
sect_status = 3 and
sect_page is not null and
sect_page <> ''
P Soni
)a
)
select
*
FROM
Search
where
ROWID between 1 and 20
Note the bold parts. @art_Filter and @sect_Filter are not being compared to anything. At least for @sectFilter it looks like you do not have the parameter in the correct place. It looks like the parameter should be here:
sect_page <> ''' + isnull(@sect_Filter, '') + ''' ) )a '
I have no clue what to do with @art_Filter.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 2:34 am
Hi Jake,
thanks for ur reply.
i found the solution.
in my query @art_Filter use indexed search keywords so that here is the problem i found.
ex. i use contains(columnname,condition).....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply