passing variable to stored procedure

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)

passing variable to stored procedure

Author

Topic

ly
Starting Member

6 Posts

Posted - 2003-09-20 : 02:58:24

Hi,
In my stored procedure, I pass a variable @tableChosen. If @tableName='a',it will point to a temp table #summary1. Else, it points to #summary2. Here is my code:

create proc calculateSum @tableChosen varchar (1)
as
create table #summary1
(..)

create table #summary2
(..)
insert #summary1...
insert #summary2..
if @tableChosen='a'
begin
set @tableChosen=#summary1
end
if @tableChosen='b'
begin
set @tableChosen=#summary2
end

create table #final(..)
select @t20 = (select unit_count from@tableChosen where @country = country and @port = port and type = '20' )
insert #final

I got a syntax error: "invalid column name #summary1, #summary2, country, port, type".

Can anyone tell me what's wrong? how can I pass a variable to point to a table?

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-20 : 09:02:52

I have a feeling you are trying to make this more difficult than it is. Are you trying to return a recordset depending on the paramater chosen. if it's 'a' you select records from #summary1 and if it's 'b' you select from #summary2

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-20 : 18:04:28

What's the point of this? The temp tables aren't available outside the SP so the name shouldn't matter.
Why not have single temp table to encompass the fields needed in both then the tablename can be hard coded.

To do what you have here you need:

create table #final(..)
declare @sql nvarchar(1000)
select @sql = 'select @t20 = unit_count from ' + @tableChosen + where country = ''' + @country + ''' and posrt = ''' + @port + ''' and type = ''20'''
exec sp_executesql @sql, N'@t20 int out', @t20 out

insert #final ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources