locked
Stored Procedure in asp.net RRS feed

  • Question

  • User-1497429768 posted

    Hi,

    I hope to search data by Stored Procedure in asp.net,below is the the search parameter:

    @vs_walla varchar(20),
    @vs_idno varchar(10),
    @vs_dTYPE varchar(20),
    @vs_TYPE varchar(1),
    @vs_order

    select * from Table-A
    where idno=iif(@vs_walla='ALL','',@vs_idno) and
    TYPE=iif(@vs_dTYPE='ALL','',@vs_TYPE)
    order by
    case when @vs_order='1' then idx end desc,
    case when @vs_order='2' then TTYPE,A,B end

    The question is:

    • if vs_walla ='ALL' , how to abort the idno condition(search all of idno)
    • if vs_dTYPE ='ALL',how to abort the TYPE condition
    • case when @vs_order='2' then TTYPE,A,B end  -- it can't work. I really don't know why.

    Thanks for any idea.

    Wednesday, May 4, 2016 8:09 AM

Answers

  • User364663285 posted

    Try

    ...
    @vs_walla varchar(20),
    @vs_idno varchar(10),
    @vs_dTYPE varchar(20),
    @vs_TYPE varchar(1),
    @vs_order
    
    select * from Table-A
    where idno=case when @vs_walla='ALL' then '' else @vs_idno end and 
    TYPE=case when @vs_dTYPE='ALL' then '' else @vs_TYPE end
    order by 
    case when @vs_order='1' then idx end desc,
    case when @vs_order='2' then TTYPE end,A,B

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 5, 2016 6:07 AM

All replies

  • User-1497429768 posted

    Hi,

     Finally,I find the solution-modify the code

       where idno=iif(@vs_walla='ALL','',@vs_idno) and 
    TYPE=iif(@vs_dTYPE='ALL','',@vs_TYPE)

    to-where idno like '%'+iif(@vs_walla='ALL','',@vs_idno)+'%' and 
    TYPE like '%'+iif(@vs_dTYPE='ALL','',@vs_TYPE)+'%'

    But the last question-case when @vs_order='2' then TTYPE,A,B end. I still don't know how to resolve it.So please help me.

    Wednesday, May 4, 2016 8:37 AM
  • User77042963 posted

    If you provide your table DDL, sample data and your expected result, you may have better chance to have solutions for your question.    Thanks.

    Wednesday, May 4, 2016 2:33 PM
  • User-1497429768 posted

    Hi limno,

    Thanks for your reply.I want to display and sort the result-set by one or more columns.It's up to the users.

    1.order by idx(field name)
    2.order by TTYPE,A,B(field name)

    Now the syntext is wrong-

    select * from Table-A
    order by
    case when @vs_order='1' then idx end desc,
    case when @vs_order='2' then TTYPE,A,B end

    If I change the Stored Procedure into
    select * from Table-A
    order by
    case when @vs_order='1' then idx end desc,
    case when @vs_order='2' then TTYPE end

    And the syntext is correct.How do I resolve the syntext error?

    Thursday, May 5, 2016 3:41 AM
  • User-219423983 posted

    Hi jeff.wenchai,

    I think you’d better modify your SQL query as below to build the order by clause.

    case when @vs_order='1' then idx end desc,
    case when @vs_order='2' then TTYPE end,
    case when @vs_order='2' then A end,
    case when @vs_order='2' then B end
    

    For more things, you could have a look at below link. As it says, you could also try dynamically build a command string and then use “sp_executesql” to get the result.<!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /--><o:p></o:p>

    http://stackoverflow.com/a/15621718<o:p></o:p>

    Best Regards, <o:p></o:p>

    Weibo Zhang<o:p></o:p>

    Thursday, May 5, 2016 6:06 AM
  • User364663285 posted

    Try

    ...
    @vs_walla varchar(20),
    @vs_idno varchar(10),
    @vs_dTYPE varchar(20),
    @vs_TYPE varchar(1),
    @vs_order
    
    select * from Table-A
    where idno=case when @vs_walla='ALL' then '' else @vs_idno end and 
    TYPE=case when @vs_dTYPE='ALL' then '' else @vs_TYPE end
    order by 
    case when @vs_order='1' then idx end desc,
    case when @vs_order='2' then TTYPE end,A,B

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, May 5, 2016 6:07 AM
  • User-1497429768 posted

    Hi wmec,

    It's great.Thanks for your help.But below the code- It's the same problem.
    idno=case when @vs_walla='ALL' then '' else @vs_idno end

    If I pass the parameter @vs_walla='ALL' and the result-set is empty,so I still use the code-idno like '%'+iif(@vs_walla='ALL','',@vs_idno)+'%' .
    Thanks for everyone's help.

    Thursday, May 5, 2016 11:50 PM