[RESOLVED] SQL Stored procedures in VB & ADO

[nvierros]

Hey guys,

Wondering if someone could help me out

basically i have..

rs.open "SELECT customerid, custfname, datefrom, dateto, delstats, status FROM Invoices WHERE datefrom='" & getdate & "' And not status= 'Cancelled' order by customerid", con, adOpenKeyset, adLockReadOnly

Now, how could i convert that to a stored procedure in MS SQL and then execute that procedure from VB using ADO?

Help greatly appreciated

[szlamany]

First - with ado you use a command object with parameter objects to talk to SPROCS.

    With objCmd
        .CommandText = "GetSqlSP"
        .ActiveConnection = gCn
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@StoredProc", adVarChar, adParamInput, 50, strSP)
        Set rsParam = .Execute()
    End With

The SPROC is defined basically as:

USE Acctfiles
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSqlSP]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSqlSP]
GO
CREATE PROCEDURE GetSqlSP
	 @StoredProc varchar(50)
AS
Select Parameter_Name,Data_Type,Parameter_Mode,Character_Maximum_Length
From INFORMATION_SCHEMA.Parameters
Where SPECIFIC_NAME = @StoredProc 
Go
GRANT EXECUTE ON GetSqlSP TO AcctfilesUser
Go
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

That script does a lot of other stuff before and after creating the SPROC - but you should get the idea...

[nvierros]

Is this ok?

I created a SPROC

CREATE PROCEDURE [dbo].[getcal] @myinput smalldatetime as 

SELECT customerid, custfname, datefrom, dateto, delstats, status FROM Invoices 
WHERE datefrom=@myinput And not status= 'Cancelled' order by customerid
GO

and my vbcode:

set rs = new recordset
Set rs = con.Execute("EXEC getcal @myinput=" & "'" & getdate & "'")

This appears to do what i want it to do, but is the correct way of doing it?

[szlamany]

That is acceptable.

The CONNECTION does have an EXECUTE - you can use it. I believe that you have less options for type of CURSOR and location of CURSOR.

Also, you are not using the PARAMETERS object - which means you can only pass parameters. When you use the COMMAND and PARAMETER objects you can have both input and output parameters.

Maybe others on the board can shed some light on other differences or limitations.

[brucevde]

Maybe others on the board can shed some light on other differences or limitations.

If you needed to execute a stored procedure several times within a loop. It would be more efficient to use the Command object.

Using Parameters you don't have to worry about things like formatting date strings correctly or replacing single quotes with two single quotes, like you do when creating in-line sql statements.

FYI - your code may fail on computers whose regional date setting is not mm/dd/yyyy.
Using Set rs = New Recordset line is pointless when calling the Execute method. The Execute method creates a new recordset destroying any instance that the rs variable may already contain.