[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