Stored Procedure Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Stored Procedure Help

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 05:32:32

Dear team I am Trying to write to a stored procedure which will insert a row in the table and retrives the idendity.My code is


create procedure SP_UPDATEFILE 
@user_id nvarchar(10),
@journal_code nvarchar(50),
@stage nvarchar(10),
@process nvarchar(10),
@file_name nvarchar(200),
@file_size nvarchar(50),
@file_Dt datetime(50),
@Status nvarchar(10) 
as 
insert into processdet ('user_id','Journal_code','stage','process','file_name','file_size','file_dt''status') values (@ g_userid ,@JournalCode, @stage,@process,@file_name,@file_size,@file_date,@status) select @@identity as 'identity'

Here I am getting an Error message that @Declare varraible.SOme body help please.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 05:36:39

Well, you missed a comma between 'file_dt' and 'status' column definitions.
Also, you have an extra space in the variable name for @ g_userid.

Whenever you get an error like this, try to rewrite the code for easier reading, like this example.

create procedure SP_UPDATEFILE
(
	@user_id nvarchar(10),
	@journal_code nvarchar(50),
	@stage nvarchar(10),
	@process nvarchar(10),
	@file_name nvarchar(200),
	@file_size nvarchar(50),
	@file_Dt datetime(50),
	@Status nvarchar(10)
)
as 

insert	processdet
	(
		'user_id',
		'Journal_code',
		'stage',
		'process',
		'file_name',
		'file_size',
		'file_dt',
		'status'
	)
values	(
		@g_userid,
		@JournalCode,
		@stage,
		@process,
		@file_name,
		@file_size,
		@file_date,
		@status
	)

select	@@identity as 'identity'

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 05:38:22

There are couple of mistakes in the Insert statement:

insert into processdet ('user_id','Journal_code','stage','process','file_name','file_size','file_dt''status') 
values (@user_id ,@journal_code, @stage,@process,@file_name,@file_size,@file_Dt,@status)

Parameter definitions and the variables supplied in insert statement should match...which is not happening in your case (check BOLD text)

Also use SCOPE_IDENTITY() as it is much proper !

Harsh Athalye
India.
"Nothing is Impossible"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 05:41:08

Dear Peso,
Thanks for the code.I am going to test that one.In the mean time I want to a insert value in a the selected column with the update command .How to write a stored procedure for that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 05:43:09

[code]UPDATE YourTable
SET YourColumn = SomeNewValue
WHERE SomeOtherColumnPreferrabyPkColumn = aUniqueValue[/code]or[code]UPDATE YourTable
SET YourColumn = SomeNewValue
WHERE SomeOtherColumn = aValue
AND aSecondColumn = bValue[/code]
Peter Larsson
Helsingborg, Sweden

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 05:44:27

Thanks Peso,
I will try and come back.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 05:45:55

Don't forget to look at Harsh's suggestion, that you write the variable names correctly!

Peter Larsson
Helsingborg, Sweden

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 08:40:09

Dear Peter,
I am using the following sp to insert a row.


CREATE procedure SP_UPDATEFILE
	@user_id nvarchar(10),
	@journal_code nvarchar(50),
	@stage nvarchar(10),
	@process nvarchar(10),
	@file_name nvarchar(200),
	@file_size nvarchar(50),
	@file_Dt datetime,
	@Status nvarchar(10),
	@batch nvarchar(50)

as 
begin
set nocount on
insert into process_det(user_id,Journal_code,stage,process,file_name,file_size,file_dt,status,batch)
values	(
		@user_id,
		@Journal_Code,
		@stage,
		@process,
		@file_name,
		@file_size,
		@file_dt,
		@status,
		@batch
	)

select	@@identity  as 'identity'
end
set nocount  off
GO

and I am calling this stored procedure with this (vb)

cmd.Parameters.Append cmd.CreateParameter("file_dt", adDBDate, adParamInput, 10, G_FileDateTime)

But I am getting an error msg that Error in coverting nvarchar to datetime.
I have declared the G_FileDatetime as Date in my VB Program.
Please Help.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-05 : 08:48:08

Are you sure first file_dt, is the date time column?? and
Secondly in what format you are sending your date, try to send the date in yyyymmdd foramt..

Just check whether your sp is working from QA?

Chirag

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 08:50:40

yes I am sure that the file_dt is sql datetime varriable. and I am stroring the values as
dd/mm/yyyy 12:16:10 AM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 08:51:31

quote:
Originally posted by danasegarane76

But I am getting an error msg that Error in coverting nvarchar to datetime.
I have declared the G_FileDatetime as Date in my VB Program.
Please Help.

What are the actual value of G_FileDatetime variable? Also why you are specifying size parameter for a datetime parameter?

Harsh Athalye
India.
"Nothing is Impossible"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 08:54:53

The Actual Value of the G_FileDatetime in this event is "
8/28/2006 2:07:10 PM "

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 08:55:54

Try to use adDate instead, and no parameter length. Let ADO decide this self.

cmd.Parameters.Append cmd.CreateParameter("file_dt", adDate, adParamInput, , cdate(G_FileDateTime))

Peter Larsson
Helsingborg, Sweden

EDIT: Spelling...

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-05 : 08:56:57

As soon as I got the error I tried that also and even timestamp also.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 09:31:41

Did you try my last suggestion?

Peter Larsson
Helsingborg, Sweden

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-06 : 00:06:48

Dear Peter,
I am getting the same error message.


cmd.Parameters.Append cmd.CreateParameter("file_dt", adDate, adParamInput, 10, CDate(G_FileDateTime))

In this case the original Input is


8/28/2006 2:07:10 PM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 01:01:30

Do you think you can drop the "10" part?
And what datatype do your FILE_DT column have?

Peter Larsson
Helsingborg, Sweden

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-06 : 06:11:13

Now I have droped that 10.The File_Dt is SQL Datetime Type.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 06:28:52

Now, where do you have an error?

Peter Larsson
Helsingborg, Sweden

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-06 : 06:30:22

the same error as stated above

danasegarane76
Posting Yak Master

242 Posts

Posted - 2006-09-06 : 06:45:24

I am getting the error msg Error Coverting Varchar to Datetime

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources