求一存储过程写法

[骑猪看海]

CREATE PROCEDURE [dbo].[sp_Users_List] 
@userName nvarchar(50),
@realName  nvarchar(50)
AS 
BEGIN  
 SELECT top 10 * FROM Users where1=1  ?  and id not in(select top 0 from Users where1=1 ?)  

@userName 和 @realName是 查询条件

本来应该这样写的：
SELECT top 10 * FROM Users where1=1 and userName=@userName and realName=@realName and id not in(select top 0 from Users where and userName=@userName and realName=@realName)

但有时传进来的可能是空，所以不能写死，值为空时and条件应该去除，不然就找不出数据，应该怎么写。
以前一直不用存储过程，直接在程序中用字符串把条件拼起来的，现在用下就不知道怎么做了，各位看看怎么应该做

[yqyqyoyo]

写成 and (? or @userName='') 后面一样

[Xiaoyuan245437]

可以在程序里面做判断吗 不一定要一条sql语句拉
if @realName is null
....
else
...

[hexiaoweiy]

在里面写if语句判断段，每一个下面都要有begin end

[yqyqyoyo]

SELECT top 10 * FROM Users where1=1 and (userName=@userName or @userName='') and (realName=@realName or @realName = '') and id not in(select top 0 from Users where and (userName=@userName or @userName = '') and (realName=@realName or @realName='')

改成这样就可以了。
自己去试一下吧。

[骑猪看海]

[Quote=引用 3 楼 hexiaoweiy 的回复:]
在里面写if语句判断段，每一个下面都要有begin end
[/Quote]

这方法我早试过，报错
declare @a nvarchar(100)
set @a=''
select * from [user] where 1=1 if @a<>'' begin username =@a end

[骑猪看海]

[Quote=引用 5 楼 yqyqyoyo 的回复:]
SQL code

SELECT top 10 * FROM Users where1=1 and (userName=@userName or @userName='') and (realName=@realName or @realName = '') and id not in(select top 0 from Users where and (userName=@userName ……
[/Quote]

select * from [user] where (username='' or username='')

试了，我前一个传过来是空 ，一条记录都没有了

[hexiaoweiy]

可以转化一下，oracle有一个nvl吧，sqlservice应该有一个isnull之类的吧，把null转化成''

[wuyq11]

declare @sql varchar(1000)
set @sql='SELECT top 10 * FROM Users where 1=1'
if(@userName<>'')
set @sql=@sql+ ' and username='''+@username+''''
分页存储过程
http://topic.csdn.net/u/20091204/21/722689e1-7824-497c-b709-4b1118264633.html

[guozhaoyou1]

CREATE PROCEDURE [dbo].[sp_Users_List] 
@userName nvarchar(50),
@realName  nvarchar(50)
AS 
declare @sql varchar(500)
declare @where varchar(2000)
set @where='1=1'
if(@userName is not null and @userName !='')
	set @where=@where+'and clumn0='+@userName
if(@realName is not null and @realName !='')
	set @where=@where+'and clumn1='+@realName
set @sql='SELECT top 10 * FROM Users '+@where
exec @sql

[AuC]

转化一下

[pt1314917]

CREATE PROCEDURE [dbo].[sp_Users_List] 
@userName nvarchar(50),
@realName  nvarchar(50)
AS 
	declare @where varchar(200)
	set @where =''
	set @where =@where+ case isnull(@username,'') when '' then '' else ' and userName='''+@userName+'''' end
	set @where =@where+ case isnull(@realName,'') when '' then '' else ' and realName='''+@realName+'''' end
	exec('SELECT top 10 * FROM Users where 1=1 '+@where+' and id not in(select top 0 id from Users where 1=1 '+@where+'))')

[linhaoran0010]

CREATE PROCEDURE [dbo].[sp_Users_List]
@userName nvarchar(50),
@realName nvarchar(50)
AS
declare @sql varchar(500)
declare @where varchar(2000)
set @where='1=1'
if(@userName is not null and @userName !='')
set @where=@where+'and clumn0='+@userName
if(@realName is not null and @realName !='')
set @where=@where+'and clumn1='+@realName
set @sql='SELECT top 10 * FROM Users '+@where
exec @sql

[骑猪看海]

上面的都是高手，明天试下。看来自己真应该学习下存储过程，写了半年多程序，全是在程序里拼字符串写的sql语句