SEO

#1 (**permalink**) 02-28-2008, 06:31 PM

Hi All

Im still pretty new to using stored procedures and am not sure what syntax i
should be using. The variable @LocationID will be something along the lines
of 002, 003 and so on. What i want to do in the procedure is see if the
table already exists, and if so delete it (The code i have only works if
there is a record in the table). My problem with the syntax is that i want
to combine the word "Location" with the @LocationID variable when making the
new table and when checking if the table already exists but im unsure how to
combine the two for use in the procedure

Thanks in advance

/*
** Create New Location ID Table
*/

CREATE PROCEDURE [dbo].[CreateLocation]

@LocationID Char(3) /* New Location ID Number */

AS

IF EXISTS (SELECT * FROM (Location + @LocationID))
DROP TABLE [dbo].[(Location + @LocationID)]

CREATE TABLE [dbo].[(Location + @LocationID)](
[MachineName] [VarChar] (100) NOT NULL)
GO

#2 (**permalink**) 02-28-2008, 06:31 PM

"Jarrod Morrison" <jarrodm@ihug.com.au> wrote in message
news:bndkc2$vda$1@lust.ihug.co.nz...
> Hi All
>
> Im still pretty new to using stored procedures and am not sure what syntax
i
> should be using. The variable @LocationID will be something along the
lines
> of 002, 003 and so on. What i want to do in the procedure is see if the
> table already exists, and if so delete it (The code i have only works if
> there is a record in the table). My problem with the syntax is that i want
> to combine the word "Location" with the @LocationID variable when making
the
> new table and when checking if the table already exists but im unsure how
to
> combine the two for use in the procedure
>
> Thanks in advance
>
>
> /*
> ** Create New Location ID Table
> */
>
> CREATE PROCEDURE [dbo].[CreateLocation]
>
> @LocationID Char(3) /* New Location ID Number */
>
> AS
>
> IF EXISTS (SELECT * FROM (Location + @LocationID))
> DROP TABLE [dbo].[(Location + @LocationID)]
>
> CREATE TABLE [dbo].[(Location + @LocationID)](
> [MachineName] [VarChar] (100) NOT NULL)
> GO
>
>

You could do this with dynamic SQL (see link below), but there are a couple
of reasons why you probably shouldn't. First, the data model is wrong, in
that the location ID should be part of the key in a single, larger table -
one table per location isn't a good model, and won't scale well. Second,
users who don't have permission to drop and create tables won't be able to
use this procedure, and generally you want to limit those permissions to a
limited number of users.

http://www.algonet.se/~sommar/dynamic_sql.html

This is your situation:

http://www.algonet.se/~sommar/dynami...tml#Sales_yymm

Simon

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Stored Procedures Syntax