|
Subject:
|
Two selects in one procedure
|
|
Posted By:
|
Trojan_uk
|
Post Date:
|
12/16/2003 6:31:48 PM
|
Hi,
Basically I have a stored procedure that inserts values into a temp table, what I want to do is add a second select to the procedure that takes one of the returned values so I can query another table.
How can I take a returned value then add it to a variable so that I can run the second query.
Thanks in advance for any help
|
|
Reply By:
|
jemacc
|
Reply Date:
|
12/16/2003 11:03:46 PM
|
you can try creating a view
CREATE VIEW myview
AS
SELECT col1,col2, col3
FROM yourtable
WHERE col1 = 'value'
Then query the results of the view
Subquery: Simple Example
Let's say we need to provide a list of all of our customers that have placed orders for more than $1000. We don't need the order information, just the customer information. We could accomplish this with a subquery as follows:
SELECT *
FROM customer
WHERE CustNo IN (SELECT CustNo
FROM orders
WHERE ItemsTotal > 1000)
The subquery is the portion enclosed in parentheses. The above expression tells Local SQL to execute the subquery first, which forms a list of customer numbers for all orders greater than $1000. The other query uses this result as the criteria list for the IN function. In this case, the same result could've been accomplished with an INNER JOIN and a SELECT DISTINCT. We were illustrating a simple example - not all subqueries are so trivial.
|
|
Reply By:
|
nbryson
|
Reply Date:
|
12/17/2003 4:28:22 AM
|
Hope this helps
DECLARE @var varchar(20)
SELECT @var = column
FROM yourtemptable
WHERE whatever = whatever
SELECT *
FROM secondtable
WHERE whatever = @var
|
