SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with using SP_EXECUTESQL


Problem with using SP_EXECUTESQL

Author
Message
soni.praveen12
soni.praveen12
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19324 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5650 Visits: 6900
... 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
soni.praveen12
soni.praveen12
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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
winash
winash
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1796 Visits: 1883
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 '



Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19324 Visits: 14900
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
soni.praveen12
soni.praveen12
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 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).....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search