Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
SQL
USE [OJTTEST]
GO
/****** Object:  StoredProcedure [dbo].[sp_getUserAccess]    Script Date: 10/14/2013 15:20:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Anonymous
-- Create date: 10/10/2013
-- Description:	get sp for user acess
-- =============================================
ALTER PROCEDURE [dbo].[sp_getUserAccess]
	(@UserName varchar (25),
	@Password varchar (25))
AS
BEGIN
	SET NOCOUNT ON;
	SELECT AccessWord 
	FROM [OJTTEST].[dbo].[UserAccess]
	DECLARE @return int
		IF @UserName=UserName AND @Password=AccessWord
			@return = 1
		ELSE 
			@return = 0
END


I want to make a stored procedure where it will return 1 if there is an access and 0 if not. But there was this error
"Msg 102, Level 15, State 1, Procedure sp_getUserAccess, Line 16
Incorrect syntax near '@return'."

I am using Microsoft SQL Server 2008 R2. It is my first time creating an SP.
Posted

Hey there,

You missed SET keyword before @return =, try:
SQL
Set @return = 1
and
SQL
Set @return = 2


Let me know if it helps.

Note: If you want to return @return from the stored procedure, you either need to use it as an OUTPUT parameter or Select it at the end of the stored procedure. here, this link shows both methods:
http://www.4guysfromrolla.com/articles/062905-1.aspx[^]

Azee...
 
Share this answer
 
v2
Comments
thatraja 14-Oct-13 3:51am    
5!
Modify Your Store Procedure as follow. The Following piece of code will help you
SQL
create PROCEDURE [dbo].[sp_getUserAccess]
(
	@UserName varchar (25),
	@Password varchar (25)
)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @return int
		IF exists(select UserName,AccessWord from UserAccess where UserName=@UserName AND AccessWord =@Password)
            set @return = 1
        ELSE
            set @return = 0
END
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900