Hi All,
I have two tables - dbo.employee, dbo.productorder and 1 stored procedure - dbo.uspSample with 2 input parameters.
Employee Table:
EmpID EmpName SalaryCalc HireDateYear
1 Tom 5000 2015
2 Tom 8000 2015
3 Tom 9000 2016
4 Sam 4000 2016
ProductOrder Table:
ProductID ProductName AmountCalc QuantityCalc
1 Laptop 500 1
2 PC 800 2
3 Laptop 550 2
4 Ipad 700 5
Stored Proc name: dbo.uspSample (@TableName, @ColNames)
Now I need a dynamic t-sql with in the sp that needs to be executed when a @TableName and @ColNames are passed as input parameters and based on input values it should return the following output scenarios.The values to the @ColNames would vary every time
and based on the column names
Note: All the calculated or summation fields are indicated with 'Calc' at the end of the columnnames to identify.
Scenario-1) When @TableName = dbo.employee and @ColNames = 'EmpName, SalaryCalc' (2 columns) are passed as input parameters to the sp, the output should be
Output:
EmpName SalaryCalc
Tom 22000
Sam 4000
Scenario-2) When @TableName = dbo.employee and @ColNames = 'EmpName, SalaryCalc, HireDateYear' (3 columns) are passed as input parameters to the sp, the output should be
Output:
EmpName SalaryCalc HireDateYear
Tom 13000 2015
Tom 9000 2016
Sam 4000 2016
Scenario-3) When @TableName = dbo.productOrder and @ColNames = 'ProductName, AmountCalc' (2 columns) are passed as input parameters to the sp, the output should be
Output:
ProductName AmountCalc
Laptop 1050
PC 800
Ipad 700
Scenario-4) When @TableName = dbo.productorder and @ColNames = 'ProductName, AmountCalc, QuantityCalc' (3 columns) are passed as input parameters to the sp, the output should be
Output:
ProductName AmountCalc QuantityCalc
Laptop 1050 3
PC 800 2
Ipad 700 5
Create Table dbo.Employee (EmpID int null, EmpName varchar(25) null, SalaryCalc money null, HireDateYear int null )
Insert into table dbo.Employee (EmpID, EmpName, SalaryCalc,HireDateYear)
Values
(1 , 'Tom', '5000', 2015 ),
(2 , 'Tom', '8000', 2015 ),
(3 , 'Tom', '9000', 2016 ),
(4 , 'Sam', '4000', 2016 )
Create Table dbo.ProductOrder(ProductID int null, ProductName varchar(50) null , AmountCalc money null, QuantityCalc int null)
Insert into table dbo.productorder (ProductID , ProductName , AmountCalc, QuantityCalc)
Values
(1 , 'Laptop' , '500', 1),
(2 , 'PC' , '800', 2),
(3 , 'Laptop', '550', 2),
(4 , 'Ipad', '700', 5)
Based on above data need Dynamic t-sql query which will be executed through strored proc based on any given table or columns names.
Thanks in advance
RH
sql
