Stored Procedure behaves erratically

Results 1 to 2 of 2

Thread: Stored Procedure behaves erratically

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedure behaves erratically

    I am running a bunch of database searches from a single stored procedure in SQL server. It takes a word to search, a table to search and an identity value when it is run, so I call it from the asp with<BR><BR>Query="my_proc("&word&", "&table&", "&search_id&")"<BR>conn.execute(Query)<BR><BR> but based on comma delimited lists of tables and search terms so I have more like:<BR><BR>tablenames="table1,table2,table3"<BR> words="word1,word2,word3"<BR><BR>words=split(words , ",")<BR>tablenames=split(tablenames, ",")<BR><BR>for each tablename in tablenames<BR> for each word in words<BR> Query="my_proc("&word&", "&table&", "&search_id&")"<BR> conn.execute(Query)<BR> next<BR>next<BR><BR>When it runs it will usually return either no results or only results for the first word in the wordlist and the first table in the table list. Refreshing the pages causes a differrent selection of lists to be returned, but never the complete result.<BR><BR>I think this may be a caching problem or something to do with connection pooling- I have tried closing and opening the connection before performing the query and this did not make a noticeable difference. I am not good enough with stored procedures and SQL server to really be sure what is happening here, so all suggestions would be gratefully received.

  2. #2
    Join Date
    Dec 1969

    Default Smaller Pieces

    inside the for loop, response.write the values you think you are a passing the stored procedure and i think your problem will be obvious.<BR><BR>--in the future, test the stored procedure and the asp page independantly, before you test them together if possible. it will save you a lot of time in the long run.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts