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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-20 : 09:17:09
|
| Uffe writes "Is it possible to create a dynamic stored procedure at runtime from inside VB6?I have different kinds of complex queries that I can't handle with ordinarie SQL-queries, so I have to work with T-Sql inside a stored procedure!My customers are working with a report generator, and can create any kinds of reports, and I have to dynamicly create the suitable recordset for the report!" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-20 : 10:03:36
|
| Unless your end users are using the same report criteria multiple times, (forgive me SQL Team for saying this!) you are probably better off not using stored procedures in this circumstance. If the structure of the SELECT statement is changing as much as you say it is, none of the benefits of stored procedures can be utilized. Dynamic SQL is the order of the day for you, look here for more information:http://www.sqlteam.com/item.asp?ItemID=4599http://www.sqlteam.com/item.asp?ItemID=4619There's another reason why I think you should avoid stored procedures: maintenance. You MUST be extremely diligent in dropping these sprocs after they're used, otherwise you will have naming conflicts, and can create hundreds of objects that are only used once. You can alleviate this somewhat by using temporary stored procedures. These are documented in Books Online, read up on them, but again I think you should stick with dynamic SQL or rethink how dynamic the reports need to be. |
 |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-02-20 : 10:22:11
|
| robvolkI would rather have the dynamic SQL created in a SP, so that if the business logic changed, you could make the changes in the SP instead of in the VB6 program. Advantages would be not having to recompile VB6 code to make certain kinds of changes in the business process. After the recompile, you would then have to upgrade all of the users who have this VB6 program on their machine. This is just my suggestion and I am curious about your opinion on this. Am I being logical? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-20 : 10:45:40
|
| From what he's provided in the way of information (which wasn't much), I'm just wondering how you'd go about constructing a SQL statement in an SP when I assume so much of it is dynamic...table names, join conditions, columns, WHERE, GROUP BY etc. It literally might have to come down to something like this:CREATE PROCEDURE GenReport @sql varchar(8000) ASEXEC (@sql)That's about the most efficient way to do it, and it's really not doing all that much. There's no plan to cache in this case. You might get some better performance out of sp_executesql instead of EXEC. It's hard to know without Uffe giving us more info on the tables involved. If he's only talking about 1 or 2 tables and maybe 10 dynamic columns, then I agree with your idea, because that's something that's relatively managable in the way of dynamic SQL. Anything more would be huge and will drive you insane. |
|
|
|
|
|
|
|
|
Create a dynamic stored procedure at runtime from VB6!