Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How to create SQL temporary table using parameterised SP
Message
From
28/01/2004 08:22:31
 
 
To
27/01/2004 20:54:00
James Chan
Objectmastery Pty Ltd
Hawthorn, Australia
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00871235
Message ID:
00871328
Views:
15
This message has been marked as a message which has helped to the initial question of the thread.
Here are a couple of ideas, alternatives for you to consider:
1. Add two parameters to the one query.
lcSQL = "Select * From myTable Where myField1 = '" + myVar1 + "' And myField2 = ' + AllTrim( Str( myVar2 ))

In this example, myVar1 is a character value and myVar2 is a numeric value.

2. Create a stored procedure on the sever that returns the data you want and call it;
-- Stored procedure:
-------------------------
Create Procedure myProc
( @cMyVar1 char(10),
@nMyVar2 numeric(10,2))
As

Select < Field List >
From myTable
Where myField1 = @cMyVar1
And myField2 = @nMyVar2

* VFP Calling sample:
* ------------------
lcSQL = "myProc '" + AllTrim( myVar1 ) + "', " + Alltrim( Str( myVar2 ))

The advantage of calling a stored procedure is that SQL Server will store the execution plan after it calls the stored procedure once for subsequent calls. This translates into a significant performance increase in addition to what you get from proper indexes. Also, you can hide your data structure from "prying eyes" if this is run over a network or over the internet.

It's also a good practice to ask for just the fields you need when you query a SQL server. The "*" while faster to write is always slower to execute than explicitly listing the fields that you actually need. It reduces network traffic by sending just what is needed rather than everything.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform