EXEC statement

Hi, I am facing a problem here. I am trying to make a stored procedure which accepts an input. The input is a table name within the database. The procedure itself then will make an after update trigger for the table. The purpose of making this stored procedure is because the table keeps changing (columns can be added or deleted) and I don't want to make the trigger manually everytime the table changes, instead I want to execute the stored procedure by passing the table's name and the procedure will create the trigger for me. The problem is sql server 2005 has limited the length of any variable to 8000. The create trigger statement can be longer than that. So using a variable to store the create trigger statement and then executing that variable is not an option. That is why I have inserted the statement to be executed into a column in a temp table. Now how do I execute that statement? I have tried this:

EXEC(SELECT QRY FROM temp_Update)

Qry is the column name which holds the create trigger statement. temp_Update is the temporary table. But if I run it, it will give this error:

Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 123
Incorrect syntax near ')'.

Can anybody tell me how to execute a query which is place in a column in a table? If we can't do this, then what is the workaround, maybe how to have a variable that can hold more than 8000 characters? Any suggestion is greatly appreciate it. Thanks.

This sounds quite 'unwieldy' to me. And smells of a bad data design.

But sometimes you have to live with what you inherit.

Consider having the TRIGGER execute a Stored Procedure.

The TRIGGER never changes.

Then you only have to Alter the Stored Procedure.

And to get past the 8k limit, you can do this:

EXECUTE (@.Variable1 + @.Variable2 + etc.)

|||

I agree with Arnie, since you are using SQL Server 2005, you can change the datatype of the variable from varchar(8000) to varchar(max).

I noticed your example,

Exec(Select Qry From temp_Update)

It is not correct, On exec you have to pass the varchar string or variable as follow as

Exec('Select Qry From temp_Update')

Or

Declare @.SQL as Varchar(max)

Set @.SQL = 'Select Qry From temp_Update'

Exec(@.SQL)

|||Hi Arnie,

Thanks for the quick reply. This is not the case of bad data design. See, we can not predict if in the future we have to add another column to the table. What this stored procedure do is to create a trigger whenever a new column is introduced. The newly created trigger is supposed to detect any changes/update made to that table, i.e finding which column has changed, and then insert the "before update" value and "after update" value to a log table. That way we can have a full history of the table in the log table. It will be a disaster if every time a new column added to the table we have to write the "after update" trigger to incorporate the newly added column. If this can be done then all we need to do is call the stored procedure, pass the name of the table and the trigger will be created automatically.|||

Manivannan.D.Sekaran wrote:

I agree with Arnie, since you are using SQL Server 2005, you can change the datatype of the variable from varchar(8000) to varchar(max).

I noticed your example,

Exec(Select Qry From temp_Update)

It is not correct, On exec you have to pass the varchar string or variable as follow as

Exec('Select Qry From temp_Update')

Or

Declare @.SQL as Varchar(max)

Set @.SQL = 'Select Qry From temp_Update'

Exec(@.SQL)

Hi Manivannan,

If you do it that way, the result will be the content of column Qry. What I want to do is to execute the content of Qry. Can it be done?|||

Yes you can,

Code Snippet

Declare @.SQL as Varchar(max)

Declare @.Qry as Varchar(max)

Set @.Qry = 'Col1, Col2, Col3'

Set @.SQL = 'Select ' + @.Qry + ' From temp_Update'

Exec(@.SQL)

|||

Perhaps a better explication:

DECLARE @.SQL nvarchar(max)

SELECT @.SQL = Qry FROM Temp_Update WHERE {criteria}

EXECUTE( @.SQL )

|||Hey,

Thanks again for the quick reply. I have finally found the answer and now I can make the trigger automatically just by calling the stored procedure. And indeed it is using varchar(max) as the solution. That is why I will give the credit to Manivannan. Thanks again to you and Arnie.sql