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

    Returning a value from SQL Server Stored Proc

    I'll be looking thru books on - line, but in the meantime if anyone has any suggestions...

    I'd like to do set up a stored proc so it can load values to variables depending on whether <font face="Georgia">exists (SQL Statement)</font face=georgia> is... True? 1? -1? and currently am not sure how stored procs pass results. I can read out a result using <font face="Georgia">Result(0)</font face=georgia> or, if I am actually auditing a recordset, via <font face="Georgia">Result("FieldName")</font face=georgia>.

    In this case, I'm trying to not setup or return a recordset but just find out if a valid row exists. why? I dunno. I guess it seems like the perfomance would be better.

    Overall, what I'm thinking of doing is using a stored proc to load 6 variables with some value depending on whether a row exists on any of 6 tables and don't want to page thru any recordsets to do it. That's why I thought 'why not use <font face="Georgia">exist</font face=georgia>?'

    SO, what I'd like to do is do an if ... then test in a stored proc and load a variable. do this 6 times for 6 variables. then access the stored proc in ASP to retrieve the values and do things to them. horrible, nasty, hideous things.... things like...concatenate them!!!

    whew it's hot today. sorry about the outburst.

    Anyway.... any ideas?

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

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

    Re: Returning a value from SQL Server Stored Proc

    Google for: asp "output parameter" stored procedure and you'll get:

    http://www.asp101.com/samples/storedprocs.asp

    as the top result (along with a host of others).

    Output parameters is what you'll want to use. You can perform whatever logic you need within the stored procedure (such as keeping a tally within a variable), then return the desired variable as an output parameter. Then you can perform whatever logic is necessary in your ASP front-end based on the output parameter.

  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: Returning a value from SQL Server Stored Proc

    thanks -- that looks right. i'm not using ADOVBS right now (out of sheer perversity I'm sure). I like the idea of using stored procs to do some logic internally as well. but what's this Google thing you mention? is that some sort of new Microsoft initiaitive??

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

    Re: Returning a value from SQL Server Stored Proc

    I remember that I decided not to use ADOBVS.inc in my early days until I saw the benefits. Unless you like keeping a reference handy to produce constants all day long, it's absolutely worth it!

    Keep in mind that SQL is a BEAST when it comes to executing data-driven logic. The performance of a well-written SQL procedure can be literally hundereds of times quicker than the same logic written into ADO. This is especially true when you have your data on a separate server from your webserver.

    This may help explain the process a little better...

    Method A - Logic executed in ADO
    1) Create ADO Connection
    2) Open ADO Connection
    3) Create ADO Recordset
    4) Open ADO Recordset (transfers data from SQL box to Web box)
    5) Process logic (VB Script is much slower than T-SQL)
    6) Present Result

    Method B - Logic executed in SQL
    1) Create ADO Connection
    2) Open ADO Connection
    3) Create ADO Command
    4) Create and Populate parameter(s)
    5) Execute Stored Procedure using Command object
    6) Result is returned

    Granted, the number of steps may be the same, but I guarantee the speed will be MUCH faster using method B.

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

    Re: Returning a value from SQL Server Stored Proc

    Yes, i am absolutely looking forward to wrapping my nice, easy to understand and SLOOOOOW VBScript logic into a stored proc. keep in mind, I've only just figured out how to make one do something at all last friday!

    as far as ADOVBS goes, I don't have any real reason not to use it but in the past I've used it for declaring a cursor type and the object being opened for an Access connection. and even then I only used it to get RecordCount to return something more informative than -1.

    generally, I start using something because I'm having a problem and something (such as ADOVBS) helps solve it. there's a LOT I don't know and I tend to accreate a lot of 'recieved opinions' in the course of things. for example, I never use session objects to track anything. I use a connection to a db to instantiate a new autonumber in a row and use that to 'maintain session' in the sense that the autonumber value is appended across pages to tie submissions to multiple tables in a web session. Why is this? Because I've read that session object is not necessarily unique, is not consistently implemented in all platforms, may be in a process of quasi-deprecation, etc. This may not be true but at least I know that if user A starts a process and gets a unique number from the system then User A is always identifiable; the downside of course is that if User A quits for some reason before filling out the forms I have incomplete data. which, however, is not all that hard to identify and delete.

    Anyway, that's an example of a utilizable opinion. I may in fact be completely wrong in terms of best practices but at least my results are useable, which is generally my chief goal. But, my gosh, it'd be nice to have other people at work to bounce ideas off of (and it's GREAT to have the Lounge available). I'm really looking forward to my promotion to Sys Analyst as I can, theoretically, have other people and resources to make good decisions on what constitutes best practices.

    I am quite excited about SQL Server and stored procs and look forward to getting a lot of nice performance boosts as I continue to wrestle with it all.

    cheers! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Returning a value from SQL Server Stored Proc

    It's nice to have that excited feeling. My job doesn't give me that anymore - maybe it's time for me to move on...

    FYI: SessionID numbers are not unique, but a user's Session Object is completely "private" and you should not worry about the server mixing up the sessions of different users.

    As for multi-platform implementation, you realize that Session variables are a server-side issue. They generally do not make any difference from one client to the next, with the small exception of session-based cookies.

    At this point I would suggest that a couple of books would be of great benefit to you. Even if you plan to make the switch to ASP.NET in the near future, I would suggest that you read up on some basic ASP/Web concepts. One of my favorite all-time ASP books is Professional Active Server Pages 3.0, published by Wrox press. As for ASP.NET books, my favorite is ASP.NET Unleashed, published by SAMS.

    It's important to establish good coding habits early on. Bad habits are very hard to break - I've been challenged to break quite a few of my bad habits from my early years!

    Hope this helps

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

    Re: Returning a value from SQL Server Stored Proc

    thanks for the advice. i do have the Beginning ASP book (wrox) and it's not of much use to me now except to look up string handling functions occasionally. with the new position i'll ask for (if they don't already have) the Pro ASP book, something comprehensive for javascript and SQL Server/T-SQL. There are people here who do ASP.NET and we may be going in that direction at some point. I may, in fact, end up playing a role in advising for new technologies...

    One problem I have with the place I am at is we don't necessarily control the webservers we use for hosting projects. another state agency runs the webservers. this means doing neat stuff on the servers is dependent on the skill set of the people maintaining the server. this may be one reason why .NET isn't very widespread. People *do* have security concerns and I respect that.

    Well, enough chat for now. have to retool some javascript on a registration page...

Posting Permissions

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