Solved

Migrating to SQL Server: need help with stored procedures

Posted on 2004-04-26
15
756 Views
Last Modified: 2009-07-29
Hey Folks,

I have been programming in ASP using an Access database, and I am now migrating to SQL Server 2000.  I want to replace the SQL queries I have been using with stored procedures.  This this is an example of an SQL query I would use to return a recordset, where lngCartID is the parameter I now want to pass to a stored procedure:

    Set rst = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT tblBussCarts.CartID, tblBussCarts.Created, tblBussCartItems.ProductID, tblBussCartItems.Quantity" _
        & " FROM tblBussCartItems INNER JOIN tblBussCarts ON tblBussCartItems.CartID = tblBussCarts.CartID" _
        & " WHERE tblCarts.CartID = " & lngCartID
    rstT.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

I need to know how I would create this stored procedure in SQL Server (I'm assuming using Enterprise Manager), and how to call it with ADO in ASP.

Thanks!
0
Comment
Question by:lpzCoville
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10922675
You can create it in Enterprise Manager or query analyzer.  In query analyzer, you could do something like this:


Create proc BussCarts  @Cartid int as
SELECT tblBussCarts.CartID, tblBussCarts.Created, tblBussCartItems.ProductID, tblBussCartItems.Quantity
FROM tblBussCartItems INNER JOIN tblBussCarts ON tblBussCartItems.CartID = tblBussCarts.CartID
WHERE tblCarts.CartID = @cartid



There are many ways you can call the proc from ASP.  I find it easiest to just execute against a connection object:


set rstT=conn.execute(" BussCarts   " & lngCartid)


conn would be the connection object that you opened previously.

Brett
0
 
LVL 4

Expert Comment

by:mikkelp
ID: 10922702
Hey

you might want to look up stored procedures syntax in a T-SQL reference, but to help you on your way

Create Procedure [owner].[stored proc name]
(
    [@parametername parametertype,
    [more parameters,...]
)
AS
    statements, for instance select, update, insert, delete

for instance
--- procedure begin
create procedure dbo.selectbusscart
(
       @busscartid int
)
as
SELECT tblBussCarts.CartID, tblBussCarts.Created, tblBussCartItems.ProductID, tblBussCartItems.Quantity
FROM tblBussCartItems INNER JOIN tblBussCarts ON tblBussCartItems.CartID = tblBussCarts.CartID
WHERE tblCarts.CartID = @busscartid
--- procedure end

To call this procedure from ASP/ADO you can either call it from an execute on the connection object, or use a command with parameters

' this is not the way to do it, but it should work
set objRS = objConn.execute("selectbusscart " & lngBussCartID)


(You may need to tweak this a bit, I haven't tested it)

set objCmd = Server.CreateObject("ADODB.Command")
objCmd. CommandText = "selectbusscart"
objCmd.CommandType = adCmdStoredProc
objConn.open
set objCmd.ActiveConnection = objConn
objCmd.parameters.append objCmd.CreateParameter("@busscartid", adInteger, adParamInput, 8, lngBussCartID)

set objRS = objCmd.execute

good luck

mikkelp
0
 

Expert Comment

by:donrickman
ID: 10922720

Open Query analyser and select your database

and paste your procedure code in into the window. Hit run >

--Procedure code
create proc ProcedureName
@ingCartID int
as
begin
SELECT tblBussCarts.CartID, tblBussCarts.Created, tblBussCartItems.ProductID, tblBussCartItems.Quantity
FROM tblBussCartItems INNER JOIN tblBussCarts ON tblBussCartItems.CartID = tblBussCarts.CartID WHERE tblCarts.CartID =  @lngCartID
end
--end procedure code.




you can use this code to access it through asp:

Set cn = Server.CreateObject("ADODB.Connection")
      cn.open cnnString

Set rstT = cn.execute("database.owner.ProcedureName")  --create recordset from procedure.






0
 
LVL 4

Expert Comment

by:mikkelp
ID: 10922741
well that figures :o) always refresh the question before posting...
0
 
LVL 4

Author Comment

by:lpzCoville
ID: 10922849
OK, thanks guys, let me work with this before anyone posts anything else.
0
 
LVL 4

Author Comment

by:lpzCoville
ID: 10923530
OK, I have gotten this to work with both

      Set cmd = Server.CreateObject("ADODB.Command")
      With cmd
            .CommandText = "procGetCartItems"
            .CommandType = adCmdStoredProc
            .ActiveConnection = cnn
            .Parameters.Append cmd.CreateParameter("@CartID", adInteger, adParamInput, 8, lngCartID)
            Set rst = .Execute
      End With

and

      Set rst = cnn.Execute("procGetCartItems " & lngCartID)

However, both of these methods omit the (crucial!) ability to select the cursor type, as in my orignal example.  

Therefore I am using

      Set rst = Server.CreateObject("ADODB.Recordset")
      rst.Open "procGetCartItems " & lngCartID, cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc

which also works just fine and lets me have my cusror too.

So to wrap, three things:

1) The SQL syntax for the Connection.Execute and Recordset.Open methods does not seem to specifically reference the @CartID parameter by name. What is the SQL syntax for multi-parameter stored procedures?

2)  I The Command method allows specific declaration of the parameter name(s), however, is there a way to specify the cursor properties using this method?

3) What are the relative advantages concerning speed, etc, among these methods -- which one is considered to be best practice?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10923657
1.  You just pass the extra parms (enclosing them in single quotes if alpha):


Set rst = Server.CreateObject("ADODB.Recordset")
     rst.Open "procGetCartItems " & lngCartID & "," & lngParm2 & ",'" & strParm3 &"'", cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc


2.  You can change the parm on the connection object as well.

3.  I think the command object is the "best practice" method.  However, using this method you tend to sometimes having problems matching data types....

Brett
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Expert Comment

by:mikkelp
ID: 10925597
you can use this approach

set objrs = server.CreateObject("Adodb.Command")
objRs.LockType = adLockReadOnly
objRs.CursorType = adOpenStatic
' objRs.CursorLocation = adUseClient (for disconnected recordsets)

objRs.open objCmd, objConn

0
 
LVL 13

Expert Comment

by:danblake
ID: 10926115
@all -->
when using a SP with parameters, Best practice is to use the
CREATE PROCEDURE <Name>
WITH RECOMPILE (Params)
as (Sql)

Have a look at the Article Bart Duncan (MS) Produced about this due to parameter sniffing.

http://www.microsoft.com/technet/community/chats/trans/sql/sql0724.mspx
0
 
LVL 4

Expert Comment

by:mikkelp
ID: 10926251
:-|

Well, not in the general case... you would use WITH RECOMPILE only if the execution plan needs changing. So for at simple lookup using parameters referencing indexed columns, you would not need the recompilation.. like the one i question.

If on the other hand, the sql statement were built dymaically or relied on subqueries, WITH RECOMPILE would be the right thing to do

mikkelp
0
 
LVL 13

Expert Comment

by:danblake
ID: 10926324
The reason for the introduction of the WITH RECOMPILE option is for the other querys that may be generating very different results, for a multiple parameter input/output plan -- giving the hint that this is going to be extended for other purposes.
(Such as a parameter to define the number of records returned: SET ROWCOUNT @variable would also produce very different results without the need of a subquery of dynamic sql.)

Do we know that CartID is indexed in this case ?
It would be a very good idea if this is queried against heavily to ensure that this is indexed.

(The same goes for all parameter based fields in a SP involved in a query if possible ensuring an index seek rather than a table scan).
0
 
LVL 4

Expert Comment

by:mikkelp
ID: 10926919
it was merely the
"when using a SP with parameters, Best practice is to use the
CREATE PROCEDURE <Name>
WITH RECOMPILE (Params)
as (Sql)"
that I objected to, because it's not, per say. It depends on the Sql in the procedure as you write ; "...for the other querys that may be generating very different results,..". If queries return "the same" every time there's no need for a recompile, otherwise there is. In standard CRUD procedures you would not need to recompile because the execution plan is always the same - but you should of course tune it with proper indexing and the like, as you write.

... and I assumed CartID to be the primary key of the table (sounds like it to me), so it would be indexed by default :-P, but of course this needn't be the case.

mikkelp
0
 
LVL 4

Accepted Solution

by:
mikkelp earned 500 total points
ID: 10927175
as for the question at hand:

1) do as Brett says, if you're going for the direct call. To add more parameters to the stored proc, you just separate each (@name sqltype) pair with a comma.
The command object supports named parameters, but I don't think it enforces them. If in doubt, append them in the order specified in the stored proc. Typos may also have an effect here.

2) The cursor type and location are properties of the RecordSet, so instantiate a RecordSet, set properties and use "objRS.open objCmd". Like this:

Set rst = server.CreateObject("ADODB.RecordSet")
rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly

Set cmd = Server.CreateObject("ADODB.Command")
     With cmd
          .CommandText = "procGetCartItems"
          .CommandType = adCmdStoredProc
          .ActiveConnection = cnn
          .Parameters.Append cmd.CreateParameter("@CartID", adInteger, adParamInput, 4, lngCartID)
     End With

rst.open objCmd


3) I don't think there's much difference in speed. the ADO Connection would create a Command Object if none is specified, so the same objects will be in play... even though your sql will be executed as sqltext and not stored proc. By explicitly setting the CommandType you save the trouble of a dynamic exploration, but I think the gain is neglible. The real "gain" is the automatic checks and "wrapping" supplied by ADODB.Parameter

ie. the objCmd.parameters.append objCmd.Createparameter("@CartID", adInteger, adParamInput, 4, lngCartID) statement.. It verifies that you are in fact using an integer of at most 4 bytes (32 bit integers, asp uses 16 bits integers and 32 bit longs). In the case of a  (...adVarChar, adParamInput, 42, strHelloWorld)
the parameter would restrict strHelloWorld to the first 42 characters AND it would protect your application from Sql injection attacks, because it ignores any SQL code that may be in the string. (a parameter is not evaluated with the rest of the sql command).

Regarding matching datatypes, using only strings and integers/longs, you're in the clear using adChar, adVarChar and adInteger. When using other types you need to be a little more carefull, especially the adNumeric, which you will need to modify after adding.. (Precision and NumericScale, they are properties of the created Parameter object, which you reference like this : objCmd.parameters(index).Precision and .NumericScale)

mikkelp
0
 
LVL 13

Expert Comment

by:danblake
ID: 10927231
I do agree with you mike, that it does depend on the procedure being created/process the sp is being used for...

I wanted to introduce the concept, as we did not know how this was going to extend within the db/process and this was a very open question.
0
 
LVL 4

Author Comment

by:lpzCoville
ID: 10929502
Wow, this is a big subject.  I'm afraid the  WITH RECOMPILE debate is totally off my screen for the time being.  Yes, CartID is indexed (the identity column for tblBussCarts, FK for tblBussCartItems, and vb type long. sql type int.)  Sorry if some of this was not implicitly obvious from the notation in the example presented.  I am awarding the points mikkelp because I found all his answers extremely helpful, specific and thorough, with relevant additional information beyond the original question.  Thanks!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now