Newbie Question Re: Stored Procedures/Views
Here is my situation: I am using several views to consolidate data from a rather large table. The selection criteria is based on the contents of another table (that holds the names of the fields to be shown in the views.)
When a user makes a change to the selection criteria through my VB app, the selection criteria tables will be changed. This is a "trigger" to drop and recreate the existing views.
Since I can't drop/create views in triggers or stored procedures, I was wondering if there is a way to call a VB exe from a stored procedure? Or if there is some better way to drop and create a view when a non-related table changes. I know I can do it through the program itself, but I would much prefer do it directly through sql to keep the views in sync with the selection criteria table.
When a user makes a change to the selection criteria through my VB app, the selection criteria tables will be changed. This is a "trigger" to drop and recreate the existing views.
Since I can't drop/create views in triggers or stored procedures, I was wondering if there is a way to call a VB exe from a stored procedure? Or if there is some better way to drop and create a view when a non-related table changes. I know I can do it through the program itself, but I would much prefer do it directly through sql to keep the views in sync with the selection criteria table.
A really sick way of going about this problem would be to write a batch script for isql utility, that would drop/create the views, and call this file via xp_cmdshell from a trigger.
But only a sick (or really geniuos) mind could design something like THAT ...
But only a sick (or really geniuos) mind could design something like THAT ...
ASKER
My understanding is that you also can not create a View in a stored procedure. I'm not really sure what you mean by 'dynamic sql' though.
I didn't say so. You CAN create a view from an SP, but it looks like it's not possible to call this SP from an SQL Server TRIGGER -- that's one of inherent limitations of triggers in SQL Server.
as for dynamic sql ? it's when you make a query within a stored procedure and then execute it with EXECUTE() command.
like that:
DECLARE @01 varchar(255),@02 varchar(255),@03 varchar(255),@04 varchar(255)
SELECT @01 = 'DROP VIEW testView '
SELECT @02 = 'CREATE VIEW testView AS '
SELECT @03 = 'SELECT * FROM ForumTopics '
SELECT @04 = 'WHERE TopicSecurity=''MEM'''
EXECUTE (@01) -- drop the view
EXECUTE (@02 + @03 + @04) -- recreate the view
as for dynamic sql ? it's when you make a query within a stored procedure and then execute it with EXECUTE() command.
like that:
DECLARE @01 varchar(255),@02 varchar(255),@03 varchar(255),@04 varchar(255)
SELECT @01 = 'DROP VIEW testView '
SELECT @02 = 'CREATE VIEW testView AS '
SELECT @03 = 'SELECT * FROM ForumTopics '
SELECT @04 = 'WHERE TopicSecurity=''MEM'''
EXECUTE (@01) -- drop the view
EXECUTE (@02 + @03 + @04) -- recreate the view
ASKER
You are *so* fabulous! That worked perfectly! Do you want to resubmit so you can get the points for the answer?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This procedure would be called from VB app whenever "a user makes a change to the selection criteria"
Yes, there IS a way to call an external program from SP (xp_cmdshell) but I wouldn't even dare to use it to call anything larger than 'dir' command.
I would think, the way to do that is not "to call a VB exe from a stored procedure", but just the other way around. This way, it would be a simple call to SP, which would dynamicly build a DROP/CREATE VIEW statement and handle everything cleanly.
Again, can't give you an ANSWER, but a general plan of actions.
Tell me what you think about it.
BTW, that's NOT a newbie question.