SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issue on Insert datas in Dynamic Query


Issue on Insert datas in Dynamic Query

Author
Message
lefrancisco1
lefrancisco1
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 79
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
Mike John
Mike John
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 5984
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search