SQL While statement

Question

SQL While statement

denis.gendera

Right there with Babe

Points: 733
More actions
January 21, 2015 at 10:16 am

#314718

I have table with PatchGroup Information. I like to run query using While statement that returns specific results for each patch group.
I'm using SQL Function and doing the following
DECLARE @PatchGroup TABLE (patchgroup varchar(128));
INSERT INTO @PatchGroup VALUES ('Patchgroup 1');
INSERT INTO @PatchGroup VALUES ('Patchgroup 2');
INSERT INTO @PatchGroup VALUES ('Patchgroup 3');
INSERT INTO @PatchGroup VALUES ('Patchgroup 4');
select * from @PatchGroup
SET @iCount = @@rowcount + 1
The results from the query "select * from @PatchGroup" I would like to use in "WHERE" Statement for another query.
I would use WHILE (@iCount > = 15)
BEGIN
SELECT name, installed from dbo.testable
where name IN (' <Result from my previous query> ')
END
Is there easy way of doing this? Thx.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

denis.gendera (1/21/2015)
I have table with PatchGroup Information. I like to run query using While statement that returns specific results for each patch group.
I'm using SQL Function and doing the following
DECLARE @PatchGroup TABLE (patchgroup varchar(128));
INSERT INTO @PatchGroup VALUES ('Patchgroup 1');
INSERT INTO @PatchGroup VALUES ('Patchgroup 2');
INSERT INTO @PatchGroup VALUES ('Patchgroup 3');
INSERT INTO @PatchGroup VALUES ('Patchgroup 4');
select * from @PatchGroup
SET @iCount = @@rowcount + 1
The results from the query "select * from @PatchGroup" I would like to use in "WHERE" Statement for another query.
I would use WHILE (@iCount > = 15)
BEGIN
SELECT name, installed from dbo.testable
where name IN (' <Result from my previous query> ')
END

I think something like this is what you are after.

SELECT tt.Name, tt.installed

FROM dbo.testTable tt

INNER JOIN @PatchGroup pg on pg.patchgroup = tt.name

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

denis.gendera Right there with Babe Points: 733 More actions · Answer 2

Thx for the info, I do have follow-up question

the 2nd query does acutally use function to return rows and that should also be populated with the info from first query

Your query

SELECT tt.Name, tt.installed

FROM dbo.testTable tt

INNER JOIN @PatchGroup pg on pg.patchgroup = tt.name

New query:

SELECT tt.Name, tt.installed

FROM dbo.fn_PatchInfo ('Desktop', <ResultFromPreviousQuery>)

INNER JOIN @PatchGroup pg on pg.patchgroup = tt.name

if I try your approach with CTE table, I get error message "The multi-part identifier "#PatchGroup.patchgroup" could not be bound."

LinksUp SSCertifiable Points: 6526 More actions · Answer 3

denis.gendera (1/21/2015)
Thx for the info, I do have follow-up question
the 2nd query does acutally use function to return rows and that should also be populated with the info from first query
Your query
SELECT tt.Name, tt.installed
FROM dbo.testTable tt
INNER JOIN @PatchGroup pg on pg.patchgroup = tt.name
New query:
SELECT tt.Name, tt.installed
FROM dbo.fn_PatchInfo ('Desktop', <ResultFromPreviousQuery>)
INNER JOIN @PatchGroup pg on pg.patchgroup = tt.name
if I try your approach with CTE table, I get error message "The multi-part identifier "#PatchGroup.patchgroup" could not be bound."

That was not clear in your original message.

Please look at the link in my signature about how and what to post to get the best help.

We would need to see the code for fn_PatchInfo as well as DDL for PatchGroup with enough data to come up with a solution.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

denis.gendera (1/21/2015)
I have table with PatchGroup Information. I like to run query using While statement that returns specific results for each patch group.
I'm using SQL Function and doing the following
DECLARE @PatchGroup TABLE (patchgroup varchar(128));
INSERT INTO @PatchGroup VALUES ('Patchgroup 1');
INSERT INTO @PatchGroup VALUES ('Patchgroup 2');
INSERT INTO @PatchGroup VALUES ('Patchgroup 3');
INSERT INTO @PatchGroup VALUES ('Patchgroup 4');
select * from @PatchGroup
SET @iCount = @@rowcount + 1
The results from the query "select * from @PatchGroup" I would like to use in "WHERE" Statement for another query.
I would use WHILE (@iCount > = 15)
BEGIN
SELECT name, installed from dbo.testable
where name IN (' <Result from my previous query> ')
END
Is there easy way of doing this? Thx.

Quick question, can you describe what you are trying to achieve?

😎

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

I'm just guessing here, but you might need the APPLY operator.

Something like this:

SELECT tt.Name, tt.installed

FROM @PatchGroup pg

CROSS APPLY dbo.fn_PatchInfo ('Desktop', pg.patchgroup) pi

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2