Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue on Insert datas in Dynamic Query Expand / Collapse
Author
Message
Posted Monday, February 7, 2011 1:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 15, 2012 1:15 PM
Points: 14, Visits: 66
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
Post #1059399
Posted Monday, February 7, 2011 2:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:33 AM
Points: 2,667, Visits: 5,840
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



Post #1059410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse