set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go













ALTER PROCEDURE [dbo].[Search_user]
(
    @card_id int,
    @user_id int,
    @user_name varchar(30),
    @phone varchar(30),
    @community_id int,
    @address varchar(50)
)
AS
DECLARE @sql nvarchar(2000)
SET @sql='SELECT     user_id,card_id,user_name,
(SELECT     buy_type
FROM          user_type
WHERE      (user_typeId = jt_user.user_typeId)) AS user_typeId,
(SELECT     unit_price
FROM          user_type
WHERE      (user_typeId = jt_user.price_id)) AS price_id,
(SELECT     community_name
FROM          user_community
WHERE      (community_id = jt_)) AS community_id,
(SELECT     gas_meterName
FROM          gas_meter
WHERE      (ID = jt_user.gas_meterId)) AS gas_meterId,
address,phone,
(SELECT     status_name
FROM         user_status
WHERE      (status_id = jt_user.status_id)) AS status_id,
buygas_num,total_gas,CONVERT(varchar(10), start_date, 120) AS start_date,
(SELECT     documents
FROM         documents_type
WHERE      (documents_id = jt_user.documents_id)) AS documents_id,
documents_number,
(SELECT     manager_name
FROM        manager
WHERE      (manager_id = jt_user.manage_id))
as manage_id,
back
FROM         jt_user
where 1=1'
if(@card_id>0)
begin
 SET @sql=@sql+' and card_id ='+@card_id
end
if(@user_id>0)
begin
 SET @sql=@sql+' and user_id = '+@user_id
end
if(@user_name>0)
begin
 SET @sql=@sql+' and user_name ='+ @user_name
end

EXEC(@sql)
GO

调试提示
消息 245，级别 16，状态 1，过程 Search_user，第 56 行
在将 nvarchar 值 'SELECT     user_id,card_id,user_name,
(SELECT     buy_type
FROM          user_type
WHERE      (user_typeId = jt_user.user_typeId)) AS user_typeId,
(SELECT     unit_price
FROM          user_type
WHERE      (user_typeId = jt_user.price_id)) AS price_id,
(SELECT     community_name
FROM          user_community
WHERE      (community_id = jt_)) AS community_id,
(SELECT     gas_meterName
FROM          gas_meter
WHERE      (ID = jt_user.gas_meterId)) AS gas_meterId,
address,phone,
(SELECT     status_name
FROM         user_status
WHERE      (status_id = jt_user.status_id)) AS status_id,
buygas_num,total_gas,CONVERT(varchar(10), start_date, 120) AS start_date,
(SELECT     documents
FROM         documents_type
WHERE      (documents_id = jt_user.documents_id)) AS documents_id,
documents_number,
(SELECT     manager_name
FROM        manager
WHERE      (manager_id = jt_user.manage_id))
as manage_id,
back
FROM         jt_user
where 1=1 and card_id =' 转换成数据类型 int 时失败。


请问高手怎么解决
我用的sql 2005

SET @sql=@sql+' and card_id ='+@card_id
改成：
SET @sql=@sql+' and card_id ='+convert(nvarchar(10),@card_id)
（余类似）

另：if(@user_name>0)
这个不妥吧？！