why isnt my stored procedure working

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

why isnt my stored procedure working

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-10-24 : 15:52:14

Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table

USE [dw_data]
GO
/****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[usp_address]
(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')

CREATE TABLE [dw_build].[dbo].[address] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[other_inv_link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out_of_district] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packed_address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_ignore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_valid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pct_of_res] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real_end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

END

IF @TableType is NULL OR @TableType = 2

BEGIN

CREATE TABLE [dw_build].[dbo].[address_cl] (
[_id] [int] NULL,
[address_1] [varchar](40) NULL,
[address_2] [varchar](40) NULL,
[category] [varchar](7) NULL,
[category_userno] [int] NULL,
[county] [varchar](40) NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[end_date] [datetime] NULL,
[forename] [varchar](40) NULL,
[notes] [varchar](max) NULL,
[other_inv_link] [int] NULL,
[out_of_district] [bit] NOT NULL,
[packed_address] [varchar](220) NULL,
[paf_ignore] [bit] NOT NULL,
[paf_valid] [bit] NOT NULL,
[patient] [int] NULL,
[patient_date] [datetime] NULL,
[pct_of_res] [int] NULL,
[postcode] [varchar](40) NULL,
[real_end_date] [datetime] NULL,
[relationship] [varchar](7) NULL,
[relationship_userno] [int] NULL,
[surname] [varchar](40) NULL,
[telephone] [varchar](40) NULL,
[title] [varchar](40) NULL,
[town] [varchar](40) NULL,
[updated] [datetime] NULL,
[updator] [int] NULL
) ON [PRIMARY]

END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 15:58:20

And how are you executing the proc?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-24 : 15:58:23

you can't create a table inside a stored procedure.
you can if you use dynamic sql.
but why would you want to do that?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jaskalirai
Starting Member

31 Posts

Posted - 2007-10-24 : 16:04:28

im just running the script. im running the sproc in a seperate database and the tables should be created in dw_bulid whilst the spocs are being run in dw_data

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 16:08:06

quote:
Originally posted by spirit1

you can't create a table inside a stored procedure.

Actually you can

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-24 : 16:10:42

you can?? since when?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 16:13:04

You can do it in 2000 and in 2005. Dont know about prior versions though..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jaskalirai
Starting Member

31 Posts

Posted - 2007-10-24 : 16:14:48

so do you have any suggestions for me ppl ???

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 16:16:07

quote:
Originally posted by jaskalirai

so do you have any suggestions for me ppl ???

I asked you a question..how are you calling the proc? with what parameters?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jaskalirai
Starting Member

31 Posts

Posted - 2007-10-24 : 16:28:39

using tablnames as parameters

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 16:29:16

quote:
Originally posted by jaskalirai

using tablnames as parameters

show us exactly how you are executing the proc. post the code here that you are using to run the proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-24 : 17:35:52

Does the table already exist? I prefer to use the other method of finding out whether an object exists below..

You want to fix your table structure however, you don't need all those fields to be 255 characters for sure, and you probably want to have a Primary Key of some kind assigned.

What exactly are you trying to do? There may be a better way...

And I have never had an issue creating tables within stored procedures.

CREATE PROC [dbo].[usp_address]
(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

If Not exists (Select * From sysobjects where name = 'address')

Create Table address
<snip>
END

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 17:45:58

quote:
Originally posted by dataguru1971

Does the table already exist? I prefer to use the other method of finding out whether an object exists below..

You want to fix your table structure however, you don't need all those fields to be 255 characters for sure, and you probably want to have a Primary Key of some kind assigned.

What exactly are you trying to do? There may be a better way...

And I have never had an issue creating tables within stored procedures.

CREATE PROC [dbo].[usp_address]
(
@TableType INT = null

)

AS

IF @TableType is NULL OR @TableType = 1

BEGIN

If Not exists (Select * From sysobjects where name = 'address' and Type = 'U')

Create Table address
<snip>
END

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-24 : 17:58:13

Dinakar...
THanks for catching that hasty omission

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources