主要用到动态语SQL语法。 1 :普通SQL语句可以用Exec执行 例: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL 错误: 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 -- 此句会报错 --注:@s参数必须为ntext或nchar或nvarchar类型,必须将declare @s varchar(1000) 改为declare @s Nvarchar(1000) 如下: declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @fname = 'FiledName' --设置字段名 set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句正确3. 输入或输出参数 (1)输入参数: declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @input_id int--定义需传入动态语句的参数的值 set @QueryString='select * from tablename where id=@id' --id为字段名,@id为要传入的参数 set @paramstring='@id int' --设置动态语句中参数的定义的字符串 set @input_id =1 --设置需传入动态语句的参数的值为1 exec sp_executesql @querystring,@paramstring,@id=@input_id 若有多个参数: declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @input_id int--定义需传入动态语句的参数的值,参数1 declare @input_name varchar(20)--定义需传入动态语句的参数的值,参数2 set @QueryString='select * from tablename where id=@id and name=@name' --id与name为字段名,@id与@name为要传入的参数 set @paramstring='@id int,@name varchar(20)' --设置动态语句中参数的定义的字符串,多个参数用","隔开 set @input_id =1 --设置需传入动态语句的参数的值为1 set @input_name='张三' --设置需传入动态语句的参数的值为"张三" exec sp_executesql @querystring,@paramstring,@id=@input_id,@name=@input_name --请注意参数的顺序 (2)输出参数 declare @num int, @sqls nvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中? declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) declare @output_result int--查询结果赋给@output_result set @QueryString='select @totalcount=count(*) from tablename' --@totalcount 为输出结果参数 set @paramstring='@totalcount int output' --设置动态语句中参数的定义的字符串,多个参数用","隔开 exec sp_executesql @querystring,@paramstring,@totalcount=@output_result output select @output_result 当然,输入与输出参数可以一起使用,大家可以自己去试一试。 另外,动态语句查询的结果集要输出的话,我只想到以下用临时表的方法,不知各位有没有更好的方法. IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除 drop table #tmp select * into #tmp from tablename where 1=2 --创建临时表#tmp,其结构与tablename相同 declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型) set @QueryString='select * from tablename ' insert into #tmp(field1,field2,...) exec(@querystirng)
这是我用一个现成的存储过程: /* 作者:1015 日期:2009-04-02 drop proc dbo.createProc exec dbo.createProc 'tbEmployee' */ CREATE PROCEDURE dbo.createProc @tableName varchar(50) as set nocount ondeclare @s varchar(2000),@s1 varchar(2000),@s2 varchar(2000) declare @insertSql varchar(8000) declare @updateSql varchar(8000) declare @deleteSql varchar(8000)select @s='',@s1='',@s2='' select @s=@s+name+',',@s1=@s1+'@'+name+',',@s2=@s2+name+'=@'+name+',' from syscolumns where id=object_id(@tableName) --and name<>'id' order by colid set @s= left(@s,len(@s)-1) set @s1= left(@s1,len(@s1)-1) set @s2= left(@s2,len(@s2)-1)declare @i int,@j int,@k1 int,@k2 int,@k3 int declare @v varchar(1) declare @temp varchar(2000)--INSERT语句换行处理 select @i=1,@j=0,@k1=0,@k2=0,@k3=0 set @temp=@s while @i<len(@temp) begin set @v=substring(@temp,@i,1) if @v=',' begin set @k1=@k1+1 if @k1>0 and @k1%8=0 begin set @s=substring(@s,1,@i+@j)+char(10)+char(9)+substring(@s,@i+@j+1,len(@temp)+100) set @j=@j+2 end end set @i=@i+1 end --VALUES语句换行处理 select @i=1,@j=0,@k1=0,@k2=0,@k3=0 set @temp=@s1 while @i<len(@temp) begin set @v=substring(@temp,@i,1) if @v=',' begin set @k1=@k1+1 if @k1>0 and @k1%8=0 begin set @s1=substring(@s1,1,@i+@j)+char(10)+char(9)+substring(@s1,@i+@j+1,len(@temp)+100) set @j=@j+2 end end set @i=@i+1 end--UDPATE语句换行处理 select @i=1,@j=0,@k1=0,@k2=0,@k3=0 set @temp=@s2 while @i<len(@temp) begin set @v=substring(@temp,@i,1) if @v=',' begin set @k1=@k1+1 if @k1>0 and @k1%5=0 begin set @s2=substring(@s2,1,@i+@j)+char(10)+char(9)+substring(@s2,@i+@j+1,len(@temp)+100) set @j=@j+2 end end set @i=@i+1 end--生成输入参数 create table #t(datatype varchar(100)) insert into #t select datatype='@'+a.name+' '+b.name+ isnull('('+case when a.xtype=61 then null --日期类型 when a.xtype=56 or a.xtype=127 then null --日期类型 when a.xtype=108 then cast(a.xprec as varchar)+(case when a.xscale=0 then '' else ','+cast(a.xscale as varchar) end) --numeric类型 else cast(OdbcPrec(a.xtype,a.length,a.xprec) as varchar) end+')','') from syscolumns a inner join systypes b on a.xusertype=b.xusertype where a.id=object_id(@tableName) and a.name not in('person_name1','date1') order by a.coliddeclare @p varchar(8000) set @p='' select @p=@p+datatype+','+char(10) from #t set @p=left(@p,len(@p)-2) drop table #tset @insertSql=char(9)+'INSERT INTO '+@tableName+'('+@s+') '+char(10)+char(9)+'VALUES('+@s1+')' set @updateSql=char(9)+'UPDATE '+@tableName+char(10)+char(9)+'SET '+@s2+char(10)+char(9)+'WHERE '+left(@s2,charindex(',',@s2)-1) declare @procSql varchar(8000),@procName varchar(50) set @procName='dbo.proc_'+@tableName+'_oprt' set @procSql='/* 作者:'+char(10)+ '日期:'+convert(varchar(19),getdate(),120)+char(10)+ ' DROP PROC '+@procName+char(10)+ 'EXEC '+@procName+char(10)+ '*/ ALTER PROCEDURE dbo.proc_'+@tableName+'_oprt'+char(10) +'@oprt_mode varchar(10),'+char(10) +@p+char(10)+'AS'+char(10) +'--SET NOCOUNT ON'+char(10) +char(10) +'IF UPPER(@oprt_mode)=''ADD'' BEGIN '+@insertSql+char(10)+'END'+char(10)+char(10) +'IF UPPER(@oprt_mode)=''UPDATE'' BEGIN '+char(9)+'--EXEC dbo.procWriteLogs '''+@tableName+''',''UPDATE'',@person_no1,''where 条件=主键值'','''''+char(10) +@updateSql+char(10)+'END'+char(10)+char(10) -- +'IF UPPER(@oprt_mode)=''DELETE'' -- BEGIN -- '+@deleteSql+char(10)+'END'+char(10)+char(10) -- +char(10)+char(10) +'GO'-- print @p -- print @insertSql -- print @updateSql -- print @deleteSql print @procSql GO
select b.* from sysobjects a,syscolumns b where a.id=b.id and a.name='tab'
然后加上你的参数即可.
1 :普通SQL语句可以用Exec执行 例: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL 错误: 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 -- 此句会报错 --注:@s参数必须为ntext或nchar或nvarchar类型,必须将declare @s varchar(1000) 改为declare @s Nvarchar(1000) 如下:
declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @fname = 'FiledName' --设置字段名
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确3. 输入或输出参数 (1)输入参数:
declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @input_id int--定义需传入动态语句的参数的值 set @QueryString='select * from tablename where id=@id' --id为字段名,@id为要传入的参数
set @paramstring='@id int' --设置动态语句中参数的定义的字符串
set @input_id =1 --设置需传入动态语句的参数的值为1
exec sp_executesql @querystring,@paramstring,@id=@input_id
若有多个参数:
declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @input_id int--定义需传入动态语句的参数的值,参数1
declare @input_name varchar(20)--定义需传入动态语句的参数的值,参数2 set @QueryString='select * from tablename where id=@id and name=@name' --id与name为字段名,@id与@name为要传入的参数
set @paramstring='@id int,@name varchar(20)' --设置动态语句中参数的定义的字符串,多个参数用","隔开
set @input_id =1 --设置需传入动态语句的参数的值为1
set @input_name='张三' --设置需传入动态语句的参数的值为"张三"
exec sp_executesql @querystring,@paramstring,@id=@input_id,@name=@input_name --请注意参数的顺序
(2)输出参数
declare @num int, @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中?
declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
declare @output_result int--查询结果赋给@output_result set @QueryString='select @totalcount=count(*) from tablename' --@totalcount 为输出结果参数
set @paramstring='@totalcount int output' --设置动态语句中参数的定义的字符串,多个参数用","隔开
exec sp_executesql @querystring,@paramstring,@totalcount=@output_result output
select @output_result
当然,输入与输出参数可以一起使用,大家可以自己去试一试。
另外,动态语句查询的结果集要输出的话,我只想到以下用临时表的方法,不知各位有没有更好的方法.
IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
drop table #tmp
select * into #tmp from tablename where 1=2 --创建临时表#tmp,其结构与tablename相同 declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
set @QueryString='select * from tablename '
insert into #tmp(field1,field2,...) exec(@querystirng)
/*
作者:1015
日期:2009-04-02
drop proc dbo.createProc
exec dbo.createProc 'tbEmployee'
*/
CREATE PROCEDURE dbo.createProc @tableName varchar(50)
as
set nocount ondeclare @s varchar(2000),@s1 varchar(2000),@s2 varchar(2000)
declare @insertSql varchar(8000)
declare @updateSql varchar(8000)
declare @deleteSql varchar(8000)select @s='',@s1='',@s2=''
select @s=@s+name+',',@s1=@s1+'@'+name+',',@s2=@s2+name+'=@'+name+','
from syscolumns
where id=object_id(@tableName) --and name<>'id'
order by colid
set @s= left(@s,len(@s)-1)
set @s1= left(@s1,len(@s1)-1)
set @s2= left(@s2,len(@s2)-1)declare @i int,@j int,@k1 int,@k2 int,@k3 int
declare @v varchar(1)
declare @temp varchar(2000)--INSERT语句换行处理
select @i=1,@j=0,@k1=0,@k2=0,@k3=0
set @temp=@s
while @i<len(@temp)
begin
set @v=substring(@temp,@i,1)
if @v=','
begin
set @k1=@k1+1
if @k1>0 and @k1%8=0
begin
set @s=substring(@s,1,@i+@j)+char(10)+char(9)+substring(@s,@i+@j+1,len(@temp)+100)
set @j=@j+2
end
end
set @i=@i+1
end
--VALUES语句换行处理
select @i=1,@j=0,@k1=0,@k2=0,@k3=0
set @temp=@s1
while @i<len(@temp)
begin
set @v=substring(@temp,@i,1)
if @v=','
begin
set @k1=@k1+1
if @k1>0 and @k1%8=0
begin
set @s1=substring(@s1,1,@i+@j)+char(10)+char(9)+substring(@s1,@i+@j+1,len(@temp)+100)
set @j=@j+2
end
end
set @i=@i+1
end--UDPATE语句换行处理
select @i=1,@j=0,@k1=0,@k2=0,@k3=0
set @temp=@s2
while @i<len(@temp)
begin
set @v=substring(@temp,@i,1)
if @v=','
begin
set @k1=@k1+1
if @k1>0 and @k1%5=0
begin
set @s2=substring(@s2,1,@i+@j)+char(10)+char(9)+substring(@s2,@i+@j+1,len(@temp)+100)
set @j=@j+2
end
end
set @i=@i+1
end--生成输入参数
create table #t(datatype varchar(100))
insert into #t
select datatype='@'+a.name+' '+b.name+
isnull('('+case when a.xtype=61 then null --日期类型
when a.xtype=56 or a.xtype=127 then null --日期类型
when a.xtype=108 then cast(a.xprec as varchar)+(case when a.xscale=0 then '' else ','+cast(a.xscale as varchar) end) --numeric类型
else cast(OdbcPrec(a.xtype,a.length,a.xprec) as varchar) end+')','')
from syscolumns a
inner join systypes b on a.xusertype=b.xusertype
where a.id=object_id(@tableName) and a.name not in('person_name1','date1')
order by a.coliddeclare @p varchar(8000)
set @p=''
select @p=@p+datatype+','+char(10) from #t
set @p=left(@p,len(@p)-2)
drop table #tset @insertSql=char(9)+'INSERT INTO '+@tableName+'('+@s+') '+char(10)+char(9)+'VALUES('+@s1+')'
set @updateSql=char(9)+'UPDATE '+@tableName+char(10)+char(9)+'SET '+@s2+char(10)+char(9)+'WHERE '+left(@s2,charindex(',',@s2)-1)
declare @procSql varchar(8000),@procName varchar(50)
set @procName='dbo.proc_'+@tableName+'_oprt'
set @procSql='/*
作者:'+char(10)+
'日期:'+convert(varchar(19),getdate(),120)+char(10)+
'
DROP PROC '+@procName+char(10)+
'EXEC '+@procName+char(10)+
'*/
ALTER PROCEDURE dbo.proc_'+@tableName+'_oprt'+char(10)
+'@oprt_mode varchar(10),'+char(10)
+@p+char(10)+'AS'+char(10)
+'--SET NOCOUNT ON'+char(10)
+char(10)
+'IF UPPER(@oprt_mode)=''ADD''
BEGIN
'+@insertSql+char(10)+'END'+char(10)+char(10)
+'IF UPPER(@oprt_mode)=''UPDATE''
BEGIN
'+char(9)+'--EXEC dbo.procWriteLogs '''+@tableName+''',''UPDATE'',@person_no1,''where 条件=主键值'','''''+char(10)
+@updateSql+char(10)+'END'+char(10)+char(10)
-- +'IF UPPER(@oprt_mode)=''DELETE''
-- BEGIN
-- '+@deleteSql+char(10)+'END'+char(10)+char(10)
-- +char(10)+char(10)
+'GO'-- print @p
-- print @insertSql
-- print @updateSql
-- print @deleteSql
print @procSql
GO
这个已经很棒了,good!
但有些地方参数还需要稍微调整,如:不能为类似“real”类型指定长度
还有我没搞明白为什么生成的存储过程都是“ALTER”,修改不过来
还有手动拷贝生成的存储过程也很不爽还有就是我发现SQL Server2000有这样的工具,选择表即可生成存储过程,很爽的
“企业管理器--工具->向导->数据库->创建存储过程向导”