Set MS Sql value in variable and reuse it
My code currently looks something like this.
select *
from tblReq
where ReqID in (select ReqID from tblLog where LogDate >= '2015/04/01' and LogDate < '2015/05/31')
Just wondering how the database actually finds the result for this query? Does it rerun every time it runs in a subquery? And is there a script where I can store the list of results in some variable and be able to use it back? (below code)
select @logs = tblLog.ReqID from tblLog where tblLog.LogDate >= '2015/04/01' and tblLog.LogDate < '2015/05/31'
select * from tblReq where ReqID in (@logs)
source to share
Yes, you can store the result in a variable and reuse it several times later. In your case, that will be table variable
, since you can have multiple elements. Then, just join
to the original query:
DECLARE @Logs TABLE
(
[LogID] INT
);
INSERT INTO @Logs ([LogID])
Select tblLog.ReqID
from tblLog
where tblLog.LogDate >= '2015/04/01'
and tblLog.LogDate < '2015/05/31'
select *
from tblReq A
INNER JOIN @Logs L
ON A.ReqID = L.LogID
Also, it can hurt the performance of your query as table variables are not like black box
for the query optimizer. If you store a large number of rows, use tables temporary
instead to use parallel execution plans.
source to share
You can create view
if you want to refer to your request later
CREATE VIEW V1 AS
SELECT tblLog.ReqID
FROM tblLog
WHERE tblLog.LogDate >= '2015/04/01'
AND tblLog.LogDate < '2015/05/31'
In the comment below, you can use table valued function
as well
CREATE FUNCTION functionName(@start DATE, @end DATE)
RETURNS @result TABLE
(
ReqID INT NOT NULL
)
AS
BEGIN
INSERT @result
SELECT tblLog.ReqID
FROM tblLog
WHERE tblLog.LogDate >= @start
AND tblLog.LogDate < @end
RETURN;
END;
source to share
For your case .. the best solution is to use a connection
The best one (which will work even in difficult cases) involves a table-like query (tblLog)
select * from tblReq join (select logid from tbllog where
tblLog.LogDate >= '2015/04/01' and tblLog.LogDate < '2015/05/31')tblLog
on tblReq.ReqID=tblLog.ReqID
Or simply
select * from tblReq join tblLog on tblReq.ReqID=tblLog.ReqID where
tblLog.LogDate >= '2015/04/01' and tblLog.LogDate < '2015/05/31'
Or simpler: you can also use a subquery (subquery is simpler, but cases of slow performance with too many results)
select * from tblReq where ReqID in (select tblLog.ReqID from tblLog where
tblLog.LogDate >= '2015/04/01' and
tblLog.LogDate < '2015/05/31')
If you need to store because you are using it many times in the same process, then
craete TABLE #logs
(
[LogID] INT
);
insert into #logs select tblLog.ReqID from tblLog where
tblLog.LogDate >= '2015/04/01' and
tblLog.LogDate < '2015/05/31'
select * from tblReq where ReqID in (select logid from #logs) // or use join
Attention!
variable
one value is stored. temporary storage of tables complete table
. For iteration (through strings of temporary tables or others) there is cursors
. However, try using
source to share