SQL Server输出参数返回混合结果

62lalag4  于 2022-12-10  发布在  SQL Server
关注(0)|答案(1)|浏览(131)

My goal with this code is to calculate the processing time of the query string that's passed into the stored procedure. The query string is created by the user, a student taking a sql/database course. In a subsequent query, the string is compared against the key to determine if it produces the correct results for the assignment. As you'll see, it's VB.NET code (.NET 4.5 project) calling a C# method (.NET 3.5 project) that calls a stored procedure (SQL SERVER 2012).

My initial call from the main program project is here:

Dim executionTime As Integer = exercise.GetSqlTime(txtUserSQL, connectionString)

The method called is here:

public int GetSqlTime(string SqlString, string ConnectionString)//bookmark
    {
        int executionTime = 0;

        SqlConnection con = new SqlConnection(ConnectionString);
        using (SqlCommand cmd = new SqlCommand("QueryExecutionTime", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@UserSQLstring", SqlDbType.NVarChar).Value = SqlString;
            cmd.Parameters.Add("@Duration", SqlDbType.Int);
            cmd.Parameters["@Duration"].Direction = ParameterDirection.Output;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            executionTime = Convert.ToInt32(cmd.Parameters["@Duration"].Value); 
        }
        return executionTime;
    }

My stored procedure is here:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[QueryExecutionTime] 
@UserSQLstring NvarChar(3000),
@Duration int OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @StartTime datetime = GETDATE() 
EXEC sp_executesql @UserSQLstring
DECLARE @EndTime datetime = GETDATE()   

SET @Duration = DATEDIFF(MILLISECOND,@StartTime,@EndTime)
END

The result is 0 when the query string is correct. When the string is incorrect, it returns a value between 1.4 and 2.5 seconds
This is the first time I've used a stored procedure with an output parameter. I had no idea it would prove to be so challenging.
Am I overlooking a simple detail that would make this work correctly? I have already tried at least five or six examples here on Stack Overflow alone - along with several other websites. What you're seeing here is my latest iteration. If you need additional information, please let me know.
If you know of a better way of doing this, I'm open to any and all suggestions.

osh3o9ms

osh3o9ms1#

您的结果可能是正确的。如果您的SQL SERVER版本是2008或更高版本,请使用SYSDATETIME()而不是GETDATE(),因为它具有更高的准确性(或分辨率)。否则,您可以尝试多次运行查询字符串,然后将结果除以运行次数。应放弃格式错误的查询字符串的执行时间。

相关问题