SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
alter procedure procedure_daytjlshtest
@GroupczyValue as varchar(50), --操作员名称
@StatColumn as varchar(50), --预统计的字段名实收{先用应收做测试}
@Operator as varchar(10) --统计的运算方式
AS
DECLARE @SQL as varchar(2000), @Column as varchar(50),@GroupczyDate as varchar(200) --定义参数
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy in '(''''+@GroupczyValue+'''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy'+@GroupczyDate + ' for read only') --定义游标
begin
SET @SQL='select ys_lsh,zh_id,zh_name,' --定义SQL语句头
OPEN cursor_column_field
while (0=0)
BEGIN --遍历游标
FETCH NEXT FROM cursor_column_field INTO @Column --通过游标获取列头信息
if (@@fetch_status<>0)
break
SET @SQL = @SQL + @Operator + '(CASE ys_xm WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + @Column + '], ' --循环追加SQL语句
END
SET @SQL = @SQL + @Operator + '(' + @StatColumn + ') AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time'--定义SQL语句尾
EXECUTE(@SQL) --执行SQL语句
PRINT @SQL --输出SQL语句
IF @@error <>0 RETURN @@error --如果出错,则返回错误代码
CLOSE cursor_column_field --关闭游标
DEALLOCATE cursor_column_field RETURN 0 --释放游标,释放成功则返回0
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO------------------------错误提示
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '=' 附近有语法错误。
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 18
名为 'cursor_column_field' 的游标不存在。
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 21
名为 'cursor_column_field' 的游标不存在。(所影响的行数为 4 行)select ys_lsh,zh_id,zh_name,SUM(ys_yj) AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 30
名为 'cursor_column_field' 的游标不存在。
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 31
名为 'cursor_column_field' 的游标不存在。
GO
SET ANSI_NULLS OFF
GO
alter procedure procedure_daytjlshtest
@GroupczyValue as varchar(50), --操作员名称
@StatColumn as varchar(50), --预统计的字段名实收{先用应收做测试}
@Operator as varchar(10) --统计的运算方式
AS
DECLARE @SQL as varchar(2000), @Column as varchar(50),@GroupczyDate as varchar(200) --定义参数
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy in '(''''+@GroupczyValue+'''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy'+@GroupczyDate + ' for read only') --定义游标
begin
SET @SQL='select ys_lsh,zh_id,zh_name,' --定义SQL语句头
OPEN cursor_column_field
while (0=0)
BEGIN --遍历游标
FETCH NEXT FROM cursor_column_field INTO @Column --通过游标获取列头信息
if (@@fetch_status<>0)
break
SET @SQL = @SQL + @Operator + '(CASE ys_xm WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + @Column + '], ' --循环追加SQL语句
END
SET @SQL = @SQL + @Operator + '(' + @StatColumn + ') AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time'--定义SQL语句尾
EXECUTE(@SQL) --执行SQL语句
PRINT @SQL --输出SQL语句
IF @@error <>0 RETURN @@error --如果出错,则返回错误代码
CLOSE cursor_column_field --关闭游标
DEALLOCATE cursor_column_field RETURN 0 --释放游标,释放成功则返回0
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO------------------------错误提示
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '=' 附近有语法错误。
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 18
名为 'cursor_column_field' 的游标不存在。
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 21
名为 'cursor_column_field' 的游标不存在。(所影响的行数为 4 行)select ys_lsh,zh_id,zh_name,SUM(ys_yj) AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 30
名为 'cursor_column_field' 的游标不存在。
服务器: 消息 16916,级别 16,状态 1,过程 procedure_daytjlshtest,行 31
名为 'cursor_column_field' 的游标不存在。
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
set @GroupczyDate='where ys_czy in '(''''+@GroupczyValue+'''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''--------
f(@GroupczyValue='')
set @GroupczyDate='where ys_czy in ''('''+@GroupczyValue+''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''
@GroupczyValue as varchar(50), --操作员名称
@StatColumn as varchar(50), --预统计的字段名实收{先用应收做测试}
@Operator as varchar(10) --统计的运算方式
AS
DECLARE @SQL as varchar(2000), @Column as varchar(50),@GroupczyDate as varchar(200) --定义参数
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy in ('''+@GroupczyValue+''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy'+@GroupczyDate + ' for read only') --定义游标
begin
SET @SQL='select ys_lsh,zh_id,zh_name,' --定义SQL语句头
OPEN cursor_column_field
while (0=0)
BEGIN --遍历游标
FETCH NEXT FROM cursor_column_field INTO @Column --通过游标获取列头信息
if (@@fetch_status<>0)
break
SET @SQL = @SQL + @Operator + '(CASE ys_xm WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + @Column + '], ' --循环追加SQL语句
END
SET @SQL = @SQL + @Operator + '(' + @StatColumn + ') AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time'--定义SQL语句尾
EXECUTE(@SQL) --执行SQL语句
PRINT @SQL --输出SQL语句
IF @@error <>0 RETURN @@error --如果出错,则返回错误代码
CLOSE cursor_column_field --关闭游标
DEALLOCATE cursor_column_field RETURN 0 --释放游标,释放成功则返回0
end
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy in ('''+@GroupczyValue+''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''我将上下改成一样会提示 in 附近有语法错误,,头大真不知道该怎么好。难道这个后边就不能跟where么?
DECLARE @SQL as varchar(2000), @Column as varchar(50),@GroupczyDate as varchar(200) --定义参数
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy in ('''+@GroupczyValue+''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''print(@GroupczyDate);
--你看看 ,对么?--把下面的代码注释
GO
SET ANSI_NULLS OFF
GO
alter procedure procedure_daytjlshtest
@GroupczyValue as varchar(50), --操作员名称
@StatColumn as varchar(50), --预统计的字段名实收{先用应收做测试}
@Operator as varchar(10) --统计的运算方式
AS
DECLARE @SQL as varchar(2000), @Column as varchar(50),@GroupczyDate as varchar(200) --定义参数
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy in ('''+@GroupczyValue+''')'
else
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy'+@GroupczyDate + ' for read only') --定义游标
begin
SET @SQL='select ys_lsh,zh_id,zh_name,' --定义SQL语句头
OPEN cursor_column_field
while (0=0)
BEGIN --遍历游标
FETCH NEXT FROM cursor_column_field INTO @Column --通过游标获取列头信息
if (@@fetch_status<>0)
break
SET @SQL = @SQL + @Operator + '(CASE ys_xm WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + @Column + '], ' --循环追加SQL语句
END
SET @SQL = @SQL + @Operator + '(' + @StatColumn + ') AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time'--定义SQL语句尾
EXECUTE(@SQL) --执行SQL语句
PRINT @SQL --输出SQL语句
IF @@error <>0 RETURN @@error --如果出错,则返回错误代码
CLOSE cursor_column_field --关闭游标
DEALLOCATE cursor_column_field RETURN 0 --释放游标,释放成功则返回0
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO问题依旧。很头大。 我只是想以操作员为条件,来统计下这个费用项目的总金额。
create procedure procedure_daytjlshtest
@GroupczyValue as varchar(50), --操作员名称
@StatColumn as varchar(50), --预统计的字段名实收{先用应收做测试}
@Operator as varchar(10) --统计的运算方式
AS
DECLARE @SQL as varchar(2000), @Column as varchar(50),@GroupczyDate as varchar(200) --定义参数
if(@GroupczyValue='')
set @GroupczyDate='where ys_czy = '''''
else
set @GroupczyDate='where ys_czy in ('+@GroupczyValue+')'
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy'+@GroupczyDate + ' for read only') --定义游标
begin
SET @SQL='select ys_lsh,zh_id,zh_name,' --定义SQL语句头
OPEN cursor_column_field
while (0=0)
BEGIN --遍历游标
FETCH NEXT FROM cursor_column_field INTO @Column --通过游标获取列头信息
if (@@fetch_status<>0)
break
SET @SQL = @SQL + @Operator + '(CASE ys_xm WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + @Column + '], ' --循环追加SQL语句
END
SET @SQL = @SQL + @Operator + '(' + @StatColumn + ') AS 合计 ,ys_czy,ys_time from ysfy group by ys_lsh,zh_id,zh_name,ys_czy,ys_time'--定义SQL语句尾
EXECUTE(@SQL) --执行SQL语句
PRINT @SQL --输出SQL语句
IF @@error <>0 RETURN @@error --如果出错,则返回错误代码
CLOSE cursor_column_field --关闭游标
DEALLOCATE cursor_column_field RETURN 0 --释放游标,释放成功则返回0
end
set @GroupczyDate='where ys_czy ='''+@GroupczyValue+''''
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy '+@GroupczyDate + ' for read only') --定义游标
EXECUTE ('DECLARE cursor_column_field CURSOR FOR SELECT DISTINCT ys_xm from ysfy'+@GroupczyDate + ' for read only') 定义游标 。
这句有问题 执行到这就会把 =号附近有语法错误