example or sample code of Input variable in stored procedure for beginner. 

Or input variable is used to pass value from an application to a stored procedure. here I will write a stored procedure which takes a variable sortorder and ascending or descending will be passed from application. 


There are two types of variables. local and global. Lets focus on local variables for now. 

-> U can name the variable how every u want, but the requirement is that the variable begins with an @ Symbol. 
@SortOrder 
@FirstName 
@LastName 

Now the example for the same. 

Start with creating a store procedure. 

Open Microsoft SQL Server Management Studio Express


-> Right Click on  Databases->YouDatabase->Programmability->Stored Procedures
-> Select new Stored Procedure
You would get a genearted template 
CREATE PROCEDURE  
	-- Add the parameters for the stored procedure here
	<@param1, sysname,="" @p1="">  = , 
	<@param2, sysname,="" @p2="">  = 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@param1, sysname,="" @p1="">, <@param2, sysname,="" @p2="">
END
GO


Lets Modify it 


CREATE PROCEDURE sp_test2
	-- Add the parameters for the stored procedure here
	@SortField varchar(10) = 'USERNAME' 
AS
DECLARE @SQLGeneratedString varchar(255)
BEGIN
	SELECT @SQLGeneratedString = 'SELECT * FROM USERS  order by ' + @SortField + ' asc'
	EXEC(@SQLGeneratedString)
END
GO

I have create a variable @SQLGeneratedString and used string operations to genearte the required ouput string withe variable
exec executed the geneatated sql string.