ALTER PROC [dbo].[Usp_Coverage_Report](@Ttl_Days INT,@dates DATETIME,@flg INT) as
DECLARE @i INT;
DECLARE @sql VARCHAR(8000);
DECLARE @ID INT;
DECLARE @columnname VARCHAR(50)
SET @i=1;
IF(@flg=0)
BEGIN
WHILE(@i<=@Ttl_Days)
BEGIN
SET @columnname = '[Col_' + CAST(@i AS VARCHAR) +']'
SET @sql = 'ALTER TABLE Report_tbl ADD '+@columnname+' int NULL';
EXEC(@sql)
SET @i=@i+1;
END
END
else
begin
declare cursor1 cursor for
SELECT id FROM Report_tbl
open cursor1
Fetch Next from cursor1 into @id
while @@FETCH_STATUS=0
begin
SET @i=1;
WHILE(@i<=@Ttl_Days)
BEGIN
SET @columnname = '[Col_' + CAST(@i AS VARCHAR) +']'
SET @sql='UPDATE Report_tbl SET '+@columnname+'=(SELECT count(user_id) as ttlout FROM tbl_MobileReporting WHERE Format(visit_date,''yyyy/MM/dd'')=Replace(convert(Date,'''+@dates+''',103),''-'',''/'') and user_id=CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12),'+ @ID+')) = 1 then CONVERT(VARCHAR(12), '+@ID+') else 0 End)) where id='+@ID;
EXEC(@sql)
SET @i=@i+1;
end
FETCH NEXT FROM cursor1 INTO @id
end
CLOSE cursor1
DEALLOCATE cursor1
end
GO
Loading
Abdul Amin KhanPosted Nov 30, 2015, 1:15 AM
Jignesh TrivediPosted Nov 26, 2015, 11:29 PM
Raja TPosted Nov 26, 2015, 11:24 PM
SELECT CAST('2008-08-08 11:11:11.6730000' AS DATETIME) -- conversion error
SELECT CAST(CAST('2008-08-08 11:11:11.6730000' AS DATETIME2) AS DATETIME) --regular converting
If implicit conversion to DATETIME2 should be supported.