create table t(a int, b int, c int, d int ,e int, f int) gocreate proc sp_test (@var int) as declare cur cursor read_only for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40) declare @num int, @sql nvarchar(4000) , @Flag int,@temp int open curset @flag=0fetch next from cur into @name while (@@fetch_status =0 and @flag=1) begin --print '字段名:' + @name --初始化 set @num = 0 --获取这个字段的第一行的值 set @sql='select top 1 @a=[' + @name + '] from T' exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件 set @flag=1 fetch next from cur into @name endclose cur deallocate cur godrop proc sp_Test drop table t
create table t(a int, b int, c int, d int ,e int, f int) gocreate proc sp_test (@var int) as declare cur cursor read_only for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40) declare @num int, @sql nvarchar(4000) , @Flag int,@temp int open curset @flag=0fetch next from cur into @name while (@@fetch_status =0 and @flag=1) begin --print '字段名:' + @name --初始化 set @num = 0 --获取这个字段的第一行的值 set @sql='select top 1 @a=[' + @name + '] from T' exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件 set @flag=1 fetch next from cur into @name endclose cur deallocate cur godrop proc sp_Test drop table t
--如何将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 ------------------------------------------------------------------- 动态sql语句基本语法 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
if @temp = 10 --假设=10表示符合条件 set @flag=1-->替换为 if @temp = 10 --假设=10表示符合条件 begin set @flag=1 print @name --这个@name就是你要的字段名 end
--改 create table t(a int, b int, c int, d int ,e int, f int) gocreate proc sp_test ( @var int, @out varchar(100) output --输出参数 ) as declare cur cursor read_only for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40) declare @num int, @sql nvarchar(4000) , @Flag int,@temp int open curset @flag=0fetch next from cur into @name while (@@fetch_status =0 and @flag=1) begin --print '字段名:' + @name --初始化 set @num = 0 --获取这个字段的第一行的值 set @sql='select top 1 @a=[' + @name + '] from T' exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件 begin set @flag=1 print '符合条件的字段名:' + @name set @out=@name end fetch next from cur into @name endclose cur deallocate cur go-- 调用declare @ret int,@outPara varchar(100)exec sp_Test 5,@outPara outputselect @ret as 存储过程返回值, @outPara as 存储过程输出参数 drop proc sp_Test godrop table t go
create proc sp_test (@var int) as declare cur cursor read_only for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40) declare @num int, @sql nvarchar(4000) , @Flag int,@temp int open curset @flag=0fetch next from cur into @name while (@@fetch_status =0 and @flag=1) begin --print '字段名:' + @name --初始化 set @num = 0 --获取这个字段的第一行的值 set @sql='select top 1 @a=[' + @name + '] from T' exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp < 5 begin set @flag=1 print @name fetch next from cur into @name endclose cur deallocate cur go服务器: 消息 170,级别 15,状态 1,过程 sp_test,行 38 第 38 行: 'cur' 附近有语法错误。 也就是 deallocate cur 这里报,为什么呢?
--测试通过 create table t(a int, b int, c int, d int ,e int, f int) go insert into T(a,b,c,d,e,f) select 16,1,2,3,4,5go create proc sp_test ( @var int, @out varchar(100) output --输出参数 ) as declare cur cursor read_only for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40) declare @num int, @sql nvarchar(4000) , @Flag int,@temp int open curset @flag=0fetch next from cur into @name while (@@fetch_status =0 and @flag=0) begin --print '字段名:' + @name --初始化 set @num = 0 --获取这个字段的第一行的值 set @sql='select top 1 @a=[' + @name + '] from T' exec sp_executesql @sql,N'@a int output',@num output --select @num --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件 begin set @flag=1 print '符合条件的字段名:' + @name set @out=@name end fetch next from cur into @name endclose cur deallocate cur return 0 --0表示执行成功 go-- 调用declare @ret int,@outPara varchar(100)exec @ret=sp_Test 6,@outPara outputselect @ret as 存储过程返回值, @outPara as 存储过程输出参数字段名 drop proc sp_Test godrop table t go
gocreate proc sp_test (@var int)
as declare cur cursor
read_only
for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40)
declare @num int, @sql nvarchar(4000) , @Flag int,@temp int
open curset @flag=0fetch next from cur into @name
while (@@fetch_status =0 and @flag=1)
begin
--print '字段名:' + @name --初始化
set @num = 0 --获取这个字段的第一行的值
set @sql='select top 1 @a=[' + @name + '] from T'
exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var
set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件
set @flag=1 fetch next from cur into @name
endclose cur
deallocate cur
godrop proc sp_Test
drop table t
gocreate proc sp_test (@var int)
as declare cur cursor
read_only
for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40)
declare @num int, @sql nvarchar(4000) , @Flag int,@temp int
open curset @flag=0fetch next from cur into @name
while (@@fetch_status =0 and @flag=1)
begin
--print '字段名:' + @name --初始化
set @num = 0 --获取这个字段的第一行的值
set @sql='select top 1 @a=[' + @name + '] from T'
exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var
set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件
set @flag=1 fetch next from cur into @name
endclose cur
deallocate cur
godrop proc sp_Test
drop table t
--如何将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 -------------------------------------------------------------------
动态sql语句基本语法
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 @flag=1-->替换为
if @temp = 10 --假设=10表示符合条件
begin
set @flag=1
print @name --这个@name就是你要的字段名
end
create table t(a int, b int, c int, d int ,e int, f int)
gocreate proc sp_test
(
@var int,
@out varchar(100) output --输出参数
)
as declare cur cursor
read_only
for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40)
declare @num int, @sql nvarchar(4000) , @Flag int,@temp int
open curset @flag=0fetch next from cur into @name
while (@@fetch_status =0 and @flag=1)
begin
--print '字段名:' + @name --初始化
set @num = 0 --获取这个字段的第一行的值
set @sql='select top 1 @a=[' + @name + '] from T'
exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var
set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件
begin
set @flag=1
print '符合条件的字段名:' + @name
set @out=@name
end fetch next from cur into @name
endclose cur
deallocate cur
go-- 调用declare @ret int,@outPara varchar(100)exec sp_Test 5,@outPara outputselect @ret as 存储过程返回值, @outPara as 存储过程输出参数
drop proc sp_Test
godrop table t
go
as declare cur cursor
read_only
for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40)
declare @num int, @sql nvarchar(4000) , @Flag int,@temp int
open curset @flag=0fetch next from cur into @name
while (@@fetch_status =0 and @flag=1)
begin
--print '字段名:' + @name --初始化
set @num = 0 --获取这个字段的第一行的值
set @sql='select top 1 @a=[' + @name + '] from T'
exec sp_executesql @sql,N'@a int output',@num output --因为是数字,先转换为字符后,取末位,转换为数值后,加上@var
set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp < 5
begin
set @flag=1
print @name fetch next from cur into @name
endclose cur
deallocate cur
go服务器: 消息 170,级别 15,状态 1,过程 sp_test,行 38
第 38 行: 'cur' 附近有语法错误。
也就是 deallocate cur 这里报,为什么呢?
create table t(a int, b int, c int, d int ,e int, f int)
go
insert into T(a,b,c,d,e,f) select 16,1,2,3,4,5go
create proc sp_test
(
@var int,
@out varchar(100) output --输出参数
)
as declare cur cursor
read_only
for select name from syscolumns where id=object_id('T') order by coliddeclare @name varchar(40)
declare @num int, @sql nvarchar(4000) , @Flag int,@temp int
open curset @flag=0fetch next from cur into @name
while (@@fetch_status =0 and @flag=0)
begin
--print '字段名:' + @name --初始化
set @num = 0 --获取这个字段的第一行的值
set @sql='select top 1 @a=[' + @name + '] from T'
exec sp_executesql @sql,N'@a int output',@num output
--select @num
--因为是数字,先转换为字符后,取末位,转换为数值后,加上@var
set @temp=cast(right(cast(@num as varchar(100)),1) as int) + @var if @temp = 10 --假设=10表示符合条件
begin
set @flag=1
print '符合条件的字段名:' + @name
set @out=@name
end fetch next from cur into @name
endclose cur
deallocate cur
return 0 --0表示执行成功
go-- 调用declare @ret int,@outPara varchar(100)exec @ret=sp_Test 6,@outPara outputselect @ret as 存储过程返回值, @outPara as 存储过程输出参数字段名
drop proc sp_Test
godrop table t
go
我在程序里调用这个存储过程,并且在调用的时候就把参数@var传给了存储过程,现在我需要存储过程返回一个符合条件的字段名称,我的目的就是要找到一个符合条件的字段名称,我的数据库表里不是像这里a,b,c,d这样的字段,不知道这里的存储跟表格的字段名有没有关系,早上就得交东西了,这个问题还没解决,心慌,恳请wangtiecheng大哥多多耐心指导