Declare Variable error - 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

Declare Variable error

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-01-14 : 11:31:20

Can anyone tell me why I keep getting this error? I am declaring the variable, but it's not recognizing it? What am I missing?

------error---------------
Server: Msg 137, Level 15, State 2, Procedure sp_CopyData, Line 85
Must declare the variable '@DatabaseFrom'.

-----sp----

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create Procedure dbo.sp_CopyData
(@ClientAbbrev nchar(4) )
AS
DECLARE @DatabaseFrom varchar(100)

Set @DatabaseFrom = @ClientAbbrev + '.dbo.tsn_ClaimStatus'

--------------------------------------------------------------
delete from sherrisplayground.dbo.tsn_ClaimStatus
where csclientcode = @ClientAbbrev

---Insert Data from Original table into copied table---------
Insert into [AO3\AO3].sherrisplayground.dbo.tsn_ClaimStatus (
CsClientCode,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName)
select
@ClientAbbrev,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName
from @DatabaseFrom

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-14 : 11:33:08

because you can't use a variable for a table name like that.
you have to use dynamic sql for this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 11:33:27

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources