这样的存储过程，在数据库中应该怎样执行？？？

starryautomation 2012-07-10 03:11:12

这样的存储过程，在数据库中应该怎样执行？？？

存储过程如下：

ALTER proc [dbo].[FindRoom]
@FieldNames nvarchar(100),
@Condition nvarchar(100)
as
declare @sql nvarchar(800)
set @sql='select '
if @FieldNames<>''
set @sql=@sql+@FieldNames
else
set @sql=@sql+'RoomNo
,RoomName
,RoomAlias
,RoomArea
,PersonNum'
set @sql=@sql+' FROM Room where 1=1 '+@Condition
exec (@sql)

目的：为此存储过程传递“两个空字符串”，作为参数。

第一种方式，右键执行存储过程

（1）什么值也不传。

结果：

DECLARE @return_value int

EXEC @return_value = [dbo].[FindRoom]

SELECT 'Return Value' = @return_value

错误信息：

Msg 201, Level 16, State 4, Procedure FindRoom, Line 0
Procedure or function 'FindRoom' expects parameter '@FieldNames', which was not supplied.

(1 row(s) affected)

（2）传入两个""

结果：

DECLARE @return_value int

EXEC @return_value = [dbo].[FindRoom]
@FieldNames = N'""',
@Condition = N'""'

SELECT 'Return Value' = @return_value

错误信息：

Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.

（3）传入两个''

结果：

DECLARE @return_value int

EXEC @return_value = [dbo].[FindRoom]
@FieldNames = N'''''',
@Condition = N''''''

SELECT 'Return Value' = @return_value

错误信息：
Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ''.

(1 row(s) affected)

第二种方式，SQL执行存储过程。

（1）exec FindRoom '' ''

错误信息：

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.

（2）exec FindRoom '''' ''''

错误信息：

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '''.

（3）exec FindRoom '''''' ''''''

错误信息：

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''''.

（4）exec FindRoom

Msg 201, Level 16, State 4, Procedure FindRoom, Line 0
Procedure or function 'FindRoom' expects parameter '@FieldNames', which was not supplied.

问题：试了那么多的方法，到底怎样才能给“存储过程FindRoom”传两个“空字符串”作为参数。。。为什么它们都不行呢？？？

...全文