Simple-Tech.info

How to run a stored procedure to SQL Server from a ASP web based page

 The tip is to add parameters of stored procedure on Query whith this format :
For a INTERGER
   @StoredParameterName1=1
For a VARCHAR
   @StoredParameterName2='sample string'

You can find the parameter name of each parameters on :
"Microsoft SQL Management Studio => Databases => [Your Database Name] => Programmability => Stored Procedure => [Select your stored procedure] => Right click => Modify"

Find each parameters with char @ 

USE
[MyDatabase]

GO
SET ANSI_NULLS ON
GO
ALTER PROC [dbo].[StoredProcTMP]
@nParam1  INT = 12,
@nParam2 INT = 1,
@nParam3 NVARCHAR(255) = 'My String'
AS
BEGIN
SET NOCOUNT ON
DECLARE @sMsg NVARCHAR(512)
END 

 ASP Web page content 

' création de l objet connexion
Set oConn_Step = Server.CreateObject("ADODB.Connection")
' ouverture de la connexion 
oConn_Step.Open "Driver={SQL Server Native Client 10.0}; Server=[server name]; Database=[database]; Uid=[username]; Pwd=[password];"
' Execute a stored procedure from ASP
sSQL_Step = "EXEC LoadDeviceGroup @nParam1=136, @nParam2=1, @nParam3='##2DCFED825DB94ADFB8A170273EEE03E7'"
Set oRS_Step = server.CreateObject("ADODB.Recordset")
oRS_Step.Open sSQL_Step, oConn_Step

 Microsoft KB 164485 - How to call SQL Server stored procedures from ASP