July 11, 2003 at 9:46 am
Is there a restriction on how you use Input Parameters? The following code tells me I have to declare the input variables...
alter proc lp_releasehold
(
@holdname varchar(255),
@loadname varchar(255)
)
as
select * into #temphold from @holdname where release = 1
alter table #temphold drop column sbcid
insert into @loadname select * from #temphold
--delete from @holdname where release = 1
July 11, 2003 at 10:59 am
Although I still don't know why you can't do the above code, the quick fix is to set the sql code to a variable and execute the variable:
i.e.
set @sql = 'select * from ' <plus sign> @holdtable
exec(@sql)
July 11, 2003 at 11:23 am
Here is what you need to do...
DECLARE @sql VARCHAR( 8000 )
SELECT @sql = 'INSERT ' + @loadname +
' SELECT * FROM ' + @holdname +
' WHERE release = 1'
EXECUTE( @sql )
July 14, 2003 at 5:42 am
quote:
Is there a restriction on how you use Input Parameters? The following code tells me I have to declare the input variables...alter proc lp_releasehold
(
@holdname varchar(255),
@loadname varchar(255)
)
as
select * into #temphold from @holdname where release = 1
alter table #temphold drop column sbcid
insert into @loadname select * from #temphold
--delete from @holdname where release = 1
What you are trying to do is called double evaluation and unfortunately SQL Server doesn't handle that. It means that you can't use variables' value as identifier in T-SQL. The only way is to buld and run a dynamic SQL using EXECUTE().
July 14, 2003 at 5:53 am
Or sp_executesql, which has the advantage of reusing query plans.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply