Question

请问oracle中的存储过程转成sql server的怎么写？

create or replace procedure proc_Login(
l_UserName in Varchar2,
l_PassWord in Varchar2,
l_out_truename OUT varchar2,
l_out_fun OUT varchar2,
l_out_UserId OUT varchar2
) is
v_sql VARCHAR2(8000) := '';
v_fun varchar2(100):='';
v_fun2 varchar2(20):='';
begin
begin
v_sql:='select UserId,fun,truename from v_user where username='''||l_UserName||''' and password='''||l_PassWord||''' and DStatus=1 and RStatus=1 and Status=1';
execute immediate v_sql into l_out_UserId,v_fun,l_out_truename;
exception when others then
v_fun :=null;
end;
if v_fun is not null then
v_sql:='select wm_concat(parentId) from (select distinct parentId from t_fun_dic where fun_id in ('||v_fun||'))';
execute immediate v_sql into v_fun2;
l_out_fun:=v_fun||','||v_fun2;
end if;

end proc_Login;

Answer 1

这个 相当于 把 C++ 的改写 成 JAVA 差不多少，如果plsql 没用使用面向对象特性 直接就可以了按照TSQL 语法编写了。

Answer 2

create procedure proc_Login( l_UserName in varchar(100)........
sqlserver 没有varchar2，你必须使用varchar(具体的长度)来定义

追问

begin 后面的怎么改？

Answer 3

create procedure proc_Login
(
@l_UserName Varchar(20),
@l_PassWord Varchar(20),

@l_out_truename Varchar(20) output,
@l_out_fun Varchar(20) output,
@l_out_UserId Varchar(20) output
)
as
declare @v_sql VARCHAR(8000) = '';
declare @v_fun varchar(100) ='';
declare @v_fun2 varchar(20) ='';
begin try

set @v_sql='select UserId,fun,truename from v_user where username='''+@l_UserName+''' and password='''+@l_PassWord+'''and DStatus=1 and RStatus=1 and Status=1';
execute @v_sql @l_out_UserId,@v_fun,@l_out_truename;
end try
begin catch
set @v_fun =null
if (@v_fun <>null)
set @v_sql ='select wm_concat(parentId) from (select distinct parentId from t_fun_dic where fun_id in ('''+@v_fun+'''))';
execute @v_sql @v_fun2;
set @l_out_fun =@v_fun+','+@v_fun2;
end catch