存储过程 通过 字符串拼接的问题

 

--update the storage
ALTER PROCEDURE [dbo].[sp_XXX_Insert]
@tbAgency as nvarchar(256),
@identifier as nvarchar(256),
@sender as nvarchar(256),
@password as nvarchar(256),
@sent as nvarchar(256),
@status as nvarchar(256),
@scope as nvarchar(256),
@restriction as nvarchar(256),
@msgType as nvarchar(256),
@incidents as nvarchar(256),
@source as nvarchar(256),
@info_category as nvarchar(256),
@info_event as nvarchar(256),
@info_urgency as nvarchar(256),
@info_severity as nvarchar(256),
@info_senderName as nvarchar(256),
@info_certainty as nvarchar(256),
@info_headline as nvarchar(256),
@info_description as nvarchar(256),
@info_eventCode_NC4_NIMC as nvarchar(256),
@correlation_id as nvarchar(256),
@row_status as bit,
@ret_value as int,
@parameter_Magnitude as nvarchar(256),
@parameter_TSCategory as nvarchar(256),
@parameter_Actor as nvarchar(256),
@parameter_Contained as nvarchar(256),
@parameter_AcresBurned as nvarchar(256),
@cs_row as nvarchar(256),
@info_eventCode_NC4_GFP as nvarchar(256),
@info_resource_resourceDesc as nvarchar(256),
@info_resource_uri as nvarchar(256),
@info_resource_mimeType as nvarchar(256),
@info_resource_size as nvarchar(256),
@info_resource_derefURI as nvarchar(256),
@info_specialEvent as nvarchar(256)

AS
BEGIN
declare @varRowStatus nvarchar(256)
declare @varRetValue nvarchar(256)
declare @sql nvarchar(max)

if(@row_status IS NULL)
begin
set @varRowStatus=N'NULL'
end
else
begin
set @varRowStatus=cast(@row_status as nvarchar)
end

if(@ret_value IS NULL)
begin
set @varRetValue=N'NULL'
end
else
begin
set @varRetValue=cast(@ret_value as nvarchar)
end

set @sql = 'INSERT INTO ' + @tbAgency+ ' ([identifier],[sender],[password],[sent],[status],[scope],[restriction],[msgType],[incidents],[source],[info_category],[info_event],[info_urgency],[info_severity],[info_senderName],[info_certainty],[info_headline],[info_description],[info_eventCode_NC4_NIMC],[correlation_id],[row_status],[ret_value],[parameter_Magnitude],[parameter_TSCategory],[parameter_Actor],[parameter_Contained],[parameter_AcresBurned],[cs_row],[info_eventCode_NC4_GFP],[info_resource_resourceDesc],[info_resource_uri],[info_resource_mimeType],[info_resource_size],[info_resource_derefURI],[info_specialEvent]) VALUES('''
+ @identifier + ''', ''' + ISNULL(@sender,'NULL') + ''', '''+ISNULL(@password,'NULL')+''', '''+ISNULL(@sent,'NULL')+''', '''+ISNULL(@status,'NULL')+''', '''+ISNULL(@scope,'NULL')+''', '''+ISNULL(@restriction,'NULL')+''', '''
+ISNULL(@msgType,'NULL')+''', '''+ISNULL(@incidents,'NULL')+''', '''+ISNULL(@source,'NULL')+''', '''+ISNULL(@info_category,'NULL')+''', '''+ISNULL(@info_event,'NULL')+''', '''+ISNULL(@info_urgency,'NULL')+''', '''
+ISNULL(@info_severity,'NULL')+''', '''+ISNULL(@info_senderName,'NULL')+''', '''+ISNULL(@info_certainty,'NULL')+''', '''+ISNULL(@info_headline,'NULL')+''', '''+ISNULL(@info_description,'NULL')+''', '''
+ISNULL(@info_eventCode_NC4_NIMC,'NULL')+''', '''+ISNULL(@correlation_id,'NULL')+''', '+@varRowStatus+', '+@varRetValue+', '''+ISNULL(@parameter_Magnitude,'NULL')+''', '''
+ISNULL(@parameter_TSCategory,'NULL')+''', '''+ISNULL(@parameter_Actor,'NULL')+''', '''+ISNULL(@parameter_Contained,'NULL')+''', '''+ISNULL(@parameter_AcresBurned,'NULL')+''', '''+ISNULL(@cs_row,'NULL')+''', '''
+ISNULL(@info_eventCode_NC4_GFP,'NULL')+''', '''+ISNULL(@info_resource_resourceDesc,'NULL')+''', '''+ISNULL(@info_resource_uri,'NULL')+''', '''+ISNULL(@info_resource_mimeType,'NULL')+''', '''
+ISNULL(@info_resource_size,'NULL')+''', '''+ISNULL(@info_resource_derefURI,'NULL')+''', '''+ISNULL(@info_specialEvent,'NULL')+''')'
--print(@sql)
exec(@sql)
END