3bf0834b   
   From: reb01501@NOyahoo.SPAMcom   
      
   crocboy25 wrote:   
   > Hello,   
   >   
   > I have a short procedure which compiles fine.  When the code is called   
   > from a webpage(.net) it runs the first time it is executed. However on   
   > the second time it throws an error saying "There is already an object   
   > named 'strPKHoldUp' in the database."  StrPKHold is a variable that I   
   > declare in the code as seen below.... I have never had a problem   
   > before with declaring a variable and having it "save or cache" in the   
   > database so you can use it again.  Any idea how to solve this? I tried   
   > moving the DECLARE statement around a little bit but that didnt help.   
   > I changed the variable name to something else and it works one time   
   > but fails after that...   
   >   
   > DECLARE @strPKHoldUp int   
   >   
   > -- PUT IN THE CODE TO SEE IF THE PK AND THE TYPE ALREADY EXIST. IF SO,   
   > UPDATE. IF NOT THEN INSERT.   
   > select MR_MITIGATION_UPLOADS_MITIGATION_FK into strPKHoldUp   
   ...   
   > if @strPKHoldUp = 0   
      
   As David says "into strPKHoldUp" does not assign a value to   
   "strPKHoldUp", it creates a table called "strPKHoldUp" and inserts the   
   rows generated by your sql statement into it. Your code seems to work   
   for you the first time because an int variable contains 0 when it is   
   declared, and you weren't testing the situation where the key already   
   exists, in which case, this code would have quickly failed for you.   
      
   However, he failed to correct your syntax: to assign a value to a scalar   
   variable, you need to use "=", not "into". Also, you need to avoid   
   unintended consequences if your sql statement returns more than one row.   
   When assigning a value to a scalar variable from a sql statement, you   
   need to make sure that the sql statement only returns a single row,   
   something like this:   
      
   SET @strPKHoldUp = (SELECT TOP 1 MR_MITIGATION_UPLOADS_MITIGATION_FK   
   ... )   
      
   Note: I've come to prefer this syntax when assigning a value to a single   
   variable, only using the "SELECT @variable=column_value FROM table"   
   syntax when assigning values from several columns in the same row to   
   several variables   
      
   --   
   HTH,   
   Bob Barrows   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)