I took some SQL classes and came out with some good information. I had wanted to pass this along to anyone who can use it:
Take advantage of your ability to CREATE PROCEDURES in creating your jqWidget pages.
In your controller program (for example: controller.asp) your code may look like this:
strSQL = "SELECT ID, foo1, foo2 FROM tblFooBar WHERE ID = " & queryString & " ORDER BY ID"
Set rs = Conn.execute(strSQL)
response.write "["
response.write vbNewline vbTab & "{"
response.write vbNewline vbTab & vbTab & chr(34) & "ID" & chr(34) & ":" & chr(34) & rs(0) & chr(34) & ","
response.write vbNewline vbTab & vbTab & chr(34) & "foo1" & chr(34) & ":" & chr(34) & rs(1) & chr(34) & ","
response.write vbNewline vbTab & vbTab & chr(34) & "foo2" & chr(34) & ":" & chr(34) & rs(2) & chr(34)
Of course your SQL statement may be larger than this. That’s where the advantage of CREATE PROCEDURE comes in.
Go to your SQL Program with the code:
CREATE PROCEDURE fetchRecord
@recordID int
AS
SELECT ID,
foo1,
foo2
FROM tblFooBar
WHERE ID = @recordID
ORDER BY ID
Execute the statement. Now it’s stored in your SQL Server.
Back in your controller program:
strSQL = "EXEC fetchRecord @recordID = " & queryString
Set rs = Conn.execute(strSQL)
response.write "["
response.write vbNewline vbTab & "{"
response.write vbNewline vbTab & vbTab & chr(34) & "ID" & chr(34) & ":" & chr(34) & rs(0) & chr(34) & ","
response.write vbNewline vbTab & vbTab & chr(34) & "foo1" & chr(34) & ":" & chr(34) & rs(1) & chr(34) & ","
response.write vbNewline vbTab & vbTab & chr(34) & "foo2" & chr(34) & ":" & chr(34) & rs(2) & chr(34)
See the difference? This helps with security, in the event that you don’t want anyone to immediately see the inner workings of your database.
