Solved

Newbie Question Re: Stored Procedures/Views

Posted on 1998-08-06
6
210 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:trillian30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:mitek
ID: 1089431
I don't think you can create a view from an SQL Server trigger. The way I would approach this problem, would be creating an SQL stored procedure that would (using dynamic SQL, of course) drop and re-create the view, based on parameters passed to it.
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.

0
 
LVL 4

Expert Comment

by:mitek
ID: 1089432
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 ...


0
 

Author Comment

by:trillian30
ID: 1089433
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.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 4

Expert Comment

by:mitek
ID: 1089434
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


0
 

Author Comment

by:trillian30
ID: 1089435
You are *so* fabulous! That worked perfectly! Do you want to resubmit so you can get the points for the answer?

0
 
LVL 4

Accepted Solution

by:
mitek earned 200 total points
ID: 1089436
Yep. Thank you very much. Let me know if you still have problems with it.

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question