SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on Insert Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mayoorsubbu
Yak Posting Veteran

India
94 Posts

Posted - 07/14/2010 :  22:44:33  Show Profile  Reply with Quote
Hi Folks,
I have a table RequestMaster

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[requestmaster](
	[requestid] [nchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[unit] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[requestno] [nchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[requestdt] [datetime] NOT NULL,
	[recddt] [datetime] NOT NULL,
	[regndt] [datetime] NOT NULL,
	[remarks] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_requestmaster] PRIMARY KEY CLUSTERED 
(
	[requestid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


I have created a SP like so :

IF OBJECT_ID ('dbo.InsertRequestMaster') IS NOT NULL
    DROP PROCEDURE dbo.InsertRequestMaster;
go
create procedure InsertRequestMaster
@cUnit nchar(10),
@cRequestno nchar(75),
@dRequestDt varchar (25),
@dRecdDt varchar (25),
@dRegnDate varchar (25),
@cRemarks varchar(100) = NULL
--@cRequestType nchar (4),
--@nAfQty int,
--@nAeQty int
AS
BEGIN
	DECLARE @cNextId nchar(8)
	DECLARE @RequestDt datetime
	DECLARE @RecdDt datetime
	DECLARE @RegnDt datetime

	Select @RequestDt = convert(varchar(25), @dRequestDt, 105)
	Select @RecdDt = convert(varchar(25), @dRecdDt, 105)
	Select @RegnDt = convert(varchar(25), @dRegnDate, 105)
	SELECT @cNextId= dbo.GetId(GetDate())
	
Print @cNextId
Print '------------'
Print @dRequestDt
--Print @RequestDt
Print '------------'
Print @dRecdDt
--Print @RecdDt
Print '------------'
Print @dRegnDate
--Print @RegnDt
Print '------------'
	SET XACT_ABORT ON;
	BEGIN TRANSACTION;
		
		--BEGIN TRY
			INSERT INTO RequestMaster (RequestId, Unit, RequestNo, RequestDt, RecdDt, RegnDt, Remarks) 
			VALUES (@cNextId, @cRequestno, @cUnit, @RequestDt, @RecdDt, @RegnDt, @cRemarks);
			
			UPDATE IdMaster set LastId = @cNextId
		--END TRY
		--BEGIN CATCH
		--	IF (XACT_STATE()) = -1
		--		Rollback transaction
		--	IF (XACT_STATE()) = 1
		--		Commit transaction
	    --END CATCH;
END



When I insert a record like below it works

Insert into RequestMaster 
	(requestid, unit, requestno, requestdt, recddt, regndt) values ('20000004','30 WG', '30 WG/LGS/1234/GIFTING', '12-12-2010','12-12-2010', '12-12-2010')


Howevr the SP generates an error

exec InsertRequestMaster
'30 WG', '30 WG/LGS/1234/GIFTING', '12-12-2010','12-12-2010' , '12-12-2010'


Error : Msg 8152, Level 16, State 13, Procedure InsertRequestMaster, Line 41
String or binary data would be truncated.


Help

Thanks

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 07/14/2010 :  23:11:25  Show Profile  Reply with Quote
your update statement
"UPDATE IdMaster set LastId = @cNextId"
is missing the "FROM Mytable" part.

when you are making insert, make sure that the values you want to insert into table have the same column type.
for example: your second column in the table is UNIT nchar(10), whereas from procedure you want to pass the variable into this column with type: @cRequestNo nchar(75).

go through all the columns definitions!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/14/2010 :  23:21:52  Show Profile  Reply with Quote

INSERT INTO RequestMaster (RequestId, Unit, RequestNo, RequestDt, RecdDt, RegnDt, Remarks) 
VALUES (@cNextId, @cRequestno, @cUnit, @RequestDt, @RecdDt, @RegnDt, @cRemarks);



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/14/2010 :  23:23:17  Show Profile  Reply with Quote
quote:
Originally posted by slimt_slimt

your update statement
"UPDATE IdMaster set LastId = @cNextId"
is missing the "FROM Mytable" part.


That is a valid syntax


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000