CONVERT parameter in SP?

• donato1026

  SSCrazy

  Points: 2765

  More actions

  March 30, 2011 at 9:02 am

  #148794

  Hi again,

  I have a stored procedure that talks to Oracle. I am usnig open query and works well, except that for the date, it needs to be passed as dd-MMM-yy or 30-MAR-11. I am converting the parameters in the SP, but when I go to run it, and if I type 03/07/2011 in the so_wr_dt field, it gives me a ORA-01843: not a valid month" error. Anyone have any ideas? I am doing this because my website will be passing the date this way...

  USE [DataWarehouse]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[usp_website_comments]

  @so_wr_dt varchar(12),

  @so_store_cd varchar(12),

  @so_seq_num varchar(50),

  @deldoc varchar(14)

  AS

  BEGIN

  SET NOCOUNT ON;

  declare @tempSeq TABLE (seq_num int);

  declare @sql as varchar(8000);

  declare @seq_num as varchar(5);

  declare @today as varchar(12);

  -- get the max sequence number for the comments

  SET @sql = ' SELECT * FROM OPENQUERY ( TRAIN, '' SELECT '

  set @sql = @sql + ' max(seq#) + 1 '

  set @sql = @sql + ' FROM SALES.SO_CMNT '

  set @sql = @sql + ' WHERE SO_WR_DT = ''''' + convert(varchar, @so_wr_dt, 106) + ''''''

  set @sql = @sql + ' AND SO_STORE_CD = ''''' + @so_store_cd + ''''''

  set @sql = @sql + ' AND SO_SEQ_NUM = ''''' + @so_seq_num + ''''''

  set @sql = @sql + ' '' ) '

  insert @tempSeq

  exec ( @sql ) ;

  set @seq_num = (select max(seq_num) from @tempSeq);

  SET @today = convert ( varchar , getDate(), 106);

  -- insert a comment in the SO_CMNT table

  set @sql = ' INSERT OPENQUERY ( TRAIN, '' SELECT so_wr_dt, so_store_cd, so_seq_num, seq#, dt, text, del_doc_num, perm, cmnt_type, emp_cd FROM SALES.SO_CMNT '

  set @sql = @sql + ' WHERE so_wr_dt = ''''' + convert(varchar, @so_wr_dt, 106) + ''''''

  set @sql = @sql + ' AND so_store_cd = ''''' + @so_store_cd + ''''''

  set @sql = @sql + ' AND so_seq_num = ''''' + @so_seq_num + ''''''

  set @sql = @sql + ' '' ) '

  set @sql = @sql + ' VALUES(''' + convert(varchar, @so_wr_dt, 106) + ''',''' + @so_store_cd + ''',''' + @so_seq_num + ''', ' + @seq_num + ', ''' + @today + ''', ''DELIVERY SCHEDULED'',''' + @deldoc + ''', ''Y'', ''D'', ''WEBSITE'')'

  exec ( @sql ) ;

  END

  Now, it's ony the so_wr_dt field... The @today parameter works fine....

  Any help will be greatly appreciated,

  ~D

• donato1026

  SSCrazy

  Points: 2765

  More actions

  March 30, 2011 at 9:14 am

  #1305494

  I figured it out... I feel silly....

  I didn't declare my @so_wr_dt parameter as a datetime.

  It works now!