[RESOLVED] SQL Stored procedures in VB & ADO

[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...