[thelist] MS SQL Server - T-SQL
barry sweeney
barry at springcottage.ndirect.co.uk
Tue Aug 19 11:37:30 CDT 2003
Hello all,
I have a disussion forum, the database of which I've just converted to MS
SQL Server from MS Access, and put on a different server to the web server.
In the database there is a topic table for all the topics, and a reply table
for all the replies (well, duh!). In the topics table There is a "Number of
Replies" column and I want to ensure that the figure in that field actually
agrees with the number of replies that have been posted for that topic.
The code that I used to use for validating this for the Access database was
in the vbscript active server page, but it seems to me that it would be much
more efficient to do it in a sproc in the SQL Server database, particularly
as the database is on a different machine. The vbscript snippet is as
follows:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
oConn.Open ConnString
strSQL = "Select Topic_ID, T_Replies From tblTopics"
rs.Open strSQL, oConn, 2, 2
i = 0
do until rs.EOF
i = i + 1
strSQL = "Select count(Reply_ID) AS cnt from tblReply where topic_ID = " &
rs("Topic_ID")
rs1.Open StrSQL, my_Conn
if rs1.EOF or rs1.BOF then
rs("T_Replies") = 0
rs.Update
Else
rs("T_Replies") = rs1("cnt")
rs.Update
End if
rs1.Close
rs.MoveNext
loop
rs.Close
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
I suspect that I will have to use the WHILE statement to achieve this, but
can't get my head around reading the original dataset and then looping
through querying the database using the Topic_ID in order to get the count
of the number of replies. Can somebody give me a clue where to start with
the T-SQL?
Many thanks,
Barry
More information about the thelist
mailing list