> From: Michael Babcock <[EMAIL PROTECTED]> > > I'm creating a new app that uses brand new SQL Server tables. This app > is currently VFP for the UI but it'll be going DotNet in the future > sometime, and I'd like to design for that migration now rather than > later since this is a new app. I like to use Stored Procs for the > benefit of re-use, and had started writing a simple stored proc to > return the dataset desired (like a parameterized view in VFP). > > I had heard something long ago that views in SQL Server (2000) didn't > function the same as views in VFP. I recall something about a > performance issue? Come to think of it, it wasn't SQL Server where I > was told this---it was on an Oracle 9i system. They didn't like to use > views for some performance concern (and that came from the DBA, iirc).
Views are a read only object in SQL server and Oracle and not a working cursor like your use to. I like to put views together so the joins on specific data are preset by me and I can give them to accounting or marketing. All their data is there, and the where clause separates the data they desire. Select * from vCS_CustomerSearchFull where Last_Name 'russell' That view will join 3 tables, and union 4 times to total 4 different values of business #'s that customer service needs. > For those who integrate VFP with SQL Server...would you set up a SQL > Server view and then have a stored proc that returned the view, or would > you just write the SQL Select code into the stored proc and return > that? I'm unfamiliar with how parameterized views work in SQL > Server...I'm hoping it's as easy as in Fox, but I doubt it will be. ;-) There are no parameteried "views" in SQL Server. A view can have limits like I wrote above via a where clause. You can have params in a SP but a SP isn't two or 3 ways, but it could be if you really wanted to screw with it. It's better to write the 3 SPs for your table. For your update try this: CREATE PROCEDURE Merch_updateMerchant @merch_id bigint, @login varchar(20), @pswd varchar(20), @f_name varchar(25), @l_name varchar(35), @address_1 varchar(30), @address_2 varchar(30), @city varchar(30), @state char(2), @zipcode char(10), @email varchar(50) AS UPDATE Merchant_List SET [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] WHERE [EMAIL PROTECTED] If you have 100 columns, then you really need to consider normalizing. One more thing. Make your SPs named according to the namespace of the code your writing. As your systems get big, and your dealing with 1400 + SPs, it's the only way to make any sense! I still have a couple of hundred of the "usp_"?????????? files and they are all SPs, well no shit. _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
