execute result of select statement

Question

How can I execute the results of my select query. The query below gives me some SQL statements back as result. I want to execute does statements, how to do this? All this is executed in SQL Sever Management Studio.

Query:

SELECT 'UPDATE Rolecopy SET PartofFT = ''' + R2.PlayedbyOT + ''', OriginalOT = ''' + R.PlayedbyOT + ''' WHERE RoleNo = ' + CAST(R.RoleNo AS VARCHAR) + CHAR(13)
FROM Role R INNER JOIN Role R2
ON R.PartofFT = R2.PartofFT AND R.RoleNo <> R2.RoleNo
WHERE EXISTS (
    SELECT PG.RoleNo
    FROM V_PurposeGrouping PG
    WHERE R.PartofFT = PG.PartofFT
    AND R.RoleNo <> PG.RoleNo
)

Result:

UPDATE Rolecopy SET PartofFT = 'Student', OriginalOT = 'Teacher' WHERE RoleNo = 5.00
UPDATE Rolecopy SET PartofFT = 'Project', OriginalOT = 'Teacher' WHERE RoleNo = 8.00
UPDATE Rolecopy SET PartofFT = 'Project', OriginalOT = 'description' WHERE RoleNo = 10.00
UPDATE Rolecopy SET PartofFT = 'Student', OriginalOT = 'Project' WHERE RoleNo = 15.0

0

Divergent

You can use the exec statement: EXEC(@mySql) – Wollis

i'd look into dynamic sql. sure you can use exec(@sql), but that's open to any sql injection. – Grate

doesn't work, because of the quotes i think Msg 203, Level 16, State 2, Procedure SP_Grouping, Line 28 The name '' is not a valid identifier. – Divergent

Answer 1

Try using your first query to open a cursor, then within the loop execute the result string as dynamic SQL.

declare commands cursor for
SELECT 'UPDATE Rolecopy SET PartofFT = ''' + R2.PlayedbyOT + ''', OriginalOT = ''' + R.PlayedbyOT + ''' WHERE RoleNo = ' + CAST(R.RoleNo AS VARCHAR) + CHAR(13)
FROM Role R INNER JOIN Role R2
ON R.PartofFT = R2.PartofFT AND R.RoleNo <> R2.RoleNo
WHERE EXISTS (
    SELECT PG.RoleNo
    FROM V_PurposeGrouping PG
    WHERE R.PartofFT = PG.PartofFT
    AND R.RoleNo <> PG.RoleNo
)

declare @cmd varchar(max)

open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
  exec(@cmd)
  fetch next from commands into @cmd
end

close commands
deallocate commands

Geosyncline

i wonder if someone could come up witha good recursive cte statement to combine with a dynamic sql statement to get this to work. if i had the time i'd try it. – Grate

what i mean is you use a cte to generate the initial select statement, then using a recursive cte to append it all into a string, and then execute that. if you did it right you could also set up the parameters in a different string and send that to the sp_executesql statement. at least in theory it could work, i'd have to try it – Grate

@DForck42, I think I see what you're talking about and it should be doable. I'd be curious to see if anything is gained by it... aside from a maintenance headache... – Geosyncline

sure, but if done right then you don't have the chance of someone putting a string of sql code in the data and having it execute, like you do using exec(@sql) – Grate

Answer 2

Try using this :

SELECT 'UPDATE Rolecopy SET PartofFT = ''' + R2.PlayedbyOT + ''', OriginalOT = ''' + R.PlayedbyOT + ''' WHERE RoleNo = ' + CAST(R.RoleNo AS VARCHAR) + CHAR(13)
FROM Role R INNER JOIN Role R2
ON R.PartofFT = R2.PartofFT AND R.RoleNo <> R2.RoleNo
WHERE EXISTS (
    SELECT PG.RoleNo
    FROM V_PurposeGrouping PG
    WHERE R.PartofFT = PG.PartofFT
    AND R.RoleNo <> PG.RoleNo
)
FOR XML PATH ('')

For which the result is going into one string column:

UPDATE Rolecopy SET PartofFT = 'Student', OriginalOT = 'Teacher' WHERE RoleNo = 5.00
UPDATE Rolecopy SET PartofFT = 'Project', OriginalOT = 'Teacher' WHERE RoleNo = 8.00
UPDATE Rolecopy SET PartofFT = 'Project', OriginalOT = 'description' WHERE RoleNo = 10.00
UPDATE Rolecopy SET PartofFT = 'Student', OriginalOT = 'Project' WHERE RoleNo = 15.0

All in the same column.

Inhibition