如何在一个SQL查询中检索值并在存储过程中使用它？

I am writing a Silverlight client that interacts with an SQL database via ASP.NET 2.0 web services, which I am also developing. One of the services needs to return the results of a stored procedure and it works well. However, one parameter of the stored procedure needs to be retrieved from a different location before the stored procedure can be executed and this additional request to the database causes an obvious slowdown (evident when I cache the retrieved value rather than obtaining it every call).

我正在编写一个Silverlight客户端,它通过ASP.NET 2.0 Web服务与SQL数据库交互,我也在开发它。其中一项服务需要返回存储过程的结果,并且运行良好。但是,在执行存储过程之前,需要从不同的位置检索存储过程的一个参数,并且对数据库的这个附加请求会导致明显的减速(当我缓存检索到的值而不是每次调用时都会显而易见)。

Unfortunately, caching the value isn't valid for my situation and I'd rather combine the retrieval of this value and the subsequent stored procedure execution into a single query so that the the server can optimise the request. However, my SQL is not strong and I haven't the faintest idea how I go about this.

不幸的是,缓存该值对我的情况无效,我宁愿将此值的检索和后续存储过程执行组合到单个查询中,以便服务器可以优化请求。但是,我的SQL并不强大,我不知道如何解决这个问题。

The value, let's call it tasktype, is referenced via a single key, id. The stored procedure, getrecords, takes a few arguments including tasktype, but it can be assumed that the other argument values are known at the time of calling the query. The stored procedure returns a table of records.

值,我们称之为tasktype,通过单个键id引用。存储过程getrecords采用一些参数,包括tasktype,但可以假设其他参数值在调用查询时是已知的。存储过程返回记录表。

Thanks for any help.

谢谢你的帮助。

2 个解决方案

#1

Well, it could be something like:

好吧,它可能是这样的:

    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@id", ...); // your id arg
    cmd.Parameters.AddWithValue(... , ...); // your other args...
    cmd.CommandText = @"
DECLARE @TaskType int -- or whatever

SELECT @TaskType = // some existing query based on @id

EXEC getrecords @TaskType, ...
";

However, you will perhaps have to clarify how to get a task-type.

但是,您可能必须澄清如何获取任务类型。

You should be able to consume this either as an IDataReader, or using DataTable.Load.

您应该能够将其作为IDataReader或使用DataTable.Load来使用。

#2

You should be able to create a UDF that will get the value of tasktype and call this from within your data retrieval sproc.

您应该能够创建一个UDF,它将获取tasktype的值并从您的数据检索sproc中调用它。

Something like

CREATE FUNCTION dbo.TaskType()
Returns int
   SELECT ... stuff that gets task type
END

then from with your data retrieval sproc call

然后从你的数据检索sproc调用

DECLARE tasktype int

SELECT tasktype = dbo.TaskType

or something like that...might need a bit of reworking :-)

或类似的东西...可能需要一些改造:-)

#1