SQL 存储过程调用异地数据库 存储过程或表

EXEC @s_procedure1 @dt_bdate,@dt_edate,@dt_bindate,@dt_eindate,@s_line,@s_bus,@s_driver

这句应该是写成动态SQL语句。

exec ( @s_procedure1 + @dt_bdate + ', ' + @dt_edate + ',' + @dt_bindate + ',' @dt_eindate + ',' + @s_line + ',' + @s_bus + ',' + @s_driver )

因为，你取到得存储过程名称是一个字符串，所以要把这个串和相应的参数组成一个大串，然后用动态SQL执行的方法执行。

参数定义

单个参数

1>CREATE PROCEDURE HelloWorld1

2>@UserName VARCHAR(10)

3>AS

4>BEGIN

5>PRINT 'Hello' + @UserName + '!'

6>END

7>go

1>DECLARE @RC int

2>EXECUTE @RC = HelloWorld1 'Edward'

3>PRINT @RC

4>go

HelloEdward!

0

IN、OUT、IN OUT

注：

SQL Server 的 OUTPUT 需要写在变量数据类型后面。

SQL Server 没有 IN OUT 关键字

OUTPUT 已经相当于 IN OUT 了。

1>CREATE PROCEDURE HelloWorld2

2>@UserName VARCHAR(10),

3>@OutVal VARCHAR(10) OUTPUT,

4>@InoutVal VARCHAR(10) OUTPUT

5>AS

6>BEGIN

7>PRINT 'Hello ' + @UserName + @InoutVal + '!'

8>SET @OutVal = 'A'

9>SET @InoutVal = 'B'

10>END

11>go

1>

2>DECLARE @RC int, @OutVal VARCHAR(10), @InoutVal VARCHAR(10)

3>BEGIN

4>SET @InoutVal = '~Hi~'

5>EXECUTE @RC = HelloWorld2 'Edward', @OutVal OUTPUT, @InoutVal OUTPUT

6>PRINT @RC

7>PRINT '@OutVal=' + @OutVal

8>PRINT '@InoutVal=' + @InoutVal

9>END

10>go

Hello Edward~Hi~!

0

@OutVal=A

@InoutVal=B

参数的默认值

1>CREATE PROCEDURE HelloWorld3

2>@UserName VARCHAR(10),

3>@Val1 VARCHAR(20) = ' Good Moning,',

4>@Val2 VARCHAR(20) = ' Nice to Meet you'

5>AS

6>BEGIN

7>PRINT 'Hello ' + @UserName + @Val1 + @Val2 + '!'

8>END

9>go

1>

2>DECLARE @RC int

3>BEGIN

4>EXECUTE @RC = HelloWorld3 'Edward'

5>PRINT @RC

6>EXECUTE @RC = HelloWorld3 'Edward', ' Good Night,'

7>PRINT @RC

8>EXECUTE @RC = HelloWorld3 'Edward', ' Good Night,', ' Bye'

9>PRINT @RC

10>END

11>go

Hello Edward Good Moning, Nice to Meet you!

0

Hello Edward Good Night, Nice to Meet you!

0

Hello Edward Good Night, Bye!

0

指定参数名称调用

此部分使用 “参数默认值”那一小节的存储过程。

用于说明当最后2个参数是有默认的时候，如何跳过中间那个。

1>DECLARE @RC int

2>BEGIN

3>EXECUTE @RC = HelloWorld3 'Edward'

4>PRINT @RC

5>EXECUTE @RC = HelloWorld3 'Edward', @Val1=' Good Night,'

6>PRINT @RC

7>EXECUTE @RC = HelloWorld3 'Edward', @Val1=' Good Night,', @Val2=' Bye'

8>PRINT @RC

9>EXECUTE @RC = HelloWorld3 'Edward', @Val2=' HeiHei '

10>PRINT @RC

11>END

12>go

Hello Edward Good Moning, Nice to Meet you!

0

Hello Edward Good Night, Nice to Meet you!

0

Hello Edward Good Night, Bye!

0

Hello Edward Good Moning, HeiHei !

0

---