关于MSSQL存储过程

来源：百度知道 编辑：UC知道 时间：2024/05/12 05:02:52
是更新记录，由于有个字段是image属性的，而我sql语句是写在字符串里的，但是image属性不支持，语句是这样的
create procedure IH_SET_CUSTMER_NEED
(
@id int=null,
@Specialties int=null,
@Specialties_text varchar(1000)=null,
@Response_type varchar(20)=null,
@Response_Image image=null,
@Status_ID int=null,
@Notest varchar(1000)=null,
@Finishid int=null
)
as
declare @strsql varchar(1000)

select @strsql='update CUSTMER_NEED set'

if @Specialties is not null
select @strsql=@strsql+' specialtied='+str(@Specialties)+' and Specialties_text='+@Specialties_text+''

if @Response_type is not null
select @strsql=@strsql+' and Response_type='+@Response_type+' and Response_Image='+@Response_Image+''

if @Status_ID is not null
select @strsql=@strsql+' and Status_ID='+str(@Status_ID)+''

if @Notest is not null
select @strsql=@strsql+'

使用参数方式调用存储过程就行了

<%
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = GetConn(); '数据库连接
Command1.CommandText = "dbo.xp_dgdb_Service"; '存储过程名
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParameter("@RETURN_VALUE", 3, 4)); '以下添加参数
Command1.Parameters.Append(Command1.CreateParameter("@P_orgAddr", 200, 1,21,Command1__P_orgAddr));
Command1.Parameters.Append(Command1.CreateParameter("@p_destAddr", 200, 1,21,Command1__p_destAddr));
Command1.Parameters.Append(Command1.CreateParameter("@p_servicecode", 200, 1,20,Command1__p_servicecode));
Command1.Parameters.Append(Command1.CreateParameter("@p_msg", 200, 1,200,Command1__p_msg));
Command1.Parameters.Append(Command1.CreateParameter("@LinkId", 200, 1,20,Command1__LinkId));