February 7, 2011 at 1:35 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 5:00 am
First of all, why are you writing a dynamic procedure for this?
The standard INSERT statement allows for nulls anyway, so the procedure could just be a single line:-
INSERT AccountAO_Receive (AccountAO_Fund_id, transaction_type, .....
VALUES (@FundId, @TransType,....
To answer your question about the problem with your stored procedure, your are inconsistent in the way you are putting quotes around the parameter values e.g. you aren't supplying an opening quote for @FundId. But on the same line, you are supplying the opening quote for the following parameter. If the following parameter is @Transtype, you are supplying yet another opening quote.
Supplying an opening quote for the next parameter will also cause a problem where the rest of the parameters are null... you will have already supplied opening quotes for a non existent parameter.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply