Post
Ask Question

Get SQL Query With Already Passed Parameter Value By Executing Stored Procedure

Step 1

Create one table named Employee.
 
The table script is given below.
  1. CREATE TABLE [dbo].[Employee](  
  2.     [Name] [nvarchar](50) NULL,  
  3.     [Address] [nvarchar](50) NULL,  
  4.     [Age] [int] NULL,  
  5.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  6.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [ID] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  10. ) ON [PRIMARY]  
  11.   
  12. GO 
Step 2

I entered some dummy data. 
  1. select * from Employee 
Output
 
 
 
Step 3

Create one stored procedure named Sp_EmployeeFullQuery. 
  1. Create procedure Sp_EmployeeFullQuery  
  2. (  
  3.  @ID VARCHAR(200)  
  4. )  
  5. as   
  6. begin  
  7. DECLARE @FullQuery  NVARCHAR(MAX)  
  8. set nocount on;  
  9. SET @FullQuery='select * from Employee where Employee.ID=' + CAST(@ID AS VARCHAR) + ''  
  10. end  
  11. PRINT @FullQuery  
  12. EXEC (@FullQuery) 
In this procedure, I defined one parameter called @ID.

Declare one variable @FullQuery.
  1. DECLARE @FullQuery  NVARCHAR(MAX) 
Now, put all SQL query inside @FullQuery variable.
  1. SET @FullQuery='select * from Employee where Employee.ID=' + CAST(@ID AS VARCHAR) + '' 
At last, whatever parameter value is passed, this will show through @FullQuery variable by using last two parts.
  1. PRINT @FullQuery  
  2. EXEC (@FullQuery) 
Output
  1. exec Sp_EmployeeFullQuery  1  
Here, 1 means AutoGenerate ID column value, which will show the related column data by filtering this ID value.
 
 
 
You will get all the details of SQL query with already passed parameter value.
 
 
 
Summary

Create procedure.

Using primary key, autogenerate id column value showing the records.

Also, generate SQL query, which is defined in stored procedure by passing parameter value.