Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing SQL string from ASP to SQL Server

    I have a bit of programming logic in ASP that determines what kind of SQL String will be composed. Is there a way to pass this into a Stored Proc directly rather than re-writing the info in a stored proc? one of the problems I noticed is, in the ASP code I am loading the ORDER BY clause using a variable, and this is (almost) not possible in T-SQL. Of course, once the ASP code has determined the desired SQL String, it will have a column name rather than a var and that should be useful, and it would be niuce if I could just load the entire thing into a stored proc as a variable from ASP. This would really speed up things.

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Passing SQL string from ASP to SQL Server

    How many possible sort orders are there? It might be easiest to have two or three SPs and call the appropriate one...

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing SQL string from ASP to SQL Server

    there's a different sort order per column in the table (10) -- including a default.

    FWIW, I have re-considered (for now) the need to rewrite the sorting logic in a stored proc, as it is a pain. Frankly, I am not all that bugged by using ADO for paging the recordset at this time. for smaller recordsets, it's fine and for the largest (45K+) it takes about 6 seconds to load and 4 seconds to page or sort, which is not great but not fatal...if useage goes up, that's a different story...

    So, rather than place all the ASP logic into SQL Server, I thought 'why not pass the SQL String after it's produced via code? therein lies the question. I suppose I may have to do some kind of SELECT CASE procedure somewhere as there are other differences in teh SQL strings besides what column is being sorted.

    I have learned that it is not very easy to write stored procs but i am getting a wee better at it!

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Passing SQL string from ASP to SQL Server

    Can the ORDER BY clause be parameterized? (Not that you care at this point, but it could be a solution.)

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing SQL string from ASP to SQL Server

    Remember that stored procedures perform their best when they can create and optimize execution plans with existing (mostly static) SQL rather than receiving a dynamic sql string each time they are run. If you're passing in a dynamic SQL string to a stored procedure, there's little beneifit over executing a dynamic SQL string from ADO.

    Here's an article on SQL Team's website that provides details on using a dynamic Order By clause. This one has been very helpful to me over the years.

Posting Permissions

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