Question: Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question: Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-04 : 08:06:57

kay writes "I read these articles about Dynamic SQL but still can't find the solution I'm looking for.

WHen I have a code something like this;


Declare @sql varchar(500)
SET @sql = 'ALTER TABLE t1 ALTER COLUMNS c1 datetime'
EXEC (@sql)

How can I catch the error returned by @sql?
The statement I have to execute has a possibility of producing any error; (in this case, it cause a syntax error when c1 column datatype can not be converted to datetime)
@@ERROR seems not working this case."

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-04 : 08:31:01

Declare @sql varchar(500)
SET @sql = ' IF (SELECT COUNT(*) FROM t1 WHERE ISDATE(c1) = 0) = 0 BEGIN ALTER TABLE t1 ALTER COLUMNS c1 datetime END'
EXEC (@sql)

________________
Make love not war!

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-12-06 : 09:37:23

Hi,

Try to execute the following script.

begin
Declare @sql varchar(500)
SET @sql = 'ALTER TABLE testtable ALTER COLUMNS dd datetime'
EXEC (@sql)

If @@error > 0
begin
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
end
end

:) While we stop to think, we often miss our opportunity :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources