Cant get Stored Procedure to execute properly

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

SQL Server 2005 Forums

Transact-SQL (2005)

Cant get Stored Procedure to execute properly

Author

Topic

ease2002
Starting Member

8 Posts

Posted - 2008-06-12 : 12:00:45

Hi,

I am trying to execute the following simple stored procedure. The procedure runs but doesn't create the table I am trying to create and doesn't insert the records into the table I created. But again, the procedure runs without any errors.

Can someone please correct the syntax so my procedure will run properly. It pretty basic.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PremierData1] 
	
AS
	
IF EXISTS(SELECT 1 FROM sys.sysojects where type='U' AND name='PremierTest') DROP TABLE dbo.[PremierTest]

BEGIN

	CREATE TABLE dbo.[PremierTest] (
		[AYQ] nvarchar(6) null ,
		[CYQ] nvarchar(6) null ,
		[Description] nvarchar(50) null, 
		[PIP] [decimal] (17,2) ,
		[BI] [decimal](17,2) Not null,
		[PD] [decimal](17,2) Not null,
		[COLL] [decimal](17,2) not null,
		[COMP] [decimal](17,2) not null,
		[DCRAT] [nvarchar](2) null ,
		[Agent][nvarchar](3) null ,
	)  ON [Primary]
	
	

	insert into dbo.premiertest
	select *
	from dbo.[new agt type tri 0804]
	where dir_ceded_ind = 'c'

	

END

Thank you for any help provided.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-12 : 12:36:47

There's one to may commas in your create table statement. Get rid of the last one
[Agent][nvarchar](3) null ,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 12:39:01

After running the procedure did you run select statement to see if there are rows?

Madhivanan

Failing to plan is Planning to fail

ease2002
Starting Member

8 Posts

Posted - 2008-06-12 : 13:43:35

Unfortunately the table isn't even created, but I get the message 'Command(s) completed successfully.'

It is as if the proc doesn't even run. That is the problem. I manually execute the procedure by pressing the 'Execute' button. I refresh the tables and the table doesn't show up, so obviously something isn't executing.

Any help would be appreciated.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 01:15:55

It worked for me anyways. i was able to create the table. Mean while, not sure it happened while you copy pasted, there's a small typo in your code. its sys.sysobjects the b is missing in your code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 01:36:20

[code]CREATE PROCEDURE dbo.PremierData1
AS

SET NOCOUNT ON

IF EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'PremierTest'
)
DROP TABLE dbo.PremierTest

CREATE TABLE dbo.PremierTest
(
AYQ NVARCHAR(6),
CYQ NVARCHAR(6),
Description NVARCHAR(50),
PIP DECIMAL (17,2),
BI DECIMAL(17,2) NOT NULL,
PD DECIMAL(17,2) NOT NULL,
COLL DECIMAL(17,2) NOT NULL,
COMP DECIMAL(17,2) NOT NULL,
DCRAT NVARCHAR(2),
Agent NVARCHAR(3)
)

INSERT dbo.PremierTest
(
AYQ,
CYQ,
Description,
PIP,
BI,
PD,
COLL,
COMP,
DCRAT,
Agent
)
SELECT AYQ,
CYQ,
Description,
PIP,
BI,
PD,
COLL,
COMP,
DCRAT,
Agent
FROM dbo.[new agt type tri 0804]
WHERE dir_ceded_ind = 'c'[/code]

E 12°55'05.25"
N 56°04'39.16"

ease2002
Starting Member

8 Posts

Posted - 2008-06-13 : 11:20:03

How are you guys executing the T-SQL? Are you executing it through the 'Execute!' button?

I have reworked the code and I see that it is running again without error, but no table is created.

Thanks for your patience, guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 11:37:11

quote:
Originally posted by ease2002

How are you guys executing the T-SQL? Are you executing it through the 'Execute!' button?

I have reworked the code and I see that it is running again without error, but no table is created.

Thanks for your patience, guys.

you only create the procedure when you hit execute button. the procedure is executed by using statement
Exec PremierData1
and hitting execute button

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources