473,695 Members | 2,497 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

alter table #TempTable problem

I am trying to add a column to a temp table and then immeditaely query
against that new column. If I do this in Query Analyzer it works fine
as long as there is a go in between, but I can't use a go inside a
stored proc.. How do i get SQL to finish processing the alter table
command so I can use the new column?

alter table #TempPaging add TIId int not null identity
--go --fixes the problem in QA, but not in proc
Select * From #TempPaging Where TIId > 0 AND TIId < 11

(Error TIId does not exist)

Aug 9 '05 #1
7 2620
pb648174 (go****@webpaul .net) writes:
I am trying to add a column to a temp table and then immeditaely query
against that new column. If I do this in Query Analyzer it works fine
as long as there is a go in between, but I can't use a go inside a
stored proc.. How do i get SQL to finish processing the alter table
command so I can use the new column?

alter table #TempPaging add TIId int not null identity
--go --fixes the problem in QA, but not in proc
Select * From #TempPaging Where TIId > 0 AND TIId < 11


It would help if you provided the context you are trying to do
this in. The problem is that if the procedure is recompiled after
CREATE TABLE, but before ALTER TABLE, the refernce to the column
not yet created causes an error, as there is - luckily! - no deferred
name resolution on column names.

Thus some kind of workaround is needed, which is why I asked for
context.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 9 '05 #2
Why add a column to the temp table? Just create it with the right columns to
start with.

--
David Portas
SQL Server MVP
--
Aug 9 '05 #3
Ok, here is what I am trying to do - make a generic routine for paging
that can be added to any proc and can be maintained easily rather than
copying and pasting in the same code all the time.

In proc 1 I have:

Select *
Into #TempPaging
from ScheduleTask

--do paging based on temp table
exec spDoPaging @itemsPerPage, @Page, @TotalPages OUTPUT, @TotalRecords
OUTPUT

In proc 2(spDoPaging I have:
Select @TotalRecords=C ount(*) From #TempPaging
Select @TotalPages=CEI LING(Cast(@Tota lRecords as
decimal)/Cast(@itemsPerP age as decimal))

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @itemsPerPage
SELECT @LastRec = (@Page * @itemsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT * FROM #TempPaging WHERE TIID > @FirstRec AND TIID < @LastRec

--clean up
drop Table #TempPaging

The kicker is that it all depends on the identity column so that I have
a row number for each item in the list, so that I can get the
appropriate page. so the below line is crucial

--add numbering
alter table #TempPaging add TIId int not null identity

Adding this line to proc1 works and is my workaround for now, but I
would like to have all the paging functionality in the spDoPaging if
possible and just use the "Into #TempPaging" and call to stored proc
anytime I want to add paging to a particular proc.

Aug 10 '05 #4
You can put the IDENTITY function in the SELECT INTO statement, then
you don't have to add it later.

There are also other paging methods that don't require IDENTITY and
temp tables:
http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--

Aug 10 '05 #5
> You can put the IDENTITY function in the SELECT INTO statement, then
you don't have to add it later.


Example:

SELECT IDENTITY(INT, 1,1) AS tiid, *
INTO #TempPaging
FROM YourTable

--
David Portas
SQL Server MVP
--

Aug 10 '05 #6
pb648174 (go****@webpaul .net) writes:
Ok, here is what I am trying to do - make a generic routine for paging
that can be added to any proc and can be maintained easily rather than
copying and pasting in the same code all the time. -- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT * FROM #TempPaging WHERE TIID > @FirstRec AND TIID < @LastRec


You could put this in dynamic SQL with sp_executesql.

However, I think you are in a dead end here. I would assume that
you expect the IDENTITY values to respect some sort of order that you
want the data to be presented in. Don't count on that.

The SELECT INTO with the IDENTITY() function suggested by David is a
somewhat better bet if the amount of data is small, and you use an
ORDER BY clause. But you are not guaranteed to get rows in order.

CREATE TABLE followed by INSERT is safer, not the least if you add
OPTION (MAXDOP 1) to avoid surprises with parallelism.

Of course, since you don't know exactly what the table would look
like, CREATE TABLE is kind of difficult, but a SELECT INTO with
an IDENTITY() and a WHERE condition of 1 = 0 could cut it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Aug 10 '05 #7
,IDENTITY(INT, 1,1) AS TIId --add numbering
Into #TempPaging

Ok, I changed my code to have this as the line(s) that I can copy and
paste into procs and then just put in the paging proc lines below that

--do paging based on temp table
exec spDoPaging @itemsPerPage, @Page, @TotalPages OUTPUT,
@TotalRecords OUTPUT

Which seems to work pretty well. Thanks for the help guys.

Aug 11 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
16293
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the equivalent to the SQL 'with' using TSQL? If there is not one, what is the TSQL solution to creating a temporary table that is associated with an SQL statement? Examples would be appreciated. Thank you!!
5
11112
by: ahokdac-sql | last post by:
Hi, I'm adapting access queries to sql server and I have difficulties with the following pattern : query1 : SELECT * FROM Query2 WHERE A=@param1 query 2: SELECT * FROM Table2 WHERE B=@param2 The queries are nested, and they both use parameters. In MS Acccess the management of nested queries with parameters is so easy (implicit declaration of parameters, transmission of parameters from main query to nested query)
4
10673
by: deko | last post by:
I'm trying to delete a table with db.execute DROP TABLE tempTable I've tried first clearing the RecordSource of the forms that use tempTable like this: Me!RecordSource = "" But it does not seem to help. I still get this error:
3
2002
by: roy_ware | last post by:
I'm using a VB.Net interface to load an Access table. The relationship is 1 input record to many table rows. The problem is that the first name of the first row is populated for every row on the table (which curiously has the correct total number of rows for the input file!). The reader does read the data correctly. If I put all the input record data into one row, it works perfectly. Here's the partial code:
6
2353
by: A_M_IS | last post by:
Hello group, hope to anybodys help on my temporary blackout. (Using Access 2003 on XP Win.) I know how to create and edit temporary query recordset, then I can set this data source as my form recordsource. Until there all this work for me at a glance. Problem case now! For another reason I need to store my selected datas from this datasource in another new table, can we name it as tempTable.
11
4095
by: raylopez99 | last post by:
Keep in mind this is my first compiled SQL program Stored Procedure (SP), copied from a book by Frasier Visual C++.NET in Visual Studio 2005 (Chap12). So far, so theory, except for one bug (feature?) below. At some point I'm sure I'll be able to laugh about this, akin to forgeting a semi-colon in C/C++, but right now it's frustrating (time to sleep on it for a while). Problem-- For some reason I get the error when trying to save files...
1
1747
by: MLH | last post by:
I have a form used as a subform on frmCreateInvoice. It is displayed in datasheet view in the subform control. It displays a few text fields, a boolean field (shown as a checkbox control) and a numeric field. The subform is fed by a query. When I open the main form, I often click the checkbox control in the records listed in the subform control, changing them from False to True. And in the numeric field, I may change the displayed...
0
6097
by: Romulo NF | last post by:
Greetings again everyone Recently i´ve been asked to develop a script to allow filtering in the content of the table, with dinamic options based on the own content. Example: a table with the name of some students and their respective numbers, and then you wanna show only studentes called "Joao", or students with number "5", or even only students called "joao" with number "5". The structure we are going to use is a basic html table, like: ...
22
6630
by: DreamersDelight | last post by:
Hi, I'm stuck on this problem and I can't find a sollution. I'm going to try and explain this step by step. 1 After certain rows get updated with a certain value. I don't know wich rows in advance. And they do not have a certain order, but after the update they all have the same trans_id. (in unload_details_tab). 2 I select select the rows using the value that just got updated (trans_id), this way I know wich rows I needed to select, the...
0
9120
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8999
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8858
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7670
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6496
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5841
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4348
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
1980
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.