CODEWITHSUNDEEP
sql-server
Relativity
Relativity

Reputation: 6868

How to automate given SQL query

I have an update query as given below. Can somebody tell me how to automate this, please

1)I have to execute First part.
2)Then comment line "AND s.ExtractYear = l.ExtractYear"...
3)Uncomment "--and l.ExtractYear = 2011" and "--WHERE s.Prod IS NULL", then execute.
4)Uncomment "--and l.ExtractYear = 2010" and "--WHERE s.Prod IS NULL", then execute.
5)Uncomment "--and l.ExtractYear = 2009" and "--WHERE s.Prod IS NULL", then execute.
6)Uncomment "--and l.ExtractYear = 2008" and "--WHERE s.Prod IS NULL", then execute.
7)Uncomment "--and l.ExtractYear = 2007" and "--WHERE s.Prod IS NULL", then execute.

--First part
UPDATE s
Set Col1 = value
FROM table1 s
INNER JOIN LkpTable l
ON
s.PId= l.PId
AND s.ExtractYear = l.ExtractYear


--Second part
--and l.ExtractYear = 2011
--and l.ExtractYear = 2010
--and l.ExtractYear = 2009
--and l.ExtractYear = 2008
--and l.ExtractYear = 2007
--WHERE s.Prod IS NULL

Upvotes: 0

Views: 685

Answers (3)

Serg
Serg

Reputation: 2427

Try this one:

-- Will use EXECUTE statement as
-- 'Execute a character string'
DECLARE @cmdUpdate VARCHAR(200)
DECLARE @iYear INT
DECLARE @cYear VARCHAR(5)

SET @cmdUpdate = 'UPDATE s
                  Set Col1 = value
                  FROM table1 s
                  INNER JOIN LkpTable l
                  ON
                  s.PId= l.PId'

SET @iYear = 2012
WHILE @iYear >= 2007
BEGIN
  SET @cYear = CONVERT(VARCHAR(5), @iYear)
  IF @iYear > 2011
    -- Executing the first part (@iYear = 2012)
    EXECUTE (@cmdUpdate + ' AND s.ExtractYear = l.ExtractYear')
  ELSE
    -- Executing all other parts consecutively
    EXECUTE (@cmdUpdate + ' and l.ExtractYear = ' + @cYear + ' WHERE s.Prod IS NULL')
  SET @iYear = @iYear - 1
END

Upvotes: 1

Bryan
Bryan

Reputation: 17693

Here's my best guest without having the table structure and some sample data to test. You are testing this query somewhere, right? :)

UPDATE s
    SET Col1 = value
FROM
    table1 s
        INNER JOIN
    LkpTable l
        ON
        s.PId= l.PId
            AND
        (s.ExtractYear = l.ExtractYear
            OR
         (l.ExtractYear IN (2007, 2008, 2009, 2010, 2011)
            AND
          s.Prod IS NULL))

Upvotes: 0

judda
judda

Reputation: 3972

For the second part, you could easily code a loop to cycle through all of the years starting from 2007 onto current year which would automate that section.

You probably wouldn't want to automate the first step with the second step because that is just asking for problems because they are doing two entirely different things.

Upvotes: 0

Related Questions