CODEWITHSUNDEEP
sqlsql-servert-sql
Sabareeshwari Kannan
Sabareeshwari Kannan

Reputation: 75

How to pass column name dynamically for the temp table in sql

INSERT #temptable(Sno,emp_code,emp_id,d1,d2,d3,d4,d5) EXEC sp_executesql @lsql1;

In the Above code d1,d2,d3,d4,d5 are the days in the month.. I have to pass this according to the parameters of from date and to date for a particular month..

Is there any way to sort out this?

Now when i attempt to insert more than a specified column it shows me a error

Column name or number of supplied values does not match table definition.

 ALTER PROCEDURE [dbo].[Attendance_LateComing_Report1] @fdate1 varchar(20),@tdate1 varchar(20),@cmp_code varchar(20) 
AS
declare @ls varchar(max),@lsql1 nvarchar(max),@lsql2 nvarchar(max),@ls2 varchar(200)
Create table #temptable(Sno int,emp_code varchar(10),emp_id varchar(20),d1 nvarchar(10),d2 nvarchar(10),d3 nvarchar(10),d4 nvarchar(10),d5 nvarchar(10),d6 nvarchar(10),d7 nvarchar(10),d8 nvarchar(10),d9 nvarchar(10),d10 nvarchar(10),d11 nvarchar(10),d12 nvarchar(10),d13 nvarchar(10),d14 nvarchar(10),d15 nvarchar(10),d16 nvarchar(10),d17 nvarchar(10),d18 nvarchar(10),d19 nvarchar(10),d20 nvarchar(10),d21 nvarchar(10),d22 nvarchar(10),d23 nvarchar(10),d24 nvarchar(10),d25 nvarchar(10),d26 nvarchar(10),d27 nvarchar(10),d28 nvarchar(10),d29 nvarchar(10) default '',d30 nvarchar(10) default '',d31 nvarchar(10)default '' )

set  @ls = dbo.get_date(@fdate1,@tdate1)
print @ls

select @ls = dbo.get_date(@fdate1,@tdate1)
set @lsql1=N'select ROW_NUMBER() over (order by Emp_Code) SNo,Emp_Code,Emp_id,'+isnull(@ls,'')+' from (
select Emp_Code,A.Emp_id,AttnDate,isnull(sum(DateDiff(MI,Start_Time2,LunchIn)),0) as Late_L
    from Hr_Master_Attendance A 
    inner join HR_Emp_Main M on A.Emp_id=M.Emp_id 
    inner join Hr_Master_Shift S on M.Company=S.Company and A.Shift=S.Shift_Code 
    where AttnDate between Convert(datetime,'''+@fdate1+''') and Convert(datetime,'''+@tdate1+''') 
    group by M.Emp_Code,A.Emp_id,AttnDate ) as a 
    pivot(sum(Late_L) for AttnDate in ('+ @ls + '))  as b'


print @lsql1
--exec (@lsql1)    
print 'Insertion Table'
set @ls2= 'd1,d2,d3,d4,d5,d6'   

set @lsql2='INSERT #temptable(Sno,emp_code,emp_id,'+@ls2+') EXEC sp_executesql '+@lsql1
exec (@lsql2)
select Sno,emp_code,emp_id,isnull(d1,0)as d1,isnull(d2,0) as d2,isnull(d3,0)as d3,isnull(d4,0) as d4,isnull(d5,0) as d5,isnull(d6,0) as d6,isnull(d7,0) as d7,
isnull(d8,0)as d8,isnull(d9,0)as d9,isnull(d10,0)as d10,isnull(d11,0)as d11,isnull(d12,0)as d12,isnull(d13,0)as d13,isnull(d14,0)as d14,isnull(d15,0)as d15,
isnull(d16,0)as d16,isnull(d17,0)as d17,isnull(d18,0)as d18,isnull(d19,0)as d19,isnull(d20,0)as d20,isnull(d21,0)as d21,isnull(d22,0)as d22,isnull(d23,0)as d23,
isnull(d24,0)as d24,isnull(d25,0) as d25,isnull(d26,0)as d26,isnull(d27,0)as d27,isnull(d28,0)as d28,isnull(d29,0)as d29,isnull(d30,0)as d30,isnull(d31,0) as d31 from #temptable

-- exec [Attendance_LateComing_Report1] '2017-02-01','2017-02-06','aee'

Upvotes: 0

Views: 366

Answers (1)

hassan naeimi
hassan naeimi

Reputation: 119

to use dynamic parameter to insert data in SQL Server can uses code like below

    declare @param varchar(max)
    declare @lsql1 varchar(max)
    declare @sql varchar(max)

    set @param=',d1,d2,d3,d4,d5'
    set @sql='INSERT into #temptable(Sno,emp_code,emp_id'+@param+') EXEC sp_executesql '+@lsql1
    exec (@sql)

Upvotes: 1

Related Questions