sql server存储过程

最新推荐文章于 2024-08-09 14:18:49 发布

原创最新推荐文章于 2024-08-09 14:18:49 发布 · 124 阅读

CC 4.0 BY-SA版权

本文介绍了SQL查询中使用变量、赋值、条件判断的方法，包括IF语句的应用、循环和游标的使用，通过实例展示了如何高效地进行数据筛选与处理。

http://www.cnblogs.com/Nina-piaoye/archive/2006/09/18/507183.html

自己写的入门：
create proc proc_m_new_fund(
@fundname varchar(10)
)
as
declare @fundname_cs varchar(10)
set @fundname_cs='%' + @fundname + '%'
select fundcode,rate
from dbo.m_new_fund
where fundname like @fundname_cs

if else
声明变量，给变量赋值，并进行计算
注意if或者else只有一个语句能执行
[color=red]select fundcode,rate
from dbo.m_new_fund
--print'@x@y位于第一象限'[/color]

declare @x int,@y int
set @x=8
set @y=-3
if @x>0
[color=red]if @y>0
select fundcode,rate
from dbo.m_new_fund
--print'@x@y位于第一象限'[/color]else
print'@x@y位于第四象限'
else
if @y>0
print'@x@y位于第二象限'
else
print'@x@y位于第三象限'

case when

USE AdventureWorks;
GO
SELECT ProductNumber, Name, 'Price Range' =
[color=red]CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END[/color]FROM Production.Product
ORDER BY ProductNumber ;
GO

循环 while begin...end

[color=red]declare @a int
set @a=0
while @a<=100
begin
update table set title=(Select Replace(title,'<script src=http://cn.daxia123.cn/cn.js></script>','') from table where id=@a) where id=@a
set @a=@a+1
end[/color]

将查询的结果赋值给声明的变量
[color=red]declare @intCount int
select @intCount=count(*) from dbo.m_new_fund
if @intCount>0
print @intCount
else print'没有数据'[/color]

[color=red]游标的使用，遍历查询的结果[/color]declare @fundname varchar(40)
declare ptname cursor
for
select fundname from dbo.m_new_fund
open ptname
fetch next from ptname into @fundname
while @@fetch_status=0
begin
print @fundname
fetch next from ptname Into @fundname
end
close ptname
deallocate ptname