参数表:参数 值
A 1
B 2
D 3
因为参数表经常变化,我想并动态创建一个函数,创建时遍历参数表,这个需要游标,请问这样的存储过程该怎么写?
CREATE function dbo.aaa(@st varchar(15))
returns varchar(12)
as
begin
declare @p varchar(12)
if @st='A' set @p='1'
else if @st='B' set @p='2'
else if @st='D' set @p='3'
else set @p='x'
return @p
end
A 1
B 2
D 3
因为参数表经常变化,我想并动态创建一个函数,创建时遍历参数表,这个需要游标,请问这样的存储过程该怎么写?
CREATE function dbo.aaa(@st varchar(15))
returns varchar(12)
as
begin
declare @p varchar(12)
if @st='A' set @p='1'
else if @st='B' set @p='2'
else if @st='D' set @p='3'
else set @p='x'
return @p
end
@st varchar(15),
@ret varchar(15) output
as
select @ret = 值 from tablename where 参数 = @st
set @ret = isnull(@ret,'x')
go
returns varchar(12)
as
begin
declare @p varchar(12)
select @p=值 from [参数表] where 参数=@st
return @p
end
declare @SQL nvarchar(8000)
set @SQL= 'CREATE function dbo.aaa(@st varchar(15)) returns varchar(12) as"
这里用游标把参数表中所有数据遍取出, 参数表中有几行这里就生成几行
set @sql= @sql + ' if @st='A' set @p='1' '
SET @SQL=@sql + 'else set @p='x' return @p '
EXEC (@SQL)
CREATE PROCEDURE CREAaaa ()
AS
declare @SQL nvarchar(8000)
if exists(select 1 from sysobjects where name='aaa' and xtype='FN' and status>0) drop function aaa
set @SQL= 'CREATE function dbo.aaa(@st varchar(15)) returns varchar(12) as"
declare cursor1 cursor for select 参数,值 from 参数表
open cursor1
fetch cursor1 into
while(@@fetch_status = 0)
begin 这里用游标把参数表中数据循环取出, 生成动态字符串
set @sql= @sql + ' if @st=参数 set @p=值 '
end
SET @SQL=@sql + 'else set @p='x' return @p '
EXEC (@SQL)
RETURN
GO
CREATE PROCEDURE CREAaaa ()
AS
declare @SQL nvarchar(8000) ,@CanSu varchar(20), @Zhi varchar(20),if exists(select 1 from sysobjects where name='''aaa''' and xtype='FN' and status>0) drop function aaa
set @SQL= 'CREATE function dbo.aaa(@st varchar(15)) returns varchar(12) as"
declare cursor1 cursor for select 参数,值 from 参数表
open cursor1
fetch cursor1 into @CanSu, @Zhi
while(@@fetch_status = 0)
begin 这里用游标把参数表中所有数据遍取出, 生成动态字符串
set @sql=@sql + 'if @st= ' + @CanSu + 'set @p=''' + @Zhi
end
SET @SQL=@sql + 'else set @p='x' return @p '
EXEC (@SQL) RETURN
GO