How to retrieve value and use it in stored procedure in one SQL query?

Question

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

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.

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.

Thanks for any help.

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

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

Something like

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

then from with your data retrieval sproc call

DECLARE tasktype int

SELECT tasktype = dbo.TaskType

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