Error: Must declare the scalar variable

[saravana784]

USE [CP_DOTNET]
GO
/****** Object: StoredProcedure [dbo].[T_sp_SetScreenPermission] Script Date: 06/09/2007 20:15:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[T_sp_SetScreenPermission]

@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_PERMISSIONS')
Begin
If exists (select m1.ROLE_ID,m1.USER_ID,m2.DEFAULT_PERMISSION_ID,m2. SCREEN_ID,m2.PERMISSION_ID,m2.IS_ACTIVE
from M_USER m1,M_DEFAULT_SCREEN_PERMISSIONS m2 where m2.IS_ACTIVE='1')SET @RECEXIST = 0

ELSE
Begin
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'T_LOG_FOR_USERS')
Begin
Begin Tran
INSERT INTO T_SET_SCREEN_PERMISSIONS(SET_SCREEN_PERMISSION_ID, USER_ID, SCREEN_ID, PERMISSION_ID,CREATED_ON, CREATED_BY,IS_ACTIVE )

VALUES (@Set_Screen_Permission_Id,@User_Id, @Screen_Id,@Permission_Id,@Created_On, @Created_By,'1' )
DECLARE @Set_Screen_Permission_Id int
set @Set_Screen_Permission_Id = @@identity
Insert into T_LOG_FOR_USERS(TR_DATE,USER_ID,USER_IP_ADDRESS,TR ANSACTION_NAME,TRANSACTION_TABLE_NAME,TRANSACTION_ KEY_FIELD_NAME,TRANSACTION_KEY_FIELD_VALUE,REPORTI NG_FLAG)
Values(@Created_On,@Created_By,@machine_ip,'set Screen Permission Creation','T_SET_SCREEN_PERMISSIONS','SETSCREEN_PE RMISSION_ID',@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_SetScreenPermission, Line 30
Must declare the scalar variable "@Set_Screen_Permission_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.