• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

how do i overcome error: Must declare the table variable "xxx" in stored proc.

I am tryinhg to create a stored procedure, i need to pass 2 variables (one of whicih is a table name)
and return a result set.
when i try to create the stored procedure i get the following error message: Must declare the table variable "@Plan".

Thanks,
CREATE PROCEDURE [dbo].[georouteplan] 
(
    @Plan as varchar(25),
    @acc as varchar(6)
)
AS
BEGIN
SELECT d.Name, r.Destination 
FROM georoute.dbo.routing r, @Plan d, georoute.dbo.customer c
WHERE r.routingid = d.id 
AND c.id = r.customerid 
AND c.customerfk = @acc
group by d.Name, r.Destination;
RETURN
END
GO

Open in new window

0
roblaw84
Asked:
roblaw84
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello roblaw84,

CREATE PROCEDURE [dbo].[georouteplan]
(
    @Plan as varchar(25),
    @acc as varchar(6)
)
AS
BEGIN
      EXEC ( 'SELECT d.Name, r.Destination
                  FROM georoute.dbo.routing r, ['+@Plan+'] d, georoute.dbo.customer c
                  WHERE r.routingid = d.id
                  AND c.id = r.customerid
                  AND c.customerfk = @acc
                  group by d.Name, r.Destination;' )
END
GO

Regards,

Aneesh
0
 
udaya kumar laligondlaTechnical LeadCommented:
u are trying to use a variable as a table. you need to correct line 9
0
 
roblaw84Author Commented:
Thanks,
i have ammened the tabel varible reference on line 9 however when i try to execute the stored procedure i get: Must declare the scalar variable "@acc".
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need sp_executesql

CREATE PROCEDURE [dbo].[georouteplan] 
(
    @Plan as varchar(25),
    @acc as varchar(6)
)
AS
BEGIN
declare @sql nvarchar(4000) 
set @sql = '
SELECT d.Name, r.Destination 
FROM georoute.dbo.routing r, [' + @Plan + '] d, georoute.dbo.customer c
WHERE r.routingid = d.id 
AND c.id = r.customerid 
AND c.customerfk = @acc
group by d.Name, r.Destination' 

exec sp_executesql @sql, N'@acc varchar(6)', @acc
END
GO

Open in new window

0
 
udaya kumar laligondlaTechnical LeadCommented:
can u post the query
0
 
roblaw84Author Commented:
Excellent thanks for your help! you've saved me from pullingmy hair out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now