Problem with using SP_EXECUTESQL

  • 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.

  • Can you post the exact SQL you are executing? And the definition of the stored procedure being called?

  • ... 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

  • 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

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

  • 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.

  • 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