February 7, 2011 at 1:58 am
folks,
I have wrote an SP FOR INSERT ...that too dynamic sql....in some case the values are optional should allow null values...while not giving any values like date...it throws error...could anyone help me out with this task
Dynamic query:
ALTER procedure [dbo].[AO_checkInsert](@FundId as integer=0,@TransType as varchar(50)=null,@InmateId as integer=null,@CashFlag as integer=null,
@TransDesc as nvarchar(150)=null,
@DepoId as integer=null,
@TransAmt as money=null,
@RexedFrom as nvarchar=null,
@createdby as integer,
@CreatedDate as nvarchar(30),
@RecieptNo as nvarchar=null)
as
declare @StrCol as nvarchar(max)=' insert into AccountAO_Receive( '
declare @StrValues as nvarchar(max)=' values( '
if @FundId>0
begin
set @StrCol=@StrCol+' AccountAO_Fund_id, '
set @StrValues=@StrValues+ CAST(@FundId as varchar(4)) + ''','''
end
if @TransType is not null
begin
set @StrCol=@StrCol+' transaction_type, '
set @StrValues=@StrValues+ '''' + CAST(@TransType as varchar(50)) + ''','''
end
if @InmateId>0
begin
set @StrCol=@StrCol+' Inmate_id, '
set @StrValues=@StrValues+ CAST(@InmateId as varchar(4)) + ''','''
end
if @CashFlag>0
begin
set @StrCol=@StrCol+' transaction_receive_cash_flag, '
set @StrValues=@StrValues+ CAST(@CashFlag as varchar(4)) + ''','''
end
if @TransDesc is not null
begin
set @StrCol=@StrCol+' transaction_description, '
set @StrValues=@StrValues+ '' +CAST(@TransDesc as nvarchar(150)) + ''','''
end
if @DepoId>0
begin
set @StrCol=@StrCol+ ' AccountAO_Depository_id, '
set @StrValues=@StrValues+ CAST(@DepoId as varchar(4)) + ''','''
end
if @TransAmt>0
begin
set @StrCol=@StrCol+ ' transaction_amount, '
set @StrValues=@StrValues+ CAST(@TransAmt as varchar(8)) + ''','''
end
if @RexedFrom > 0
begin
set @StrCol=@StrCol+ ' transaction_receive_from, '
set @StrValues=@StrValues+ '' +CAST(@RexedFrom as varchar(50)) + ''','''
end
if @RecieptNo is not null
begin
set @StrCol=@StrCol+ ' transaction_Receipt, '
set @StrValues=@StrValues+'' + CAST(@RecieptNo as varchar(50)) + ''','''
end
if @createdby>0
begin
set @StrCol=@StrCol+ ' created_by, '
set @StrValues=@StrValues+ CAST(@CreatedBy as varchar(4)) + ''','''
end
if @CreatedDate is not null
begin
set @StrCol=@StrCol+ ' create_date ) '
set @StrValues=@StrValues+ '' + CAST(@CreatedDate as varchar(30)) + ''')'
end
exec (@StrCol+@StrValues)
Thanks and Regards,
leo franklin.M
February 7, 2011 at 2:28 am
The simplest would be to remove all the dynamic SQL and just use a simple insert possibly with NULLIFs in ths values clause if the app passes blanks that you want to store as nulls or similar. Is there some reaon youi NEED to manually string the SQL together like this?
Mike
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply