Must declare the scalar variable @permfkey

I have a stored procedure, when I execute it it shows me this error "Must declare the scalar variable @permfkey"

The SP is :
@permFkey int
as
select username, hasread, haswite, hasdelete, readperm form tblpermper user
where username=user and perm_Fkey=@permfkey
LVL 6
YadtrtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
@permFkey int

should be

declare @permFkey int

Lee
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Your first line is missing (create procedure), please repost the complete code.
0
YadtrtAuthor Commented:
Qlemo: I know I just copied the code

lsavidge:
I have used declare @permFkey int
it shows me this error

Msg 156, Level 15, State 1, Procedure sp_Perm, Line 3
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Procedure sp_Perm, Line 4
Incorrect syntax near the keyword 'as'.
Msg 137, Level 15, State 2, Procedure sp_Perm, Line 7
Must declare the scalar variable "@permFkey"

The procedure is as following

ALTER procedure [dbo].[sp_Perm]
declare @PermFkey int
as

select userName, HasRead, HasWrite, hasDelete, ReadPerm from tblPermperUser
Where UserName=user and perm_Fkey = @permFkey
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you remove the declare keyword, which should be your initial version, it works for me.

ALTER procedure [dbo].[sp_Perm]
   @PermFkey int
as
select  userName, HasRead, HasWrite, hasDelete, ReadPerm from tblPermperUser
Where  UserName=user and perm_Fkey = @permFkey


0
Lee SavidgeCommented:
form tblpermper

should be

 from tblpermper

Lee
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
lsavidge,

that's already corrected in the second author post (with the complete, but changed, command).
0
YadtrtAuthor Commented:
still getting same error
0
Rajkumar GsSoftware EngineerCommented:
This is wrong
ALTER procedure [dbo].[sp_Perm]
declare @PermFkey int
as

To receive a parameter, it should be
ALTER procedure [dbo].[sp_Perm]
 @PermFkey int
as

If no way, try this

DROP PROCEDURE [dbo].[sp_Perm]

CREATE procedure [dbo].[sp_Perm]
(
   @PermFkey int
)
as
BEGIN
select  userName, HasRead, HasWrite, hasDelete, ReadPerm from tblPermperUser
Where  UserName=user and perm_Fkey = @permFkey
END

Raj
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
Eventhough may not be cause of error, verify whether this is correct.

...where username=user ...

Raj
0
YadtrtAuthor Commented:
it works thanks
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Which one of the alternative notations worked?
0
Rajkumar GsSoftware EngineerCommented:
Is there a field in the table 'tblPermperUser' named 'user' ??

If not, I think you want to check a particular user having particular permission, then you need to modify your query with Stored Procedure like

CREATE procedure [dbo].[sp_Perm]
(
   @user varchAR(50),
   @PermFkey int
)
as
BEGIN
select  userName, HasRead, HasWrite, hasDelete, ReadPerm from tblPermperUser
Where  
UserName=@user and
 perm_Fkey = @permFkey
END
GO

Raj
0
YadtrtAuthor Commented:
user in interneal SQL function for getting the current user
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.