Error: Must declare the scalar variable

[saravana784]

USE [CP_DOTNET]
GO
/****** Object: StoredProcedure [dbo].[T_sp_SetScreenP ermission] Script Date: 06/09/2007 20:15:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO




CREATE PROCEDURE [dbo].[T_sp_SetScreenP ermission]

@RECEXIST Int output,
@user_Id varchar(10) ,
@Screen_Id varchar(10) ,
@Permission_Id varchar(10) ,
@Created_On Datetime,
@Created_By varchar(50) ,
@machine_ip Varchar(50)


AS
SET NOCOUNT ON;
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'T_SET_SCREEN_P ERMISSIONS')
Begin
If exists (select m1.ROLE_ID,m1.U SER_ID,m2.DEFAU LT_PERMISSION_I D,m2.SCREEN_ID, m2.PERMISSION_I D,m2.IS_ACTIVE
from M_USER m1,M_DEFAULT_SC REEN_PERMISSION S m2 where m2.IS_ACTIVE='1 ')SET @RECEXIST = 0

ELSE
Begin
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'T_LOG_FOR_USER S')
Begin
Begin Tran
INSERT INTO T_SET_SCREEN_PE RMISSIONS(SET_S CREEN_PERMISSIO N_ID,USER_ID, SCREEN_ID, PERMISSION_ID,C REATED_ON, CREATED_BY,IS_A CTIVE )

VALUES (@Set_Screen_Pe rmission_Id,@Us er_Id, @Screen_Id,@Per mission_Id,@Cre ated_On, @Created_By,'1' )
DECLARE @Set_Screen_Per mission_Id int
set @Set_Screen_Per mission_Id = @@identity
Insert into T_LOG_FOR_USERS (TR_DATE,USER_I D,USER_IP_ADDRE SS,TRANSACTION_ NAME,TRANSACTIO N_TABLE_NAME,TR ANSACTION_KEY_F IELD_NAME,TRANS ACTION_KEY_FIEL D_VALUE,REPORTI NG_FLAG)
Values(@Created _On,@Created_By ,@machine_ip,'s et Screen Permission Creation','T_SE T_SCREEN_PERMIS SIONS','SETSCRE EN_PERMISSION_I D',@Set_Screen_ Permission_Id,' 1')
Commit
SET @RECEXIST = 1
End

Else
Begin
SET @RECEXIST = 3
End
End
End
Else
Begin
SET @RECEXIST = 2
End

Msg 137, Level 15, State 2, Procedure T_sp_SetScreenP ermission, Line 30
Must declare the scalar variable "@Set_Screen_Pe rmission_Id".

[siva538]

what is the problem you want to ask ... you have to declare the variable before you can use . ... the variable is declared after it is used ..

I suggest you try these kind of simple things before posting to the community ...

 DECLARE @Set_Screen_Permission_Id int

should be declared before the insert statement ..

Also as a best practise you should declare all the variables at the beginning of the stored procedure ..

Thanks,
Sivakumar.

[cetpainfotech]

This error in Microsoft SQL Server occurs when you reference a variable that has not been declared. To resolve this error, you need to declare the variable before you use it in your query.
Here's an example of how you can declare a variable in SQL Server:

DECLARE @variable_name data_type
SET @variable_name = value

Replace variable_name with the name you want to give to your variable, and data_type with the appropriate data type (e.g. INT, NVARCHAR, DATETIME, etc.). And value should be replaced with the actual value you want to assign to the variable.

Once you have declared the variable, you can use it in your query just like any other value. For example:
DECLARE @myVariable INT
SET @myVariable = 5
SELECT * FROM myTable WHERE myColumn = @myVariable

In this example, the variable @myVariable is declared as an INT and assigned the value 5. The variable is then used in the WHERE clause of the SELECT statement to filter the results based on the value of myColumn.

By declaring and using variables in this way, you can avoid the "Must declare the scalar variable" error and ensure that your queries run correctly in SQL Server

If you're looking to build your skills in Microsoft SQL , CETPA Infotech is the perfect place to start. With its comprehensive training programs, expert instructors, and hands-on approach to learning, CETPA Infotech is the ideal choice for anyone looking to take their career to the next level in the cloud computing industry.