Select variable = value... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select variable = value...

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 13:58:28

Currently when I run SELECT @__id, I only get back the last row...how can I get SELECT @__id to return all the ID records that meet the criteria? Thanks

DECLARE @__id int

SELECT @__id = [ID] FROM TempTable WHERE [ID] IN
(
SELECT [ID]
FROM TempTable
WHERE (ProductCategoryName NOT IN (SELECT [Name] FROM ProductCategory) AND ProductCategoryName <> '')
OR
(SeasonName NOT IN (SELECT [Name] FROM Season) AND SeasonName <> '')
OR
(CustomerName NOT IN (SELECT [CustomerName] FROM Customer) AND CustomerName <> '')
)
SELECT @__id

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-27 : 14:14:27

@__Id is declared as an int (thats singular). You want a list, and it is no longer an int, it could be a table with an int column or a csv string.


Declare @myTable table (id int)

Insert Into @myTable
SELECT [ID] FROM TempTable WHERE [ID] IN
(
SELECT [ID]
FROM TempTable 
WHERE (ProductCategoryName NOT IN (SELECT [Name] FROM ProductCategory) AND ProductCategoryName <> '')
OR
(SeasonName NOT IN (SELECT [Name] FROM Season) AND SeasonName <> '')
OR
(CustomerName NOT IN (SELECT [CustomerName] FROM Customer) AND CustomerName <> '')
)

Select ID From @myTable

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-07-27 : 14:16:58

@__id is an integer. It can only hold a single value. You could use a table variable.

DECLARE @AllIDs TABLE ([ID] int)

INSERT @AllIDs ([ID])
SELECT [ID]
FROM TempTable
WHERE [ID] IN
(
SELECT [ID]
FROM TempTable
WHERE (ProductCategoryName NOT IN (SELECT [Name] FROM ProductCategory) AND ProductCategoryName <> '')
OR
(SeasonName NOT IN (SELECT [Name] FROM Season) AND SeasonName <> '')
OR
(CustomerName NOT IN (SELECT [CustomerName] FROM Customer) AND CustomerName <> '')
)
SELECT [ID] FROM @AllIDs

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-07-27 : 14:17:50

Gah! Sniped in my own forums

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Harry C
Posting Yak Master

148 Posts

Posted - 2005-07-27 : 14:48:31

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources