Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
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.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement