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)
 error messages running this script

Author  Topic 

jaskalirai
Starting Member

31 Posts
Posted - 2007-10-09 : 06:05:51
USE [dw_data]
GO

CREATE PROC [dbo].[usp_patients_create]

AS

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

CREATE TABLE [dw_build].[dbo].[patients] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[copy_letters] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[copy_letters_userno] [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,
[date_of_birth] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_of_death] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name_at_birth] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[previous_ref_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[previous_ref_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sector] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sector_userno] [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]

CREATE PROC [dbo].[usp_ward_leave]

AS

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

CREATE TABLE [dw_build].[dbo].[ward_leave] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[admission_smr4_sent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[consultant] [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,
[disch_return_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[disch_return_time] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discharge_smr4_sent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[expect_return] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fist_admission_reco] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fist_discharge_reco] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hrg_code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hrg_code_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hrg_version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[leave_place] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[leave_type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[leave_type_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[min_leave_f_up] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[min_leave_f_up_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [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,
[place] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pss_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[result] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[result_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ris_record_sent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sig_facility] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sig_facility_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[start__ward__hour] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[start__ward__minute] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[start_hour] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[start_minute] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[status_change_smr4] [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,
[user_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_5] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_6] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_7] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ward] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ward_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ward_leave] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY];



CREATE PROC [dbo].[usp_user_code]

AS

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


CREATE TABLE [dw_build].[dbo].[user_code] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alias] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[code] [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,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[disabled] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[group_] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[list_change_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[list_number2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[long_description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[national_code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[national_default] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[number] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[number_as_string] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[palmtop_use] [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,
[user_code_type_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_10] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_11] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_12] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_13] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_14] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_15] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_16] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_17] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_18] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_19] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_20] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_21] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_22] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_23] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_24] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_25] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_26] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_27] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_28] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_5] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_6] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_7] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_8] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_code_type_9] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[z2nd_alias] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


CREATE PROC [dbo].[usp_sys_table_index_base]

AS

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

CREATE TABLE [dw_build].[dbo].[sys_table_index_base] (
[table_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[index_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_yn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[unique_yn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[build_yn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]




CREATE PROC [dbo].[usp_sys__import_table_list1]

AS

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

CREATE TABLE [dw_build].[dbo].[sys__import_table_list1] (
[table_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[import_yn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[table_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]



CREATE PROC [dbo].[usp_sys__import_table_list__short]

AS

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

CREATE TABLE [dw_build].[dbo].[sys__import_table_list__short] (
[table_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[import_yn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[table_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


CREATE PROC [dbo].[usp_sys__import_table_list]

AS

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

CREATE TABLE [dw_build].[dbo].[sys__import_table_list] (
[table_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[import_yn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[table_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]



i go to run the above script and keep getting a error message Incorrect syntax near the keyword 'PROC'.

what am i missing out please help me

elancaster
A very urgent SQL Yakette

1208 Posts
Posted - 2007-10-09 : 06:08:08
try putting a GO after your 'create table' bits etc..

Em
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-09 : 06:57:59
I don't think you can do:

CREATE PROC [dbo].[usp_patients_create]
AS
IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'patients')
CREATE TABLE [dw_build].[dbo].[patients] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
...
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

because CREATE TABLE has to be in a single statement on its own. So I reckon you will need something like:

CREATE PROC [dbo].[usp_patients_create]
AS
IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'patients')
EXEC('CREATE TABLE [dw_build].[dbo].[patients] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
...
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]')

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-09 : 06:59:28
Is everything really 255 chars wide?

Even the ID? and the long_description?

Might be a staging table of course ... but I'd want Varchar(8000) for everything for one of those I reckon!

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-09 : 07:18:55
Yes, you can create several tables within a SP without EXEC.
CREATE PROCEDURE PesoTest
AS

CREATE TABLE #Temp (i INT)
CREATE TABLE #Test (j INT)

INSERT #Temp SELECT a FROM Table1
INSERT #Test SELECT i FROM #Temp

DROP TABLE #Temp
DROP TABLE #Test

In your code/script above, put a GO before "CREATE PROC". Your code faults because a CREATE PROC needs to be first in a statement.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-09 : 07:39:51
"you can create several tables within a SP"

Doh! Not my day, sorry about that
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-09 : 07:44:41
2 on one day?
Kristen, do you need some more coffee?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-09 : 07:50:32
Actually biscuits would be good; I'm in the dog-house for not doing the Supermarket online-shop ...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-10-09 : 07:56:16
http://www.thamelmall.com/thamelgift/thirdpage.asp?categoryID=41&subcategoryID=154


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-09 : 08:00:53
/Munch!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site