?
Solved

How to write a stored procedure to create a table using a parameter

Posted on 2007-10-16
2
Medium Priority
?
177 Views
Last Modified: 2010-03-20
Hi,

I have a stored procedure, that i want to use to create a table. I want to take in a parameter and use this parameter as the table name.

=========================
CREATE PROC proc1
@myTblName nvarchar(15)
AS
BEGIN

CREATE TABLE [@myTblName] (

[id] [bigint] NOT NULL,

[col1] [nvarchar](50) NULL,

[col2] [int] NULL,

[col3] [nchar](10) NULL,

[col4] [money] NULL,

[col5] [money] NULL,

[col6] [bigint] NULL,)

END
GO
===============================

This stored procedure runs, but not take in the parameter. it will only create a table with the name of "@myTblName". can anyone help? thanks
0
Comment
Question by:CRIIT
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20087860
CREATE PROC proc1
@myTblName nvarchar(15)
AS
BEGIN

exec ('
CREATE TABLE [' + @myTblName + '] (

[id] [bigint] NOT NULL,

[col1] [nvarchar](50) NULL,

[col2] [int] NULL,

[col3] [nchar](10) NULL,

[col4] [money] NULL,

[col5] [money] NULL,

[col6] [bigint] NULL)
')

END
0
 

Author Comment

by:CRIIT
ID: 20087871
cool, this is it, thanks a lot
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question