Daynamic Execution of SQL ....

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)

Daynamic Execution of SQL ....

Author

Topic

mohit_sharan
Starting Member

22 Posts

Posted - 2003-12-02 : 02:12:11

Hi ,

I want to create a table dynamicaly based on some condition. This is fired on the body of the triger. The code is as follows :
-----------------------------
Alter TRIGGER tbI_wsxMain_Lookup ON wsxMain_Lookup for INSERT as
begin
declare @f_cusip char(9),
@t_cusip char(9),
@t_name varchar(50),
@create_table varchar(500),

set @f_cusip = (select from_cusip from inserted)
set @t_cusip = (select to_cusip from inserted)
set @t_name = (select table_name from inserted)

if ((select count(*) from wsxMain_Lookup where from_cusip >= @f_cusip or to_cusip <= @t_cusip)>1)
begin
print 'Violating the From_Cusip or To_Cusip range. Please check the cusip range which is going to be inserted'
rollback
return
end
else
begin
set @create_table = 'select * into '+@tab_name+' from wsxMain where 1<>1'
EXEC sp_executesql @create_table
select @create_table

end

end
---------------------------
Can any body help me in How to execute the dynamic sql in the body of the trigger.

Thanks in advance.

-Mohit.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 12:22:39

What is the error that you are getting?

This version compiles fine:

Alter TRIGGER tbI_wsxMain_Lookup ON wsxMain_Lookup for INSERT as
begin
declare @f_cusip char(9),
@t_cusip char(9),
@t_name varchar(50),
@create_table varchar(500)

select @f_cusip = from_cusip from inserted
select @t_cusip = to_cusip from inserted
select @t_name = table_name from inserted

if ((select count(*) from wsxMain_Lookup where from_cusip >= @f_cusip or to_cusip <= @t_cusip)>1)
begin
print 'Violating the From_Cusip or To_Cusip range. Please check the cusip range which is going to be inserted'
rollback
return
end
else
begin
set @create_table = 'select * into '+@t_name+' from wsxMain where 1<>1'
EXEC sp_executesql @create_table
select @create_table

end

end

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-02 : 12:45:25

I'm still waiting for Yukon, where TRIGGERs will be able to make Margarittas!

Can you imagine what would happen with a couple of thousand rows on INSERT...boooooom

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources