Not able to assign the bit value to declared variable
here is stored procedure.
Objective of this SP is getting the value in 1st monday of one month and update it in 1st monday of next month.
table tblA column defined as (id int, [1] bit,[2] bit..... [31] bit)
table tblB column defined as (id int, [1] bit,[2] bit..... [31] bit)
not able to set the value to local variable and update it in another table.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[auto]
as
begin
declare @dt datetime
declare @fdt datetime
declare @tdt datetime
declare @nfdt datetime
declare @ntdt datetime
declare @adt datetime
declare @res varchar(5)
declare @MyVar bit
declare @myvar1 bit
declare @sql nvarchar(max)
declare @sq2 nvarchar(max)
declare @year int
declare @month int
declare @wkno int
declare @dd varchar(4)
set @fdt = getdate()
set @tdt = getdate()+10
set @year =year(@fdt)
set @month= month(@fdt)
while @fdt <= @tdt
begin
set @dd = datename(dd,@fdt)
set @adt = CAST(CAST(@year AS varchar) + '/' + CAST(@month AS varchar) + '/' + CAST(@dd as varchar) AS datetime)
set @wkno = datepart(wk, @adt)-datepart(wk,dateadd(m, DATEDIFF(M, 0, @adt), 0)) + 1
set @res ='['+convert(varchar,@dd,2)+']'
set @sql = N'select '+@res+' from tblA where id = 100'
EXEC sp_executesql @sql,N'@myvar1 bit OUTPUT',@myvar1 OUTPUT
SELECT @myvar1
set @sq2 = N'insert into tblB (id,'+@res+')values (100,'+@myvar1+') where id=100'
EXEC sp_executesql @sq2
set @myvar = cast(@myvar1 as bit)
set @fdt = @fdt+1
end
end