Need help with stored procedure.....

Results 1 to 2 of 2

Thread: Need help with stored procedure.....

  1. #1
    Charlie Pyne Guest

    Default Need help with stored procedure.....

    Can someone give me a general idea how to create a dynamic stored procedure that that returns data, but is not always sent the same criteria, or number of criteria. I have read the articles on dynamic SPs here, but I&#039m still a little lost on how exactly to handle the parameters... Somtimes I&#039ll have 1 criteria and sometimes I&#039ll have 10.. ????<BR><BR>I am selecting work orders from a screen where the user can enter data into any number of text boxs..<BR><BR>Example: I need it to be able to generate each of these WHERE clauses...<BR><BR>WHERE order_id = 23423<BR><BR>WHERE order_id = 23423 and building = &#039MAIN&#039<BR><BR>WHERE building = &#039MAIN&#039 and last_name = &#039SMITH&#039<BR><BR><BR>I&#039m imaginging a procedure with 20 parameters... 10 data 10 column names and then somehow assemble a WHERE with the not null ones... Am I on the right track????

  2. #2
    Join Date
    Dec 1969

    Default RE: Need help with stored procedure.....

    you can specify a default value for params on an SP like so ...<BR><BR>CREATE PROCEDURE Test <BR>@pParam1 VarChar(10) =NULL<BR>@pParam2 VarChar(10) =NULL<BR>@pParam3 VarChar(10) =NULL<BR>AS<BR>...<BR><BR>Then if nothing is passed to those paramters they will be null<BR><BR>you can check for null&#039s like so...<BR><BR>IF @pParam1 IS NULL <BR>-- Its Null<BR>ELSE<BR>-- Its Not Null<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts