Declare Input Parms

Question

Declare Input Parms

rachel

SSCommitted

Points: 1626
More actions
July 11, 2003 at 9:46 am

#159510

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rachel SSCommitted Points: 1626 More actions · Answer 1

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)

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 2

Here is what you need to do...

DECLARE @sql VARCHAR( 8000 )

SELECT @sql = 'INSERT ' + @loadname +

' SELECT * FROM ' + @holdname +

' WHERE release = 1'

EXECUTE( @sql )

jcool SSC Eights! Points: 863 More actions · Answer 3

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().

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

Or sp_executesql, which has the advantage of reusing query plans.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
Connect with me on LinkedIn