error in stored procedure - 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

error in stored procedure

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-11-05 : 02:41:04

Hi all,

I have this stored procedure and getting error:

CREATE PROC [dbo].[GetColorMapColumns]
(
@columnName VARCHAR(50),
@BCCH int
)
AS
BEGIN

select @columnName
into #temp
from F_CSV_VOICE_09D2_DEV where BCCH = @BCCH
select * from #temp

END

Msg 1038, Level 15, State 5, Procedure GetColorMapColumns, Line 9
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-05 : 02:58:48

Use Dynamic SQL

Exec('select '+@columnName+' into #temp from F_CSV_VOICE_09D2_DEV where BCCH =' +@BCCH )

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-11-05 : 03:14:30

Hi senthil:

Thanks for the fast response. Now i dont get error. stored procedure is created but when i run it, I get error:

(4700 row(s) affected)
Msg 208, Level 16, State 0, Procedure GetColorMapColumns, Line 15
Invalid object name '#temp'.

Here is the stored procedure that i created:

ALTER PROC [dbo].[GetColorMapColumns]
(
@columnName VARCHAR(50),
@BCCH int
)
AS
BEGIN

Exec('select '+@columnName+' into #temp from F_CSV_VOICE_09D2_DEV where BCCH =' +@BCCH )

select * from #temp

END

Regards,
Asif Hameed

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-05 : 03:35:58

The Dynamic SQL treat Select into statement in a Different Connection.

Then the select did't work. To solve it use Global TEmp table ##temp, will solve your problem. But the global temp table will be exist for other connections too.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-05 : 03:45:42

Else Try like this tooo

create table #temp (column_name varchar(50))
exec('insert into #temp select '+ @column_name +' from mytable')
select * from #temp

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources