473,883 Members | 1,669 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 2633
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
16303
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
11120
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
10698
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
2014
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
2364
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
4113
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
1755
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
6112
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
6694
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
9940
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10742
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
10847
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,...
1
7971
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
7126
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
5797
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...
1
4611
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4220
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3232
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.