Table Name with Input Parameter

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 2000 Forums

Transact-SQL (2000)

Table Name with Input Parameter

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-01 : 14:52:08

hi
I want to create some table baseed on input parameter

CREATE PROCEDURE ABC @tabname varchar(40)
AS
DECLARE @tabNM vachar (40)
Set @filmn=abc_dce_+@tabname
CREATE TABLE @filmn(
field1 varchar (50) AS NULL ,
field2 varchar (10)
Go

For Execution
exec abc xyz

Iam using the above code where xyz is input parameter, what i want is the tble name should be dynmic and whatever parameter i supply it will create a new table
Thanks in advnce

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-01 : 14:53:28

This is called dynamic sql. There are hundreds if not thousands of examples in the forums. Just do a forum search and you'll see.

Why would you want to do this though? Just use a temp table instead.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-01 : 14:59:57

thanks

Why I need cz every time we have to enter in different tables

so if u know the exact solution plz reply me otherwise i will search for it

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-01 : 16:36:20

I have an error at @tabname so can i use variable with create table

CREATE TABLE 'abc_dec_'+@tabname
OR
CREATE TABLE @tabname

plz help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-01 : 16:40:21

Did you search the forums for "dynamic sql"? Here's one result:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30129

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-01 : 16:59:03

Thanks for ur quick reply
Yes that exapmle is gr8 but still when iam runnig my command
I have an error in
create table @tablename
the error is at @tablenme

plz help me

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-01 : 17:01:07

Did you change your stored procedure to incorporate dynamic sql? That's the only way that it is going to work. What exactly are you running? Show me how you added the dynamic sql into it.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-01 : 17:25:07

yes
I dont know how to use dynamic sql its first time iam using.

CREATE PROCEDURE abc_sss @x varchar(40)
AS
Declare @y varchar(40)
Set @y ='abc_dec_'+@x

CREATE TABLE @y
(
Field1 varchar NULL ,
Field2 varchar NULL
)

So this is my code
and when i run i have error at CREATE TABLE @y at @y which is my variable

thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-01 : 17:27:47

I don't see where you used the example in the post to fix your stored procedure. Here is an example of dynamic sql for a SELECT statement:

DECLARE @SQL VARCHAR(7000)
DECLARE @TblName SYSNAME

SET @TblName = 'Table1'

SET @SQL = 'SELECT * FROM ' + @TblName

EXEC (@SQL)

You need to understand what dynamic sql is doing. You are building a dynamic string to be executed. That is what you must do in your code. The building part is done at SET @SQL...

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-01 : 20:43:28

CREATE PROCEDURE ccc @x varchar(40)
AS
Declare @y varchar(40)
Declare @sq varchar(255)

Set @y='abc_dec_'+@x
set @sq= 'CREATE TABLE'+ @y+
'(
Field1 varchar NULL ,
field2 varchar NULL,
)'

execute ccc testfile

Now this is working with no error but there it is not creating any table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 12:10:54

You don't have EXEC (@sq) in your stored procedure. The SET statement just sets up the variable. You need to execute the variable. This is done inside the stored procedure.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources