October 21, 2010 at 10:49 pm
I know I am missing something I cannot figure out why I am getting this erro
Msg 295, Level 16, State 3, Procedure AddRma, Line 16
Conversion failed when converting character string to smalldatetime data type.
CREATE TABLE [dbo].[RMA](
[RMAID] [int] IDENTITY(1,1) NOT NULL,
[Store] [int] NOT NULL,
[OriginalSR] [varchar](32) NULL,
[RMACreated] [smalldatetime] NULL,
[Make] [varchar](128) NULL,
[Model] [varchar](128) NULL,
[Serial] [varchar](128) NULL,
[Asset] [varchar](128) NULL,
[StoreTrack] [varchar](128) NULL,
[DepotTrack] [varchar](128) NULL,
[RMAClosed] [smalldatetime] NULL,
[RMADetails] [varchar](2048) NULL
) ON [PRIMARY]
alter procedure [dbo].[AddRma]
@rmadetails varchar(2048), @storeid int, @originalsr varchar (32), @make varchar(128), @model varchar (128), @serial varchar (128),
@asset varchar (128), @storetrack varchar (128), @DepotTrack varchar (128), @RMAClosed smalldatetime
as
set nocount on
declare @created smalldatetime
if @rmaclosed = '' begin select @rmaclosed = null end
select @created = getdate()
Insert rma
select store = @storeid, originalsr = @originalsr,
make = @make, model = @model, serial = @serial, asset = @asset, storetrack = @storetrack, depottrack = @DepotTrack, rmaclosed = @RMAClosed,
rmadetails = @rmadetails, rmacreated = @created
exec [AddRma] @storeid = '0',
@originalsr = 'Original SR Here', @make = 'Make', @model = 'Model',
@serial = 'Serial', @asset = 'Asset', @storetrack = 'StoreTrack',
@depottrack = 'DepotTrack', @rmaclosed = '',
@rmadetails = 'Enter details of this long running service request here.'
I have been able to determine that the @rmadetails variable is the problem (I just starting putting in dates into the variables until I figured out which one was doing it). Alas unless I put a date as the @rmadetails I get the error.
October 21, 2010 at 11:21 pm
The issue is with your "insert" statement.. If you do not provide the INSERT statement with the column names, it will insert the values as how it is in the table. So change your INSERT statement to have the column names explicitly mentioned as below
IF OBJECT_ID ( 'dbo.AddRMA', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.AddRMA;
GO
CREATE PROCEDURE [dbo].[AddRMA]
@rmadetails VARCHAR(2048),
@storeid INT,
@originalsr VARCHAR (32),
@make VARCHAR(128),
@model VARCHAR (128),
@serial VARCHAR (128),
@asset VARCHAR (128),
@storetrack VARCHAR (128),
@DepotTrack VARCHAR (128),
@RMAClosed SMALLDATETIME
AS
SET NOCOUNT ON
DECLARE @created SMALLDATETIME
IF @rmaclosed = ''
BEGIN
SELECT @rmaclosed = NULL
END
SELECT @created = getdate()
INSERT rma
(
store ,
originalsr ,
make ,
model ,
serial ,
asset ,
storetrack ,
depottrack ,
rmaclosed ,
rmadetails ,
rmacreated
)
SELECT store = @storeid,
originalsr = @originalsr,
make = @make,
model = @model,
serial = @serial,
asset = @asset,
storetrack = @storetrack,
depottrack = @DepotTrack,
rmaclosed = @RMAClosed,
rmadetails = @rmadetails,
rmacreated = @created
GO
Also, as a side note, you might wanna consider following best practices in creating the SP. SP looks shabby. I have beautified it to some extent; you can add comments, indent the lines, terminate the lines with semi-colons etc etc..
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply