DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Dynamic SQL in Stored Procedures-Called with ASP page

  1. #1
    Join Date
    Mar 2005
    Posts
    4

    Question Dynamic SQL in Stored Procedures-Called with ASP page

    I have a series of 12 drop down menus. Each DropDown is dependant on eachother. If the user does not choose anything it should default to "Show All" (1=1). I am having hte hardest time getting it to work. I need the flexibility to have it either show 1=1 or columnname='value'. It either wants to take the values as 1='1' or columnname=value. Neither work. I have tried almost everything. (I only say almost pecause I still have a tiny bit of hope). The only thing that seems to work is building the statement in my ASP page but that will make the page load so slowly and it will be harder for me to integrate things in later. Can someone please help? I posted my latest attempt below.

    ASP:
    'What menu is this for?
    main_col_name="StandardMarketName"

    'Write Menu out
    Response.Write ("<td>&nbsp;</td><td><select name=StandardMarketName onchange=""return submit(this.menu)"">")

    Response.Write ("<option value=""-"">All(default)</option>")

    strSQLStandardMarketNamePop="sp_StandardMarketNamePop @main_col_name = '" & main_col_name & "', @RecYearForm = " & RecYearForm & ", @MediaTypeForm = '" & MediaTypeForm & "'"

    set RS = MyConn.Execute(strSQLStandardMarketNamePop)

    __________________

    Stored Procedure:
    CREATE PROCEDURE sp_StandardMarketNamePop
    @main_col_name varchar(100)
    , @RecYearForm varchar(100) = '0000'
    , @MediaTypeForm varchar(100) = '-'
    , @debug bit = 0
    As
    Declare @SQL varchar(1000)

    Select @SQL = 'Select ' + @main_col_name + Char(10)

    Select @SQL = @SQL + 'From SpotReport' + Char(10)

    Select @SQL = @SQL + 'Where '

    Select @SQL = @SQL +
    Case @MediaTypeForm
    When '-' Then '1 = 1'
    Else 'MediaType = ''' + @MediaTypeForm + ''''
    End
    + Char(10)

    Select @SQL= @SQL + ' And ' +
    Case @RecYearForm
    When '0000' Then '1 = 1'
    Else 'RecYear = ' + @RecYearForm
    End
    + Char(10)

    Select @SQL= @SQL + 'Order By ' + @main_col_name

    If @debug = 0
    EXEC (@SQL)
    Else
    Select @SQL

    Error:
    "incorrect syntax near ","

    Is the problem with my ASP, Stored Procedure, or ir what I am trying to do impossible?

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    What SQL do you get back when @debug = 1?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Mar 2005
    Posts
    4
    I'm not rexactly sure what you mean by what SQL I get back. Could you please clarify?

    Right now the SQL Profiler is reporting the following back when I run a trace:
    exec sp_StandardMarketNamePop @main_col_name = 'StandardMarketName', @RecYearForm = , @MediaTypeForm = ''

    select * from sp_StandardMarketNamePop @main_col_name = 'StandardMarketName', @RecYearForm = , @MediaTypeForm = ''

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    I think "@RecYearForm = ," is your problem. Try simply omitting the @RecYearForm and @MediaTypeForm parameters if there's no value to pass, e.g.: exec sp_StandardMarketNamePop @main_col_name = 'StandardMarketName'
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Mar 2005
    Posts
    4
    I definatly think you are correct that that is my problem but unfortunately I am pretty sure I need those. All my menus are dependant on one another with a default to show all values. So say we have 3 menus Country, State, and County. If the user does not select anything the menus should populate when the page loads with Every Country in the worls, Every State in the World, and Every County in the world. Once the user selects a State the menus will then reload and load the information for that Country, etc. That is why I want the 1=1 or "show all". Is there any way to do this?

  6. #6
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Your stored proc specifies default values for @RecYearForm ('0000') and @MediaTypeForm ('-'). If you don't pass values for these parameters, the stored proc should use the default values. Can you please try my suggestion and let us know if it works?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  7. #7
    Join Date
    Mar 2005
    Posts
    4
    If I try what you mentioned then if I pass in actual values via the drop down those values won't get passed to the stored procedure. For example lets say I have the aforementioned dropdowns and the variablename for what is submitted for Country is RecYearForm and the main_col_name=Country(the column in the table). The other dropdowns have their own stored procedures that are stacked and each dependant on the one before it (State dependant on Countr,y County dependant on State). When the page first loads it will execute the stored procedure (the value for RecYearForm is then blank so 1=1 shows everything-Or the where clause will be where 1=1). All other menus then populate with the default values showing everything. So theoretically we can have the stored procedure for County being where 1=1 and 1=1 and 1=1. Next the user only wants to see only information for the USA so he selects that and all the other menus now populate with that info (Country='USA'-USA being the RecYearForm value submitted). So now the where clause for the County dropdown will be where Country='USA' and 1=1 and 1=1. This theoretically will now populate the other menus with all states and counties in the USA.

    As I was typing I thought of a solution that may work but it would definately be a perormance issue. It would work if I put an If RecYearForm="" or RecYearForm="-" then RecYearForm="0000"

    But doing that for 12 menus would really mean a really slow load time. Any other opinions?
    Last edited by Buckles; 03-30-2005 at 08:25 PM.

  8. #8
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    I don't really understand your load time problems, but all you need to do is add a couple more WHEN-s to the CASE statements, isn't it?

    Rune
    If you hit a brick wall, you didn't jump high enough!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center