网页如何调用存储过程_网页调用SQL存储过程的步骤

// 假设这是一个ASP.NET Core控制器方法
[HttpPost("executeStoredProcedure")]
public async Task<IActionResult> ExecuteStoredProcedure([FromBody] StoredProcedureParams requestParams)
{
    // 假设requestParams包含存储过程所需的参数
    string connectionString = _configuration.GetConnectionString("DefaultConnection"); // 从配置中获取连接字符串

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        using (SqlCommand command = new SqlCommand("YourStoredProcedureName", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            // 添加参数，注意使用参数化查询防止SQL注入
            command.Parameters.AddWithValue("@Param1", requestParams.Param1);
            command.Parameters.AddWithValue("@Param2", requestParams.Param2);
            // ... 根据存储过程定义添加其他参数

            try
            {
                // 如果存储过程返回结果集
                List<SomeResultObject> results = new List<SomeResultObject>();
                using (SqlDataReader reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        results.Add(new SomeResultObject
                        {
                            Id = reader.GetInt32(reader.GetOrdinal("Id")),
                            Name = reader.GetString(reader.GetOrdinal("Name"))
                            // ... 读取其他列
                        });
                    }
                }
                return Ok(results); // 返回JSON格式的结果
            }
            catch (SqlException ex)
            {
                // 记录数据库错误
                _logger.LogError(ex, "Error executing stored procedure.");
                return StatusCode(500, "Database error occurred.");
            }
        }
    }
}

public class StoredProcedureParams
{
    public string Param1 { get; set; }
    public int Param2 { get; set; }
}

public class SomeResultObject
{
    public int Id { get; set; }
    public string Name { get; set; }
}

SqlConnection

SqlCommand

SqlDataReader

CommandType.StoredProcedure

AddWithValue

PDO

mysqli

PDO

<?php
// 假设这是一个处理POST请求的PHP脚本
header('Content-Type: application/json');

$input = json_decode(file_get_contents('php://input'), true);
$param1 = $input['param1'] ?? null;
$param2 = $input['param2'] ?? null;

$dsn = 'sqlsrv:Server=your_server;Database=your_database'; // SQL Server DSN
$user = 'your_username';
$pass = 'your_password';

try {
    $pdo = new PDO($dsn, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 调用存储过程
    $stmt = $pdo->prepare("{CALL YourStoredProcedureName(?, ?)}"); // 使用CALL语法
    $stmt->bindParam(1, $param1, PDO::PARAM_STR);
    $stmt->bindParam(2, $param2, PDO::PARAM_INT);
    $stmt->execute();

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC); // 获取所有结果行
    echo json_encode($results);

} catch (PDOException $e) {
    // 记录错误
    error_log("Database error: " . $e->getMessage());
    http_response_code(500);
    echo json_encode(['error' => 'Database error occurred.']);
}
?>

PDO

prepare

bindParam

{CALL YourStoredProcedureName(?, ?)}

mssql

mssql

// 假设这是一个Express路由处理函数
const express = require('express');
const app = express();
const sql = require('mssql'); // 引入mssql模块

// 数据库配置
const config = {
    user: 'your_username',
    password: 'your_password',
    server: 'your_server', // You can use 'localhost\instance' for named instance
    database: 'your_database',
    options: {
        encrypt: true, // For Azure SQL Database or if you're using SSL
        trustServerCertificate: true // Change to false for production
    }
};

app.use(express.json()); // 用于解析JSON请求体

app.post('/api/executeStoredProcedure', async (req, res) => {
    const { param1, param2 } = req.body;

    try {
        await sql.connect(config);
        const request = new sql.Request();

        // 添加参数
        request.input('Param1', sql.NVarChar, param1); // 定义参数类型
        request.input('Param2', sql.Int, param2);

        // 执行存储过程
        const result = await request.execute('YourStoredProcedureName');

        // result.recordsets[0] 包含第一个结果集
        res.json(result.recordsets[0]);

    } catch (err) {
        console.error('SQL error', err);
        res.status(500).json({ error: 'Database error occurred.' });
    } finally {
        sql.close(); // 关闭连接池
    }
});

app.listen(3000, () => console.log('Server running on port 3000'));

await sql.connect(config)

await request.execute

request.input