How to Create a Stored Procedure with Parameter and Variables in SQL Server

Creating a Stored Procedure with Parameter and Variables in SQL Server
USE [TAMATAM]
GO

IF EXISTS(SELECT * FROM SYS.PROCEDURES 
WHERE Name = 'SP_Emp_ByDept_Details')
BEGIN
DROP PROC  [SP_Emp_ByDept_Details]
END
GO

CREATE PROCEDURE [dbo].[SP_Emp_ByDept_Details]                           
( 
--Parameter Declaration                           
 @Dept1 as varchar(255) = NULL                    
)                          
AS   
--Variables Declaration  
DECLARE @Get_Dept AS varchar(255)=NULL,
@SQL_Stmnt AS varchar(255)=NULL 

BEGIN              
TRUNCATE TABLE Emp_Dtls_ByDept
--Defining Variable with Where Cluase statement by passing Parameter as value
SET @Get_Dept =  'WHERE [Dept_Name] ='''+ @Dept1 + ''''
Print ( @Get_Dept )

SET @SQL_Stmnt = 'INSERT INTO Emp_Dtls_ByDept 
                        SELECT * FROM EMP_Details ' + @Get_Dept
                      Print ( @SQL_Stmnt )
                      EXEC ( @SQL_Stmnt)

SELECT*FROM Emp_Dtls_ByDept
END

GO

/*
DECLARE @RC int
DECLARE @Dept1 varchar(255)

SET @Dept1='Business Intelligence'
EXECUTE @RC = [dbo].[SP_Emp_ByDept_Details]  @Dept1
GO
*/

--Executing the Stored Procedure - Method-I
EXECUTE [SP_Emp_ByDept_Details] @Dept1='Business Intelligence'
--Executing the Stored Procedure - Method-II
EXEC [SP_Emp_ByDept_Details] 'Business Intelligence'

SELECT * FROM Emp_Dtls_ByDept 

GO

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------