SQL Server - stored procedure for getting bit value from table column

Asked By raj on 07-Dec-16 08:22 AM
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


Robbe Morris replied to raj on 08-Dec-16 09:57 AM
Your code is a little bit of a mess to follow.  One thing that would help is to not use dynamic sql if you don't have to.  It runs in a different scope than the rest of your procedure.

Not sure you are aware of this but you can select a column value into a variable.

declare @MyVar1 bit

select @MyVar1 = SomebitColumn from tableA where id='blah blah blah'

print CAST(@MyVar1 as varchar(10))