Databases
StackOverflow2013
Postsdbo
POSet Where Parameter Dynamically on stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSet Where Parameter Dynamically on stored procedure
    primarykey
    data
    text
    <p>I have a stored procedure to search the data from database with parameter like this :</p> <pre><code>CREATE PROCEDURE [dbo].[Rintis_SearchPayment] -- Add the parameters for the stored procedure here @payIDin as int, @PayAccountin as varchar(10), @PayCustNamein as varchar(30), @PayAmountin as int, @PayAmountPaidin as int, @PayResponsein as char (2), @PayRefNoin as varchar (120), @PayScreenTextin as varchar (100), @PayReceiptTextin as varchar (350), @PayDatetimein as datetime, @PayBankCodein as varchar (6), @payIDou as int, @PayAccountou as varchar(10), @PayCustNameou as varchar(30), @PayAmountou as int, @PayAmountPaidou as int, @PayResponseou as char (2), @PayRefNoou as varchar (120), @PayScreenTextou as varchar (100), @PayReceiptTextou as varchar (350), @PayDatetimeou as datetime, @PayBankCodeouin as varchar (6) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. select @payIDou = PayId , @PayAccountou = PayAccount, @PayCustNameou = PayCustName, @PayAmountou = PayAmount, @PayAmountPaidou = PayAmountPaid, @PayResponseou= PayResponse, @PayRefNoou= PayRefNo, @PayScreenTextou= PayScreenText, @PayReceiptTextou= PayReceiptText, @PayDatetimeou= PayDatetime, @PayBankCodeouin= PayBankCode from Payment where PayId is not null AND PayAccount like '%'+ISNULL((@PayAccountin),'')+'%' AND PayCustName LIKE '%'+ISNULL((@PayAccountin),'')+'%' AND PayAmount LIKE '%'+ISNULL((@PayAccountin),'')+'%' AND PayAmountPaid LIKE '%'+ISNULL((@PayAccountin),'')+'%' AND PayResponse LIKE '%'+ISNULL((@PayAccountin),'')+'%' AND PayRefNo LIKE '%'+ISNULL((@PayAccountin),'')+'%' AND cast(PayDatetime as date) = ( select convert(date , ''+ISNULL((@PayAccountin),'')+'', 103)) order by PayDatetime DESC select @payIDou -- Insert statements for procedure here END </code></pre> <p>but when I execute it I didn't give value, cause I know some like statement is incorrect, so it'll never give the return value.</p> <p>At first I do the query like this on my vb .net :</p> <pre><code> Public Function SearchPayment(ByVal PayAccount As String, ByVal PayCustName As String, ByVal PayAmount As String, ByVal PayAmountPaid As String, ByVal PayResponse As String, ByVal PayRefNo As String, ByVal PayDatetime As String) As Boolean Dim strsql As String = " select top 100 * from Payment where PayId is not null " If PayAccount &lt;&gt; "" Then strsql &amp;= "and PayAccount like '%" &amp; PayAccount &amp; "%'" End If If PayCustName &lt;&gt; "" Then strsql &amp;= "and PayCustName like '%" &amp; PayCustName &amp; "%'" End If If PayAmount &lt;&gt; "" Then strsql &amp;= "and PayAmount like '%" &amp; PayAmount &amp; "%'" End If '' add PayAmountPaid If PayAmountPaid &lt;&gt; "" Then strsql &amp;= "and PayAmountPaid like '%" &amp; PayAmountPaid &amp; "%'" End If If PayResponse &lt;&gt; "" Then strsql &amp;= "and PayResponse like '%" &amp; PayResponse &amp; "%'" End If '' add PayRefNo If PayRefNo &lt;&gt; "" Then strsql &amp;= "and PayRefNo like '%" &amp; PayRefNo &amp; "%'" End If If PayDatetime &lt;&gt; "" Then ''select * from Inquiry where InquiryId is not null and cast(InquiryDate as date) = ( select convert(date ,'7/05/2013' , 103) ) strsql &amp;= "and cast(PayDatetime as date) = ( select convert(date , '" &amp; PayDatetime &amp; "', 103)) " End If strsql &amp;= "order by PayDatetime DESC" Return runQuery(strsql) End Function </code></pre> <p>Is it possible to add <code>Where</code> parameter by checking the input value first like I did on my vb.net ?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn Woo
    1. USSabilv
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat are you trying to do when you set the value of your parameters with `SELECT @payIDou = PayId ....` they are not declared as output parameters so you cannot read them back in your vb code
      singulars
      1. POSet Where Parameter Dynamically on stored procedure
      1. USSteve
    2. CO@Steve ah pardon me, i made mistake when copy the code, i put the output code.
      singulars
      1. POSet Where Parameter Dynamically on stored procedure
      1. USSabilv
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload