sqlserver 預存程序 增加

標籤：date   from   into   字串類型   單引號   訊息   sage   存在   code   

親測可用

CREATE PROCEDURE [dbo].[InsertMessage](@strTable varchar(50),         --表名@strValues nvarchar(1000),     --要插入的資料（用英文逗號分隔）,如果是字串類型，需加單引號@only_field varchar(20)=NULL,  --唯一性欄位(列名)@only_value varchar(20)=NULL,  --唯一性欄位值@msg nvarchar(50)=NULL         --錯誤訊息)asBEGIN SET NOCOUNT ON;declare @sqlString varchar(5000);IF @only_field is not null    begin        declare @p int;                    --查詢唯一性結果        declare @sql nvarchar(1000);       --拼接查詢sql字串        set @sql=‘SELECT @p=count(1) FROM ‘ [email protected]+‘ WHERE ‘ [email protected]_field+‘=‘+@only_value;        exec sp_executesql @sql,N‘@p AS int OUTPUT‘,@p OUTPUT            if @p > 0            begin                raiserror(@msg ,16,1)                return            end        else            begin                set @sqlString = ‘insert into ‘[email protected]+‘ values (‘[email protected]+‘)‘;                exec(@sqlString);            end    endELSE    begin        set @sqlString = ‘insert into ‘[email protected]+‘ values (‘[email protected]+‘)‘;        exec(@sqlString);    endEND--execute [dbo].[InsertMessage] ‘base._Member‘,"0,2,getdate(),0,1,‘4445‘,‘654321‘,‘654321‘,N‘鄒柯00‘",‘AcctNbr‘,‘4445‘,N‘會員卡號已存在,不能重複添加!‘--execute [dbo].[InsertMessage] ‘base._Member‘,"0,2,getdate(),0,1,‘4445‘,‘654321‘,‘654321‘,N‘鄒柯00‘",NULL,NULL,NULL

 

sqlserver 預存程序 增加