Temp table and sql select - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Temp table and sql select

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-07 : 15:47:40

hi,
I have a table #result in my procedure which has the result sets to which the procedure inserts values thro explicit insert statements. I have few select statements like
select emp=@emp_id for printing the values
for debugging in the Procedure.

Before i introduced the temp table these statements worked fine.
Now i get the error 'Column name or number of supplied values does not match table definition' I know this is the problem but I dont understand why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-07 : 16:44:15

Show us the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-07 : 19:14:27

Deleted since not reated to the problem being discussed

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 09:44:01

Any helpers? thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:48:38

i dont think error is in posted query as i cant see any INSERT statements in it. i guess its happening inside UP_Insert_Result_Values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 10:32:55

i debugged it it enters in to the DEBUG=1 block
IF @DEBUG=1
BEGIN
SELECT vctrim=@vc_trimid,year=@year,vmpwrebate=@vmp_w_rebate,destination=@destination,actAnnMil=@actual_annual_mileage,
state=@state,fuelinflation=@fuel_inflation,citypercent=@city_percent
and after the first select statement the control flows to the catch.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:38:01

what are you trying to do in above select? are you trying to populate variables with value from table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 10:44:32

no just print the values of the variables to the output ie the values i read from the XML to check it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 10:48:22

nope this wont generate the error you posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 10:57:03

now i ran here in sql server 2005 it says
A severe error occurred on the current command. The results, if any, should be discarded. I cant debug in this installation just ran it.

The earlier debug was with sql server 2008.

If i set the debug=0 it runs good and i get my result as expected.

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 16:05:56

ok ... what is a temp table?
i have two tables one with name #Results and one Results in my code. I think this is causing some conflict.
When i have both the table names as #Results and Results it works. but when i change the db table name to #Results_Table i get 'Invalid object name 'Results'.

I need to pass a result set back from the procedure, so i created #results and passed the final select from #results.
I use [Results] in the db in the UP_Insert_result_values for some other purpose i can even get rid of it.

any ideas on what is causing the problem...

CSears
Starting Member

39 Posts

Posted - 2010-03-08 : 16:44:11

quote:
Originally posted by AAAV

i debugged it it enters in to the DEBUG=1 block
IF @DEBUG=1
BEGIN
SELECT vctrim=@vc_trimid,year=@year,vmpwrebate=@vmp_w_rebate,destination=@destination,actAnnMil=@actual_annual_mileage,
state=@state,fuelinflation=@fuel_inflation,citypercent=@city_percent
and after the first select statement the control flows to the catch.

Maybe I don't understand the posting structure of this forum, but isn't this select statement missing something?

Should be:
SELECT * FROM #result WHERE vctrim=@vc_trimid,year=@year,vmpwrebate=@vmp_w_rebate,destination=@destination,actAnnMil=@actual_annual_mileage,
state=@state,fuelinflation=@fuel_inflation,citypercent=@city_percent

There are a few other possibilities, but I am fairly certain that is not a valid select statement. Can we see the full select statement please? Or if that is it I can tell you what the problem is right now as long as there isn't some quick hand way of selecting data from temp tables that I am not aware of.

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-08 : 21:22:41

CSears... that is a valid statement...
try
Declare @EmpName varchar(25)
select @Empname='Test Name'
select Employee=@EmpName

This is one form of printing... if you issue the print statement we have to typecast and concatenate or give separate print statements for each variable. with select you could print more than one in one statement.

I am asking about the table with # prefix in the definition

CSears
Starting Member

39 Posts

Posted - 2010-03-08 : 23:16:13

Never used the Second select statement format before. Useful.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-09 : 00:42:45

We really can't help you unless you post the full code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources