SQL results operation question

Question

I know I missasked the question, and that's probably the reason I can't find the solution myself:

How do I take a result in a stored procedure and and change the data such as

    if column1 = AAA then 
      column1=Hello
    else if column1 = BBB then 
     column1 = goodbye

and have the data return with the new values?

Thanks

Answer 1

Use the CASE statement:

SELECT CASE
         WHEN column1 = 'AAA' THEN 'Hello' 
         WHEN column1 = 'BBB' THEN 'goodbye' 
         ELSE ... -- optional
       END

Answer 2

these two queries:

UPDATE table_name SET column1='Hello' WHERE column1='AAA'
UPDATE table_name SET column1='goodbye' WHERE column1='BBB'

Answer 3

If you can get to the content of the stored procedure, you should change it to a table-valued function instead - if possible, an inline one (because that has a much higher chance of being able to be optimised).

This will let you query the results and change it as needed:

SELECT CASE column1 
         WHEN 'AAA' THEN 'Hello' 
         WHEN 'BBB' THEN 'goodbye' 
       END as NewColumn,
  ,*
FROM dbo.yourNewFunction() as t;

If you can't do this, and are very much stuck with a stored procedure, you could wrap it up in OPENROWSET:

SELECT CASE column1 
         WHEN 'AAA' THEN 'Hello' 
         WHEN 'BBB' THEN 'goodbye' 
       END as NewColumn,
  , *
FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=Yes;','EXEC dbo.YourProc') as t;

This is a way to treat the output of a procedure as a table expression, but if you can do it without the procedure, that's better still.

Answer 4

you'll have to rename everything starting with my to your actual table names

CREATE PROCEDURE myProc
AS
BEGIN
    UPDATE myTable 
        SET Column1 = 
            CASE
            WHEN 'AAA' THEN 'Hello' 
            WHEN 'BBB' THEN 'goodbye'
            END
    WHERE Column1 in ('AAA','BBB')
    OUTPUT myTable.myIDColumn, INSERTED.Column1
END

Updates the table and returns only the updated records once you

EXEC myProc

Reference 1. http://technet.microsoft.com/en-us/library/ms177564.aspx