SQL 学习笔记

 

1.

Declare @table table(val int)

Insert Into @table(val)

Exec(@sql)

 

Select @count = val From @table

 

 

 

 

2.

 

Declare @Table Table

(

focustar_tasksid varchar(40) ,

focustar_tasktitle varchar(400) ,

focustar_taskdesc varchar(500),

focustar_taskstate varchar(10),

createdbyname varchar(100),

createdon varchar(20),

focustar_recordid varchar(40),

focustar_etc varchar(100)

)

 

 

Insert Into @Table

(

focustar_tasksid ,

focustar_tasktitle ,

focustar_taskdesc ,

focustar_taskstate ,

createdbyname ,

createdon ,

focustar_recordid ,

focustar_etc

)

exec(@sql)

Declare @RowCount int

Select @RowCount = Count(1) From @Table

Declare @focustar_tasksid varchar(40) 

Declare @focustar_tasktitle varchar(400) 

Declare @focustar_taskdesc varchar(500)

Declare @focustar_taskstate varchar(10)

Declare @createdbyname varchar(100)

Declare @createdon DateTime

Declare @focustar_recordid varchar(40)

Declare @focustar_etc varchar(100)

 

 

DECLARE product_cursor CURSOR FOR 

SELECT Top 21 *

FROM @Table

 

OPEN product_cursor

FETCH NEXT FROM product_cursor INTO

@focustar_tasksid,

@focustar_tasktitle,

@focustar_taskdesc,

@focustar_taskstate,

@createdbyname,

@createdon,

@focustar_recordid,

@focustar_etc

Declare @Html varchar(max)

Set @Html = ''

 

Declare @index smallint

Set @index = 1

 

WHILE @@FETCH_STATUS = 0

BEGIN

if (@focustar_tasksid Is Null) Set @focustar_tasksid = ' '

if (@focustar_tasktitle Is Null) Set @focustar_tasktitle = ' '

if (@focustar_taskdesc Is Null) Set @focustar_taskdesc = ''

if (@focustar_taskstate Is Null) Set @focustar_taskstate = ' '

if (@createdbyname Is Null) Set @createdbyname = ''

if (@createdon Is Null) Set @createdon = ' '

if (@focustar_recordid Is Null) Set @focustar_recordid = ' '

if (@focustar_etc Is Null) Set @focustar_etc = '0'

If((@index%2.0) = 0)

Set @Html = @Html + '<tr style="background-color:White;border:none;margin:0px;height:20px;font-family:宋体;font-size:12px;">'

Else

Set @Html = @Html + '<tr style="background-color:#EEEEEE;border:none;margin:0px;height:20px;font-family:宋体;font-size:12px;">'

Set @Html = @Html + '<td width='''+@width1+'''  align=''left;border-left:1px solid red;'' οnclick="o(''' + @focustar_recordid +''',' + @focustar_etc + ');"><div style=''width:'+@width1+'px;overflow:hidden;white-space:nowrap; margin-left:10px;cursor: hand;''>'+@focustar_tasktitle+'</div></td>'

Set @Html = @Html + '<td width='''+@width2+''' align=''left;border-left:1px solid red;''><div style="width:' + @width2 + ';overflow:hidden;white-space:nowrap;">' + @focustar_taskdesc + '</div></td>'

Set @Html = @Html + '<td width='''+@width3+''' align=''center;border-left:1px solid red;''><div style="width:' + @width3 + ';overflow:hidden;white-space:nowrap;" >' + @focustar_taskstate + '</div></td>'

Set @Html = @Html + '<td width='''+@width4+''' align=''center;border-left:1px solid red;''><div style="width:' + @width4 + ';overflow:hidden;white-space:nowrap;" >' + @createdbyname + '</div></td>'

Set @Html = @Html + '<td width='''+@width5+''' align=''center;border-left:1px solid red;''><div style="width:' + @width5 + ';overflow:hidden;white-space:nowrap;" >' + Convert(varchar(20),@createdon,120) + '</div></td>'

Set @Html = @Html + '</tr>'

FETCH NEXT FROM product_cursor INTO

@focustar_tasksid,

@focustar_tasktitle,

@focustar_taskdesc,

@focustar_taskstate,

@createdbyname,

@createdon,

@focustar_recordid,

@focustar_etc

Set @index = @index + 1

END

 

CLOSE product_cursor

DEALLOCATE product_cursor

//**********************************************************************************

DECLARE product_cursor CURSOR FOR 
 select * from @Table
 OPEN product_cursor
 FETCH NEXT FROM product_cursor into @y,@initCount,@readCount,@auditCount,@checkCount
WHILE @@FETCH_STATUS = 0
BEGIN
set @count = 0
select @count=COUNT(*) from T_BUSI_ReadMetSchedule where YearMonth=@y
if(@count=0)
BEGIN
insert into T_BUSI_ReadMetSchedule(guid,YearMonth,InitTasksCount,ReadTasksCount,AuditTasksCount,CheckTasksCount) 
values(newid(),@y,@initCount,@readCount,@auditCount,@checkCount)
END
else
BEGIN
update T_BUSI_ReadMetSchedule set 
InitTasksCount=@initCount,
ReadTasksCount=@readCount,
AuditTasksCount=@auditCount,
CheckTasksCount=@checkCount 
where YearMonth=@y
END
FETCH NEXT FROM product_cursor into @y,@initCount,@readCount,@auditCount,@checkCount
END

CLOSE product_cursor
DEALLOCATE product_cursor

 

3.

convert(varchar(100),focustar_importdate,23)

4. sp_executesql 

declare @sql nvarchar(2000)
declare @param nvarchar(500)
declare @avgMeterValue numeric(12,2)


set @sql = N'select @avgMeterValueOut=avg(convert(numeric(12,2),isnull(ThisMeterValue,0))) from T_BUSI_InspectMeterReadRec where 1 > 0 '

set @param = N'@avgMeterValueOut numeric(12,2) OUTPUT';
execute sp_executesql @sql, @param,@avgMeterValueOut=@avgMeterValue OUTPUT;

5.删除列

alter table dbo.Department add DepartState int  not null default 2
alter table Department drop  column DepartState --获取默认约束名称
alter table dbo.Department drop  constraint  DF__Departmen__Depar__4E88ABD4 --DF__Departmen__Depar__4E88ABD4为默认约束名称
alter table Department drop column DepartState