We help IT Professionals succeed at work.

SQl stored proc

HLRosenberger
HLRosenberger used Ask the Experts™
on
I want to turn these 3 statements into a stored proc so that I can reuse and create a set of tablew, where the table name is the input parm.   Actually, I need the prefix IHPS on  the table, so I'll only pass everything after IHPS_



CREATE TABLE [dbo].[IHPS_case_information](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [clientID] [bigint] NULL,
 CONSTRAINT [PK_IHPS_case_information] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IHPS_case_information]  WITH CHECK ADD  CONSTRAINT [FK_IHPS_case_information_IHPS_client] FOREIGN KEY([clientID])
REFERENCES [dbo].[IHPS_client] ([ID])
GO

ALTER TABLE [dbo].[IHPS_case_information] CHECK CONSTRAINT [FK_IHPS_case_information_IHPS_client]
GO
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer
Commented:
Essentially you would have to build the script as a string, and then execute that using SQLExec.


See the solution here as an example:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27674894.html
Do it this way:

Create Procedure Create_Alter_Table @tabname varchar(30)
As
Begin

Declare @str1 varchar(100), @str2 varchar(1000), @str3 varchar(1100), @str4 varchar(100), @str5 varchar(500), @str6 varchar(600), @str7 varchar(100), @str8 varchar(500), @str9 varchar(600)

SET @str1 = 'CREATE TABLE [dbo].[IHPS_'+ @tabname
SET @str2 = '(
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [clientID] [bigint] NULL,
 CONSTRAINT [PK_IHPS_case_information] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]'

SET  @str3 = @str1 + @str2

Exec sp_executesql @str3

SET @str4 = 'ALTER TABLE [dbo].[IHPS_'+ @tabname
SET @str5 = '  WITH CHECK ADD  CONSTRAINT [FK_IHPS_case_information_IHPS_client] FOREIGN KEY([clientID])
REFERENCES [dbo].[IHPS_client] ([ID])'

SET  @str6 = @str4 + @str5

Exec sp_executesql @str6

ALTER TABLE [dbo].[IHPS_case_information] CHECK CONSTRAINT [FK_IHPS_case_information_IHPS_client]

SET @str7 = 'ALTER TABLE [dbo].[IHPS_'+ @tabname
SET @str8 = '  CHECK CONSTRAINT [FK_IHPS_case_information_IHPS_client]'

SET  @str9 = @str7 + @str8

Exec sp_executesql @str9

End

The second ALTER TABLE is incomplete OR incorrect. Please fit the correct ALTER TABLE string in the correct way just as I fitted for the first ALTER TABLE. Cheers.

Author

Commented:
thanks