Microsoft KB Archive/288185

= FIX: Stored Procedure Continues to Use Parameter Value Passed During First Execution =

Article ID: 288185

Article Last Modified on 7/19/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q288185



BUG #: 101265 (SQLBUG_70)



SYMPTOMS
A stored procedure that accepts a parameter, which is used to determine how many columns to add to a temporary table that the stored procedure creates, might continue to use the value of the parameter passed the first time the procedure was executed, even though different values are passed for subsequent executions.



WORKAROUND
Create the procedure using a WITH RECOMPILE clause.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack SP3 for Microsoft SQL Server 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:

http://www.microsoft.com/sql/prodinfo/previousversions/downloads/servicepacks.mspx

For more information, contact your primary support provider.



Steps to Reproduce Behavior

 * 1) Run the script below.
 * 2) Modify the script, commenting out the three lines as indicated by the comment in the script.
 * 3) Run the script again.

USE tempdb go DROP PROCEDURE pr_TestProc go CREATE PROCEDURE pr_TestProc @Columns INT AS SET NOCOUNT ON DECLARE @SQL VARCHAR(1000) -- Now remove the next three lines (which do nothing valuable) and try again. CREATE TABLE #tmpJunk (JunkID INT)       -- COMMENT THIS LINE INSERT #tmpJunk (JunkID) VALUES (0)      -- COMMENT THIS LINE DROP TABLE #tmpJunk                      -- COMMENT THIS LINE CREATE TABLE #tmpTbl (XX int) SET @SQL = 'ALTER TABLE #tmpTbl ADD ' WHILE @Columns > 0 BEGIN SET @SQL = @SQL + '[Col' + CONVERT(VARCHAR(10), @Columns) + '] VARCHAR(20)' + ',' SET @Columns = @Columns - 1 END SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) EXEC (@SQL) SELECT * FROM #tmpTbl go

exec pr_TestProc 7 exec pr_TestProc 3 exec pr_TestProc 12 go

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB288185

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.