1

I have following problem formatting the @p_f_field variable correct, I get the error:

Could not execute statement.
Incorrect syntax near ‘+’
Sybase error code=102, SQLState=”42000”
Severity Level=15, State=181, Transaction State=1
Line 7

My stored procedure:

create proc dbo.sp_m_efi_dw_full_dsa_table_load_im ( -- parameter examples:
@p_skm_name varchar(4096),
@p_usr_name varchar(4096),
@p_t_name_dsa varchar(4096),
@p_t_name_bas varchar(4096),
@p_f_name varchar(4096),
@p_f_field varchar(4096)
)
as begin
declare @sql varchar(4096)
if @p_skm_name is not null
begin
if @p_usr_name is not null
begin
set @sql='TRUNCATE TABLE '+@p_skm_name+'.'+@p_usr_name+'.'+@p_t_name_dsa
exec (@sql)
end
end
set @sql='INSERT INTO '+@p_skm_name+'.'+@p_usr_name+'.'+@p_t_name_dsa+' '+@p_f_name+
' VALUES '+@p_f_field
exec (@sql)
end

My call to the stored procedure:

execute BAS_efi.dbo.sp_m_efi_dw_full_dsa_table_load_im
@p_skm_name         = 'B_ef',
@p_usr_name         = 'dbo',
@p_t_name_dsa       = 'a_log',
@p_t_name_bas       = 'a_log',
@p_f_name           = '(NEWFIELD1)',
@p_f_field          = '('+char(39)+'daf9af01-6bc2-11e-b23182b0623e'+char(39)+')'

Any suggestions on how to format the variable @p_f_field correct, or any others suggestions on how to execute this simple INSERT INTO procedure?

nesmoht
  • 125
  • 8
  • Can you print out the @sql statements before they are executed? Might help. – Mike Gardner May 14 '13 at 14:26
  • Can you do the +char(39) padding in the procedure, instead of the call? – Mike Gardner May 14 '13 at 14:59
  • This is working fine: INSERT INTO B_ef.dbo.a_log (NEWFIELD1) VALUES (char(39)+'daf9af01-6bc2-11e-b23182b0623e'+char(39)) – nesmoht May 14 '13 at 15:08
  • It works for me to, until I put the `+char(39)` in the @p_f_field. It also works when I use `'VALUES '+char(39)+@p_f_field+char(39)` – Mike Gardner May 14 '13 at 15:58
  • Ok thanks this works for me if I put this:`... VALUES '+char(40)+char(39)+@p_f_field+char(39)+char(41)` **BUT!** when I want to put more strings inside `@p_f_field` I get problems with the formating ex. `@p_f_field = 'daf','deb'` any ideas will be very much appreciated:) – nesmoht May 15 '13 at 09:32

0 Answers0