参照sp_executesql 的使用语法格式是:
sp_executesql [ @stmt = ] stmt [ {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] } ]参考其中的一些参数的,返回的方法,这里制作一个 MyExecuteSql的存储过程,功能与sp_executesql相似。 这里提供可以输入9个可变参数,当然可以根据自己的爱好增加更多的参数。 这里免不了bug,最大的难点就是数据类型转换,有些数据类型,会转换不一致,有时间再研究研究。呵呵
Use
Test;
Go
If
Object_id
(
'
[MyExecuteSql]
'
,
'
P
'
)
Is
Not
null
Drop
Proc
[
MyExecuteSql
]
Go
Create
Proc
[
dbo
]
.
[
MyExecuteSql
]
(
@stmt
nvarchar
(
Max
),
@params
nvarchar
(
Max
)
=
null
,
@param1
nvarchar
(
Max
)
=
null
output,
@param2
nvarchar
(
Max
)
=
null
output,
@param3
nvarchar
(
Max
)
=
null
output,
@param4
nvarchar
(
Max
)
=
null
output,
@param5
nvarchar
(
Max
)
=
null
output,
@param6
nvarchar
(
Max
)
=
null
output,
@param7
nvarchar
(
Max
)
=
null
output,
@param8
nvarchar
(
Max
)
=
null
output,
@param9
nvarchar
(
Max
)
=
null
output )
As
Set
Nocount
On
Begin
Try
Declare
@xml
xml,
@tmp1
nvarchar
(
1024
),
@tmp2
nvarchar
(
1024
),
@Define
nvarchar
(
Max
),
@Set
nvarchar
(
Max
),
@Update
nvarchar
(
Max
)
If
Object_id
(
'
tempdb..#
'
)
Is
Not
null
Drop
Table
#
Create
Table
# ( id
smallint
Identity
(
1
,
1
)
Not
null
, param
nvarchar
(
1024
), datatype
nvarchar
(
1024
), flag
char
(
1
), value
nvarchar
(
max
), IsOutPut
bit
)
/*
筛分 @params
*/
Set
@xml
=
Convert
(xml,
Stuff
(
Replace
(
@params
+
'
,
'
,
'
@
'
,
'
</a><a>@
'
),
1
,
4
,
''
)
+
'
</a>
'
)
Insert
Into
# (datatype)
Select
t.m.value(
'
.
'
,
'
nvarchar(1024)
'
)
From
@xml
.nodes(
'
/a
'
) t(m)
Update
#
Set
@tmp1
=Left
(datatype,
CharIndex
(
'
'
,datatype)
-
1
), param
=
@tmp1
, IsOutPut
=
Case
When
Charindex
(
'
out
'
,datatype)
>
0
Then
1
Else
0
End
,
@tmp2
=
Reverse
(
Stuff
(
Reverse
(
Replace
(
Replace
(
Replace
(
Replace
(
Replace
(datatype,
@tmp1
,
''
),
'
output
'
,
''
),
'
out
'
,
''
),
'
input
'
,
''
),
'
'
,
''
)),
1
,
1
,
''
)), datatype
=
@tmp2
, flag
=
Case
When
CharIndex
(
'
tinyint
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
smallint
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
int
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
real
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
money
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
float
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
bit
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
decimal
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
numeric
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
smallmoney
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
bigint
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
varbinary
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
binary
'
,
@tmp2
)
>
0
Or
CharIndex
(
'
timestamp
'
,
@tmp2
)
>
0
Then
''
Else
''''
End
/*
构造执行语句
*/
Select
@Define
=
Isnull
(
@Define
+
'
,
'
,
'
Declare
'
)
+
param
+
'
'
+
datatype,
@Set
=
Isnull
(
@Set
+
'
,
'
,
'
Select
'
)
+
param
+
'
=Convert(
'
+
datatype
+
'
,
'
+
Case
id
When
1
Then
Isnull
(flag
+
@param1
+
flag,
'
null
'
)
When
2
Then
Isnull
(flag
+
@param2
+
flag,
'
null
'
)
When
3
Then
Isnull
(flag
+
@param3
+
flag,
'
null
'
)
When
4
Then
Isnull
(flag
+
@param4
+
flag,
'
null
'
)
When
5
Then
Isnull
(flag
+
@param5
+
flag,
'
null
'
)
When
6
Then
Isnull
(flag
+
@param6
+
flag,
'
null
'
)
When
7
Then
Isnull
(flag
+
@param7
+
flag,
'
null
'
)
When
8
Then
Isnull
(flag
+
@param8
+
flag,
'
null
'
)
When
9
Then
Isnull
(flag
+
@param9
+
flag,
'
null
'
)
End
+
'
)
'
,
@Update
=
Isnull
(
@Update
+
'
When
'
,
'
Update # Set value=Case id When
'
)
+
Rtrim
(id)
+
'
Then Convert(nvarchar(max),
'
+
param
+
'
)
'
From
#
Set
@stmt
=
@Define
+
char
(
13
)
+
char
(
10
)
+
@Set
+
char
(
13
)
+
char
(
10
)
+
'
Set Nocount Off
'
+
char
(
13
)
+
char
(
10
)
+
@stmt
+
char
(
13
)
+
char
(
10
)
+
'
Set Nocount On
'
+
char
(
13
)
+
char
(
10
)
+
@Update
+
'
End
'
Exec
(
@stmt
)
/*
输出参数
*/
Select
@param1
=
value
From
#
Where
id
=
1
And
IsOutPut
=
1
Select
@param2
=
value
From
#
Where
id
=
2
And
IsOutPut
=
1
Select
@param3
=
value
From
#
Where
id
=
3
And
IsOutPut
=
1
Select
@param4
=
value
From
#
Where
id
=
4
And
IsOutPut
=
1
Select
@param5
=
value
From
#
Where
id
=
5
And
IsOutPut
=
1
Select
@param6
=
value
From
#
Where
id
=
6
And
IsOutPut
=
1
Select
@param7
=
value
From
#
Where
id
=
7
And
IsOutPut
=
1
Select
@param8
=
value
From
#
Where
id
=
8
And
IsOutPut
=
1
Select
@param9
=
value
From
#
Where
id
=9
And
IsOutPut
=
1
End
Try
Begin
Catch
Declare
@ErrMsg
nvarchar
(
1024
)
Set
@ErrMsg
=
Error_message()
Raiserror
50001
@ErrMsg
End
Catch
测试:
Declare
@x
numeric(
12
,
3
)
Set
@x
=
56.31
Print
'
结果:
'
Print
'
开始 @x=
'
+
Rtrim
(
@x
)
+
char
(
13
)
+
char
(
10
)
+
char
(
13
)
+
char
(
10
)
+
'
调用[MyExecuteSql]:
'
exec
[
MyExecuteSql
]
'
Select @2=@2*5;Select @1,@2,@3
'
,
'
@1 nvarchar(20 ) input ,@2 numeric(12, 3) output , @3 int
'
,
'
sdf
'
,
@x
output,
34
Print
'
输出 @x=
'
+
Rtrim
(
@x
)
/* 结果: 开始 @x=56.310 调用[MyExecuteSql]: -------------------- -------------------------- sdf 281.550 34 (1 行受影响) 输出 @x=281.550 */