4

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the COUNT(ID) is bigger then a specified value, an UPDATE is issued for another Table WHERE the above IDs are used (SELECT ID FROM TableA WHERE something.. @cur_var)..

The idea is to have the SELECT stored in a variable (pseudo-code idlist = SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var). Then the length of this list is used in the IF (pseudo-code IFlength(idlist)>100. In this case the list is used again in the WHERE statement (pseudo-code again, UPDATE TableB SET value = @cur_var WHERE ID IN idlist)

I hope to be specific enough, I'd like to save the result of a SELECT in a variable. Then use the result in WHERE IN variable and the number of rows in the variable to use in an IF.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

EDIT

I've tried to keep the procedure as reasonable as possible without giving away any private information..

My intention is to remove the duplicate code. The SELECT used in the IF and the SELECT used in the WHERE from the UPDATE are the same, except that I first need the COUNT(ID) and then the actual IDs

DECLARE @cur_id int; 
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
    --this is actually a more complex SELECT but basically gives a list of IDs
    SELECT ID FROM TableC ORDER BY ID DESC

    OPEN Cur;
    FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN

    --here I check if the count of found IDs (with value=NULL and IN the SELECT) is bigger than 5
    IF
    (
        SELECT COUNT(ID) FROM TableA WHERE value=NULL AND ID IN
        (
            SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
        )
    ) > 5
    BEGIN
        --if its bigger, set the value for every ID that is part of the ID list (the same select, but above the count, below no count) 
        UPDATE TableB
        SET value = @cur_id
        WHERE ID IN
        (
            SELECT ID FROM TableA WHERE value=NULL AND ID IN
            (
                SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
            )
        )
    END

    FETCH NEXT FROM Cur INTO @cur_id;
END

EDIT 2:

This is the closest possible solution for the duplicate SELECT i have above..

DECLARE @TempIDs TABLE
(
   ID int
);

INSERT INTO 
    @TempIDs 
--this is the select i have twice in the procedure above
SELECT 
    ID
FROM 
    TableA


SELECT COUNT(*) from @TempIDs
SELECT * FROM @TempIDs

In this case I the SELECT only once, but followed by two other SELECTS later, once using * and once COUNT(*).

Is there no way to use @TempIDs like this? (pseudo-code):

IF COUNT_ROWS(@TempIDs) > 5
BEGIN
    UPDATE TableB SET value = "wow" WHERE ID IN (@TempIDs)
END
2
  • 2
    I'm unable to understand your requirements. Please add table scripts, sample data and expected output to your question. See this article for guidance: sqlservercentral.com/articles/Best+Practices/61537 Nov 12, 2014 at 14:05
  • As the tables/procedure may reveal private information I tried to avoid this, but I completely understand you. I will now copy and rename all the used tables and the procedure. Give me a few minutes. thank you for helping. Nov 12, 2014 at 14:13

1 Answer 1

5

If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course.

DECLARE @Value int, @Label varchar(25)  
DECLARE @Tmp table (value int, label varchar(25)) 

INSERT INTO @Tmp (value, label) 
    SELECT 1, 'One' 
UNION SELECT 2, 'Two' 
UNION SELECT 3, 'Three' 

WHILE (SELECT count(1) FROM @Tmp) > 0 
BEGIN 
    SELECT TOP 1 @Value = value, @Label = label FROM @Tmp ORDER BY value 
    DELETE FROM @Tmp WHERE value = @Value 
    SELECT @Value 'value', @Label 'label' 
END 
1
  • I feel like you answered my question, but my SQL-foo isn't good enough yet, to create this on my own. Would you kindly show me how this is done with SELECT ID FROM TableA assigned to a tmp_var. Later use this tmp_var to compare the COUNT(ID) of tmp_var with an integer and then in WHERE otherID IN tmp_var? thank you so much. Nov 12, 2014 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.