Problems creating stored procedure

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

General SQL Server Forums

New to SQL Server Programming

Problems creating stored procedure

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2009-12-15 : 04:23:54

Hi all,

I'm struggling with the concept of stored procedures, and was hoping I could get some help from you guys here.

I have the following DB:

USE [xxxWebserv]
GO
/****** Object:  Table [dbo].[kundeservice_lopenummer]    Script Date: 12/15/2009 10:20:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kundeservice_lopenummer](
	[Lopenummer] [bigint] IDENTITY(1,1) NOT NULL,
	[Operator] [bigint] NOT NULL,
	[Kundenummer] [int] NULL,
	[utfall] [nvarchar](50) NULL,
	[Tidspunkt] [datetime] NOT NULL
) ON [PRIMARY]

What I need to do is enter an Operator and Time, and return the Lopenummer (ID) back to the caller. I then need to insert the rest when the user has completed the wizard.

I've tried several, FAILED, versions of my procedure, and this is my current (still failing) version:

USE [viasatWebserv]
GO
/****** Object:  StoredProcedure [dbo].[cd_insert_lopenummer]    Script Date: 12/15/2009 09:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cd_insert_lopenummer]
 @operator bigint,
 @tidspunkt datetime,
@kundenummer int,
@utfall nvarchar(50)

AS
	INSERT INTO [xxxWebserv].[dbo].[kundeservice_lopenummer]

DECLARE @lopenummer bigint;
     VALUES
           (@operator
           ,@kundenummer
           ,@utfall
           ,@tidspunkt) SELECT SCOPE_IDENTITY()

return  lopenummer;

Anyone?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-15 : 04:27:58

Change the Insert as below

INSERT INTO [xxxWebserv].[dbo].[kundeservice_lopenummer]
VALUES
(@operator
,@kundenummer
,@utfall
,@tidspunkt)

SELECT SCOPE_IDENTITY() as lopenummer

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-15 : 04:29:36

hi

Try this...



/****** Object:  StoredProcedure [dbo].[cd_insert_lopenummer]    Script Date: 12/15/2009 09:26:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cd_insert_lopenummer]
		@operator bigint,
		@tidspunkt datetime,
		@kundenummer int,
		@utfall nvarchar(50)

AS
BEGIN
	INSERT INTO [xxxWebserv].[dbo].[kundeservice_lopenummer]
     VALUES
           (@operator
           ,@kundenummer
           ,@utfall
           ,@tidspunkt) 

	SELECT	SCOPE_IDENTITY() as lopenummer

END

-------------------------
R...

tiwas
Starting Member

37 Posts

Posted - 2009-12-15 : 04:32:11

Thanks for quick replies! Will try at once :)

tiwas
Starting Member

37 Posts

Posted - 2009-12-15 : 04:38:58

BTW: would it be possible to insert the current datetime upon executing the stored procedure like?

USE [xxxWebserv]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[cd_insert_lopenummer]
		@operator = 1,
		@tidspunkt = SELECT GETDATE()

SELECT	'Return Value' = @return_value

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 04:47:27

USE [xxxWebserv]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[cd_insert_lopenummer]
@operator = 1,
@tidspunkt = GETDATE()

SELECT 'Return Value' = @return_value

Madhivanan

Failing to plan is Planning to fail

tiwas
Starting Member

37 Posts

Posted - 2009-12-15 : 04:52:41

madhivanan,

This is the output I get

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

I already tried changing it to that...

tiwas
Starting Member

37 Posts

Posted - 2009-12-15 : 04:56:15

BTW, I changed my table to

USE [xxxWebserv]
GO
/****** Object:  Table [dbo].[kundeservice_lopenummer]    Script Date: 12/15/2009 10:55:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[kundeservice_lopenummer](
	[Lopenummer] [bigint] IDENTITY(1,1) NOT NULL,
	[Operator] [bigint] NOT NULL,
	[Kundenummer] [int] NULL,
	[utfall] [nvarchar](50) NULL,
	[Tidspunkt] [datetime] NOT NULL CONSTRAINT [DF_kundeservice_lopenummer_Tidspunkt]  DEFAULT (getdate())
) ON [PRIMARY]

tiwas
Starting Member

37 Posts

Posted - 2009-12-15 : 05:05:01

Never mind! Just realised I had to pass NULL to the values I'm not setting :)

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources