[12.5.4] dynamic sql

create proc test_HBU as begin declare @cmd2 varchar(255) set nocount on select @cmd2 = "select 'grant execute on '+ p.name + ' to g_readonly' + char(10 ) + 'go' from Perf..sysobjects p where p.type = 'P'" exec (@cmd2) if @@error > 0 raiserror 99999 'Error ocurred during report dynamic SQL exec' end

Bonjour,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE proc test_HBU
AS
begin
SET nocount ON
 
declare @t varchar(29)
 
CREATE TABLE #tables (
    name varchar(29) NOT NULL,
    trait char(1) DEFAULT 'N' NOT NULL
)
 
INSERT #tables (name)
SELECT DISTINCT name
FROM Perf..sysobjects
WHERE type = 'P'
 
WHILE EXISTS (SELECT * FROM #tables WHERE trait = 'N')
BEGIN
    SELECT @t = name FROM #tables
 
    EXEC("grant execute on " + @t + " to g_readonly")
 
    IF @@error > 0
        raiserror 99999 'Error ocurred during report dynamic SQL exec'
 
    UPDATE #tables SET trait = 'O' WHERE name = @t
END
END