error whilealtering temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error whilealtering temporary table

Author

Topic

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 03:57:21

please find the error in following code :

create table #tblData (ID int,RowName varchar(200))
alter table #tblData add col1 varchar(100)
insert into #tblData values(1,'rr','34')
select * from #tblData
drop table #tblData

thx

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 04:19:19

create table #tblData (ID int,RowName varchar(200))
alter table #tblData add col1 varchar(100)
GO
insert into #tblData values(1,'rr','34')
select * from #tblData
drop table #tblData

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 04:47:41

Thanks error resolved but i want to use this in procedure

create proc tempPro
as
create table #tblData (ID int,RowName varchar(200))
alter table #tblData add col1 varchar(100)
GO
insert into #tblData values(1,'rr','34')
alter table #tblData add col2 varchar(100)
GO
insert into #tblData values(1,'rr','34','dr')
select * from #tblData
drop table #tblData

thx

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-20 : 04:59:22

don't understand why you need to alter the table, why don't you just create the table with all columns and insert the data one time?

--------------------
keeping it simple...

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 05:07:58

This is an example
Acutlly i want convert my data row to column wise.

data
1 a
2 b
3 c
4 d

out put
1 2 3 4
a b c d

thx

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 05:10:17

I had a feeling that would be the next question
You cant use GO in a stored procedure apart from at the very end of your executeable code

I agree with jen. What exactly are you trying to do??

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 05:27:42

This is an example
Acutlly i want convert my data row to column wise.

data
1 a
2 b
3 c
4 d

out put
1 2 3 4
a b c d

thx

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 06:46:34

This pivot table article by robvolk may help you
[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 07:04:44

thanks AndyB13

But can i solve this problem. My functionality is complex this pivot table can not solve my problem.
Please try to solve my this error

------------------
create proc tempPro
as
create table #tblData (ID int,RowName varchar(200))
alter table #tblData add col1 varchar(100)
insert into #tblData values(1,'rr','34')
alter table #tblData add col2 varchar(100)
insert into #tblData values(1,'rr','34','dr')
select * from #tblData
drop table #tblData
------------------

Thanks & Regards

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 07:21:30

Your SP doesnt make sense compared to your example, if the above code was to work then the results would look like this

ID RowName col1 col2
--------------------
1 rr 34 NULL
1 rr 34 dr

From your example the following would give you the result in a single column

CREATE TABLE #MyTemp (Col1 int, Col2 varchar(1))

INSERT #MyTemp VALUES (1,'a')
INSERT #MyTemp VALUES (2,'b')
INSERT #MyTemp VALUES (3,'c')
INSERT #MyTemp VALUES (4,'d')

DECLARE @Row1 varchar(100),@Row2 varchar(100)

SELECT @Row1 = COALESCE(@Row1 + ' ', '') + CONVERT(varchar(1),Col1)
FROM #MyTemp

SELECT @Row2 = COALESCE(@Row2 + ' ', '') + CONVERT(varchar(1),Col2)
FROM #MyTemp

SELECT @Row1 AS [Output]
UNION
SELECT @Row2

DROP TABLE #MyTemp

You need to be more specific, post some sample data & table structue, your exact code, expected results. Otherwise it is difficult for people to help without a certain amount of guess work

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-20 : 07:38:24

quote:
My functionality is complex this pivot table can not solve my problem.

Why not? Are you cross-tabbing or transposing the table? (they're not the same thing)

When using a stored procedure, you cannot reference columns that were added via ALTER TABLE. The query processor flags it as an error. The only workaround is to use dynamic SQL.

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 07:49:31

Thanks all

Please remove this error. Only this logic fulfil my functionality. I can't use any idea. Because there are many other reason also.

------------------
create proc tempPro
as
create table #tblData (ID int,RowName varchar(200))
alter table #tblData add col1 varchar(100)
insert into #tblData values(1,'rr','34')
alter table #tblData add col2 varchar(100)
insert into #tblData values(1,'rr','34','dr')
select * from #tblData
drop table #tblData
------------------

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-20 : 07:52:21

Do you mind explaining them? The more information you give us about WHAT you're trying to do, the better we can help, otherwise it's impossible to provide a solution.

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-20 : 07:58:54

Thanks robvolk

robvolk say this is impossible.

Please anybody can explain me why this error occur

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 08:06:48

Rob didnt say it was impossible, he said it wouldnt be possible to help you without knowing what EXACTLY you are trying to do

The error occurs because when the SQL is compiled the column(s) dont exist for the insert - something like 2 into 1 doesnt go

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-20 : 09:15:27

will this do?


create proc tempPro
as
create table #tblData (ID int,RowName varchar(200))
exec('alter table #tblData add col1 varchar(100)')
insert into #tblData values(1,'rr','34')
exec('alter table #tblData add col2 varchar(100)')
insert into #tblData values(1,'rr','34','dr')
select * from #tblData
drop table #tblData

if that doesnt work for you replace #tblData with ##tblData. that means that ##tblData becomes a global temp table.

and i do must say that this is weird thing you want...

Go with the flow & have fun! Else fight the flow

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-21 : 00:37:38

Sorry spirit1 i need local table.

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-21 : 02:54:47

it's difficult to help you if you're not willing to provide information

can you just describe, what do you want to do (forget about your logic and not being open to new ideas)?

--------------------
keeping it simple...

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-21 : 03:10:37

Jen i just want to solve this problem. I can not change my logic. Because my logic is very crystal clear. Just want to why this error occur.

Other wise i have many other soluntions to fulfill my functionality.
But i want know why this logic fail.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-21 : 03:45:21

This is the reason why it fails. From BOL

quote:

SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at run time because referenced objects do not exist

Is this your required output
ID RowName col1 col2
--------------------
1 rr 34 NULL
1 rr 34 dr

Kulwinder
Starting Member

18 Posts

Posted - 2005-01-21 : 04:05:53

Yes AndyB13 this is my output.

Thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources