为什么我这个stored procedure执行后返回的是空表格，请大家帮我看看吧

Question

CREATE PROCEDURE sp_displaychosencustomers 
@id int, 
@name varchar(50), 
@birth datetime, 
@address varchar(50), 
@phone varchar(50), 
@state varchar(5) 
AS 
begin
    DECLARE @str varchar(255) 
        
    if ( @id <> 0) 
        SET @str = 'ID = ' + @id 
    if ( (@name is not null) AND @id <> 0 ) 
        SET @str = @str + ' AND NAME = ''' + @name + '''' 
    else if ( (@name is not null) AND @id = 0 ) 
        SET @str ='NAME = ''' + @name + ''''
    if ( ( datediff(dd,@birth,getdate())!=0 ) AND ( @id <> 0 OR ( @name is NOT null ) )) 
        SET @str = @str + ' AND  Birth = ''' + @birth + '''' 
    else if ( datediff(dd,@birth,getdate())!=0 AND @id = 0 AND @name is null ) 
        SET @str = ( 'Birth= '' + @birth + ''' ) 
    if ( @address is not null AND ( @id <> 0 OR @name is not null OR datediff(dd,@birth,getdate())!=0 )) 
        SET @str = @str + ' AND ADDRESS = ''' + @address + '''' 
    else if ( @address is not null AND @id = 0 AND @name is null AND datediff(dd,@birth,getdate())=0 ) 
        SET @str = 'Address = ''' + @address + ''''
    if ( @phone is not null AND ( @id = 0 OR @name is not null OR datediff(dd,@birth,getdate())!=0 OR @address is not null )) 
        SET @str = @str + ' AND PHONE = ''' + @phone + '''' 
    else if ( @phone is not null AND @id = 0 AND @name is null AND datediff(dd,@birth,getdate())=0 AND @address is null ) 
        SET @str = 'PHONE= ''' + @phone + ''''
    if ( @state is not null AND ( @id <> 0 OR @name is not null OR datediff(dd,@birth,getdate())!=0 OR @address is not null OR @phone is not null )) 
        SET @str = @str + ' AND STATE = ''' + @state + '''' 
    else if ( @state is not null AND @id = 0 AND @name is null AND datediff(dd,@birth,getdate())=0 AND @address is null AND @phone is null ) 
        SET @str =  'STATE = ' + @state + ''''     if (@str is null) 
        select * from customer 
    else if (@str is not null ) 
        exec('select * from customer where '+@str) 
end我给的参数是:
id=2
name = null
birth = 2/13/2009(就是当天的日期)
address,phone,state也都是null但返回的表格是空表格,不知道是哪里不对，请大家帮我看看吧

Answer 1

给你改成参数传递吧, 这样脚本看起来没有这么累
CREATE PROCEDURE sp_displaychosencustomers 
@id int, 
@name varchar(50), 
@birth datetime, 
@address varchar(50), 
@phone varchar(50), 
@state varchar(5) 
AS 
begin 
    DECLARE @str nvarchar(4000); SELECT
@str =
CASE
WHEN @id <> 0 THEN N' AND ID = @id'
ELSE N''
END
+ CASE
WHEN @name IS NOT NULL THEN N' AND NAME = @name'
ELSE N''
END
+ CASE
WHEN DATEDIFF(Day, @birth, GETDATE()) <> 0 THEN N' AND Birth = @birth'
ELSE N''
END
+ CASE
WHEN @address IS NOT NULL THEN N' AND ADDRESS = @address'
ELSE N''
END
+ CASE
WHEN @phone IS NOT NULL THEN N' AND PHONE = @phone'
ELSE N''
END
+ CASE
WHEN @state IS NOT NULL THEN N' AND STATE = @state'
ELSE N''
END;
IF @str <> N''
SET @str = STUFF(@str, 1, 4, N' WHERE ');
SET @str = N'select * from customer ' + @str;

EXEC sp_executesql
@str,
N'
@id int, 
@name varchar(50), 
@birth datetime, 
@address varchar(50), 
@phone varchar(50), 
@state varchar(5) 
',
@id, @name, @birth, @address, @phone, @state;
end

Answer 2

这个是我的table
1 Alen 1/1/1970 12:00:00 AM 18231 barroso st 6019250001 AL
2 Bob 2/2/1971 12:00:00 AM 6019250002 CA
3 Carter 3/3/1972 12:00:00 AM 6019250003 CT
4 David 4/4/1973 12:00:00 AM NULL 6019250004 CA
5 Evan 5/5/1974 12:00:00 AM NULL 6019250005 AR
6 Ford 6/6/1975 12:00:00 AM NULL 6019250006 FL
7 Hanna 7/7/1976 12:00:00 AM NULL 6019250007 FL
8 Ilian 8/8/1977 12:00:00 AM NULL 6019250008 HI
9 Jack 9/9/1978 12:00:00 AM NULL 6019250009 IN
10 Ken 10/10/1979 12:00:00 AM NULL 6019250010 IN
11 Loen 11/11/1980 12:00:00 AM NULL 6019250011 HI
12 Mark 12/12/1981 12:00:00 AM NULL 6019250012 MI
13 Nancy 1/2/1982 12:00:00 AM NULL 6019250013 NJ
14 Oliver 2/2/1983 12:00:00 AM NULL 6019250014 NY
15 Peter 3/2/1984 12:00:00 AM NULL 6019250015 NJ
16 Queen 4/2/1985 12:00:00 AM NULL 6019250016 MI
17 Roy 5/2/1986 12:00:00 AM NULL 6019250017 WA
18 Sue 6/2/1987 12:00:00 AM NULL 6019250018 UT
19 Tae 7/2/1988 12:00:00 AM NULL 6019250019 UT
20 Uli 8/2/1989 12:00:00 AM NULL 6019250020 WA
21 Vivian 9/2/1990 12:00:00 AM NULL 6019250021 CA
22 Walt 10/2/1991 12:00:00 AM NULL 6019250022 OH
23 Xenia 11/3/1992 12:00:00 AM NULL 6019250023 TX
24 Yolanda 12/3/1993 12:00:00 AM NULL 6019250024 OH
25 Zero 1/3/1994 12:00:00 AM NULL 6019250025 TX

Answer 3

下面这个是我的table:ID      Name    Birth                   Address                 phone          state
1 Alen 1/1/1970 12:00:00 AM 18231 barroso st 6019250001 AL
2 Bob 2/2/1971 12:00:00 AM                 6019250002 CA
3 Carter 3/3/1972 12:00:00 AM 6019250003 CT
4 David 4/4/1973 12:00:00 AM NULL 6019250004 CA
5 Evan 5/5/1974 12:00:00 AM NULL 6019250005 AR
6 Ford 6/6/1975 12:00:00 AM NULL 6019250006 FL
7 Hanna 7/7/1976 12:00:00 AM NULL 6019250007 FL
8 Ilian 8/8/1977 12:00:00 AM NULL 6019250008 HI
9 Jack 9/9/1978 12:00:00 AM NULL 6019250009 IN
10 Ken 10/10/1979 12:00:00 AM NULL 6019250010 IN
11 Loen 11/11/1980 12:00:00 AM NULL 6019250011 HI
12 Mark 12/12/1981 12:00:00 AM NULL 6019250012 MI
13 Nancy 1/2/1982 12:00:00 AM NULL 6019250013 NJ
14 Oliver 2/2/1983 12:00:00 AM NULL 6019250014 NY
15 Peter 3/2/1984 12:00:00 AM NULL 6019250015 NJ
16 Queen 4/2/1985 12:00:00 AM NULL 6019250016 MI
17 Roy 5/2/1986 12:00:00 AM NULL 6019250017 WA
18 Sue 6/2/1987 12:00:00 AM NULL 6019250018 UT
19 Tae 7/2/1988 12:00:00 AM NULL 6019250019 UT
20 Uli 8/2/1989 12:00:00 AM NULL 6019250020 WA
21 Vivian 9/2/1990 12:00:00 AM NULL 6019250021 CA
22 Walt 10/2/1991 12:00:00 AM NULL 6019250022 OH
23 Xenia 11/3/1992 12:00:00 AM NULL 6019250023 TX
24 Yolanda 12/3/1993 12:00:00 AM NULL 6019250024 OH
25 Zero 1/3/1994 12:00:00 AM NULL 6019250025 TX

Answer 4

N是字符串前导符, 表示后面使用unicode数据

Answer 5

哦。我吧我的表格放在上面了，里面是有ID=2的记录的当我吧id=2,name=null,birth = 2/13/2009, address = null, phone = null, state = null 传递过去的时候，得到的查询语句应该是select * from customer where id = 2,照理说应该返回一条记录才对啊，为什么返回的是空表格呢

Answer 6

对了，zjcxc版主，能不能推荐一本sql语言的书给我看，你说的N字符前导符的我都不太懂，想学习下。

Answer 7

birth = 2/13/2009(就是当天的日期)
------------------------------------
现在 14 号了, 你传递的参数是 13 号, 没有满足条件的数据是正常的啊, 如果不管日期, 你可以传递 null 值进去

Answer 8

CREATE TABLE customer(
ID int,
name varchar(50), 
birth datetime, 
address varchar(50), 
phone varchar(50), 
state varchar(5) 
)
INSERT customer
SELECT 1, 'Alen', '1/1/1970 12:00:00 AM', '18231 barroso st', '6019250001', 'AL' UNION ALL
SELECT 2, 'Bob', '2/2/1971 12:00:00 AM', '', '6019250002', 'CA' UNION ALL
SELECT 3, 'Carter', '3/3/1972 12:00:00 AM', '', '6019250003', 'CT'
GOCREATE PROCEDURE sp_displaychosencustomers 
@id int, 
@name varchar(50), 
@birth datetime, 
@address varchar(50), 
@phone varchar(50), 
@state varchar(5) 
AS 
begin 
    DECLARE @str nvarchar(4000); SELECT
@str =
CASE
WHEN @id <> 0 THEN N' AND ID = @id'
ELSE N''
END
+ CASE
WHEN @name IS NOT NULL THEN N' AND NAME = @name'
ELSE N''
END
+ CASE
WHEN DATEDIFF(Day, @birth, GETDATE()) <> 0 THEN N' AND Birth = @birth'
ELSE N''
END
+ CASE
WHEN @address IS NOT NULL THEN N' AND ADDRESS = @address'
ELSE N''
END
+ CASE
WHEN @phone IS NOT NULL THEN N' AND PHONE = @phone'
ELSE N''
END
+ CASE
WHEN @state IS NOT NULL THEN N' AND STATE = @state'
ELSE N''
END;
IF @str <> N''
SET @str = STUFF(@str, 1, 4, N' WHERE ');
SET @str = N'select * from customer ' + @str;

EXEC sp_executesql
@str,
N'
@id int, 
@name varchar(50), 
@birth datetime, 
@address varchar(50), 
@phone varchar(50), 
@state varchar(5) 
',
@id, @name, @birth, @address, @phone, @state;
end
GOEXEC sp_displaychosencustomers 
@id = 2, 
@name = NULL,
@birth = NULL,
@address = NULL,
@phone = NULL,
@state = NULL

EXEC sp_displaychosencustomers 
@id = 2, 
@name = NULL,
@birth = '2/14/2009',
@address = NULL,
@phone = NULL,
@state = NULL
GODROP PROC sp_displaychosencustomers;
DROP TABLE customer;

Answer 9

上述测试的两种调用方式都返回下述结果(所以估计问题出在你的调用上)
2 Bob 1971-02-02 00:00:00.000 6019250002 CA

Answer 10

请问这句是什么意思？EXEC sp_executesql
        @str,
        N'
            @id int, 
            @name varchar(50), 
            @birth datetime, 
            @address varchar(50), 
            @phone varchar(50), 
            @state varchar(5) 
        ',
        @id, @name, @birth, @address, @phone, @state;
end
GO

Answer 11

楼主看看联机帮助上关于 sp_executesql 的说明吧

Answer 12

我运行了在别的数据库里运行了你的代码，确实是对的。我是在sql server里右键sp_displaychosencustomers这个stored procedure,然后点执行，这时候会跳出来一个Execute Procedure [dbo].[sp_displaychosencustomers]的对话框，然后我就在Value这列里分别输入了id是2，其他都为NULL,然后点确定，
出来的结果要么是说：
Msg 8114, Level 16, State 5, Procedure sp_displaychosencustomers, Line 0
从数据类型 nvarchar 转换为 datetime 时出错。
(1 row(s) affected)要么就是返回一个空的表格。

Answer 13

USE [RoadsideMart]
GODECLARE @return_value intEXEC @return_value = [dbo].[sp_displaychosencustomers]
@id = 2,
@name = N'null',
@birth = N'null',
@address = N'null',
@phone = N'null',
@state = N'null'SELECT 'Return Value' = @return_valueGO

Answer 14

晕倒, 脚本明显不对嘛, 下面这样才是 NULL 值嘛USE [RoadsideMart] 
GO DECLARE @return_value int EXEC @return_value = [dbo].[sp_displaychosencustomers] 
@id = 2, 
@name = NULL, 
@birth = NULL, 
@address = NULL, 
@phone = NULL, 
@state = NULL SELECT 'Return Value' = @return_value GO

Answer 15

楼主的sql server基础知识似乎很不够, 有空的时候多翻翻联机帮助

Answer 16

使用 Unicode 数据类型定义参数可以保证客户端请求或输入在服务器上隐式转换为 Unicode 格式，并在处理中不被损坏。如果将参数指定为 OUTPUT 参数，Unicode 类型也可以在其返回到客户端时使损坏的几率降到最低程度。

Answer 17

那在Execute Procedure [dbo].[sp_displaychosencustomers]的对话框里，我应该怎么输入空值呢？

Answer 18

是不是在Execute Procedure [dbo].[sp_displaychosencustomers]的对话框里选那儿"PASS NULL VALUE"?我在ID里输入2，其余的选项都选PASS NULL VALUE,得到的脚本是：USE [RoadsideMart]
GODECLARE @return_value intEXEC @return_value = [dbo].[sp_displaychosencustomers]
@id = 2,
@name = NULL,
@birth = NULL,
@address = NULL,
@phone = NULL,
@state = NULLSELECT 'Return Value' = @return_valueGO但是错误信息说：
Msg 208, Level 16, State 1, Line 8
对象名  'Customer' 无效。
这是怎么回事啊？

Answer 19

NULL值当然是选"PASS NULL VALUE"
对象名无效, 那是你自己要解决的问题, 我怎么知道你的'Customer'表在那

Answer 20

晕，CUSTOMER 表被我删了。。不知道什么时候删掉了。

Answer 21

太感谢你了，zjcxc。程序能运行了，谢谢你给了我这么多帮助还有建议，把这100分都给你吧，再次谢谢了。