Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with using SP_EXECUTESQL Expand / Collapse
Author
Message
Posted Monday, February 23, 2009 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 6:42 AM
Points: 64, Visits: 30
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.


Post #662521
Posted Monday, February 23, 2009 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 11,148, Visits: 12,889
Can you post the exact SQL you are executing? And the definition of the stored procedure being called?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #662553
Posted Monday, February 23, 2009 5:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:24 PM
Points: 3,997, Visits: 6,049
... 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? -- Stephen Stills
Post #663151
Posted Monday, February 23, 2009 10:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 6:42 AM
Points: 64, Visits: 30
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



Post #663219
Posted Monday, February 23, 2009 11:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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 '



Post #663230
Posted Tuesday, February 24, 2009 6:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 11,148, Visits: 12,889
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 &lt;&gt; ''' + isnull(@sect_Filter, '') + ''' ) )a '

I have no clue what to do with @art_Filter.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #663389
Posted Wednesday, February 25, 2009 2:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 6:42 AM
Points: 64, Visits: 30
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).....
Post #664086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse