RE: SQL for SP

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 10/01/04 

Date: Fri, 1 Oct 2004 08:09:05 -0700

But you can access this value with a recordset object (ADO). You will be
incurring in extra work because SQL Server has to parse and create an
execution plan for the statement every time you use it. Also, the interface
(ADO) has to create a recordset because you are returning the value using a
SELECT statement.

dim oRst as adodb.recordset
dim oConn as adodb.connection

set oConn = new adodb.connection

' set connection properties and open it
...

strSQL = "INSERT INTO dbo.CompanyData (CompanyName, Address1)
VALUES (" & strCompanyName & ", " & strAddress1 & ") "
strSQL = strSQL & "SELECT SCOPE_IDENTITY() as newIdent"

set oRst = oConn.execute strSQL
...

Here are a couple of articles where to read about ADO Best Practices.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/bestprac.asp

http://www.winnetmag.com/SQLServer/Article/ArticleID/16272/SQLServer_16272.html

AMB

"Alejandro Mesa" wrote:

> That is the beauty of using a sp, you have a return value and output
> parameters. This functionality is not available when using parameterized
> queries on ADO or ADO.NET.
>
>
>
> AMB
>
> "Vlad" wrote:
>
> > I have SP in SQL Server:
> > CREATE PROCEDURE Admin_BuySellCompanyInsertUpdate
> > @CompanyName varchar(50)
> > @Address1 varchar(50),
> > @NewBuySellCompanyID int output
> >
> >
> > AS
> > INSERT INTO dbo.CompanyData (CompanyName, Address1)
> > VALUES (@CompanyName, @Address1)
> > SELECT @NewBuySellCompanyID = SCOPE_IDENTITY()
> > GO
> >
> > Instead of using this SP for Command object with CommandType
> > =adCmdStoredProc in VB6 I would like to have
> > Command object with CommandType = adCmdText
> >
> > I tried to write SQL statement for this case with no success.
> > My last attempt was:
> > strSQL = "DECLARE @NewBuySellCompanyID int OUTPUT "
> > strSQL = "INSERT INTO dbo.CompanyData (CompanyName, Address1)
> > VALUES (" & strCompanyName & ", " & strAddress1 & ") "
> > strSQL = strSQL & "SELECT @NewBuySellCompanyID = SCOPE_IDENTITY()"
> >
> > Set parTemp = New ADODB.Parameter
> > parTemp.Direction = adParamOutput
> > parTemp.Type = adInteger
> > parTemp.Name = "@NewBuySellCompanyID"
> >
> > Set cndTemp = New ADODB.Command
> > cndTemp.ActiveConnection = objConn
> > cndTemp.CommandType = adCmdText
> > cndTemp.CommandText = strSQL
> > cndTemp.Parameters.Append parTemp
> > cndTemp.Execute
> >
> > I'm getting an error
> > Maybe it's not possible at all? Ofcourse it's better to run SP, but I have
> > much more experience with VB than with SQL Server and have more control on
> > pretty complex routine which includes this insert in VB.
> >
> > Any help please
> > Thank you
> > Vlad
> >
> >
> >
> >

Relevant Pages

  • RE: SQL for SP
    ... But you can access this value with a recordset object (ADO). ... strSQL = strSQL & "SELECT SCOPE_IDENTITYas newIdent" ... >> much more experience with VB than with SQL Server and have more control on ...
    (microsoft.public.vb.general.discussion)
  • RE: SQL for SP
    ... But you can access this value with a recordset object (ADO). ... strSQL = strSQL & "SELECT SCOPE_IDENTITYas newIdent" ... >> much more experience with VB than with SQL Server and have more control on ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL for SP
    ... But you can access this value with a recordset object (ADO). ... strSQL = strSQL & "SELECT SCOPE_IDENTITYas newIdent" ... >> much more experience with VB than with SQL Server and have more control on ...
    (microsoft.public.vb.database.ado)
  • Re: How to Mimic Access Externally Linked Tables using ADO?
    ... > using SQL Server for efficiency reasons. ... > connection up programmatically using ADO or ADOX or some SQL Server API? ... >> newsgroup, but if you have a good reason to send me e-mail, you'll find ... ADO does not have a query engine. ...
    (microsoft.public.data.ado)
  • Re: How to access the SQL server express from mfc application?
    ... There are different ways to do this via MFC. ... You could certainly try ADO, ... the entire contents of the database to a SQL Server Standard Server. ...
    (microsoft.public.vc.mfc)