PROCEDURE - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

PROCEDURE

Author

Topic

mani_12345
Starting Member

35 Posts

Posted - 2012-05-30 : 06:13:35

How we can create column name and datatype for same at runtime with the help of procedure.?

give some suggestion...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 07:21:30

Have a look at the alter table statement?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 15:51:19

quote:
Originally posted by mani_12345

How we can create column name and datatype for same at runtime with the help of procedure.?

give some suggestion...

why do you need to create column at runtime? Is it for pivotting requirement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mani_12345
Starting Member

35 Posts

Posted - 2012-05-31 : 03:11:22

i like to create table with there column name and datatype at run time with the help of proc ...
though its easy to create table but what about column ? how i assign datatype at run time ???

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 03:25:14

alter table add mycolumn varchar(20)

You are probably going to get into trouble building a system based on this though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mani_12345
Starting Member

35 Posts

Posted - 2012-05-31 : 03:50:54

alter proc col_dynamic_proc (@table nvarchar(100) ,@col int )
as
Begin
set nocount on
Declare @strsql nvarchar(1000)
declare @retcd int
set @retcd = -1
SET @strsql = ''
SELECT @strsql = 'CREATE TABLE ' + @table + ' ( ' -- table creation
select @strsql = @strsql + 'id integer primary key identity (100,1) )'

select @strsql = @strsql + 'alter table '+ @table+ ' Add ' + rtrim(@col) + ' varchar(10)'
--SELECT @strsql = @strsql + 'alter table ' +@table + ''
--select @strsql = @strsql + 'add '+ @col + ' '
--select @strsql = @strsql +' + @col + INTEGER PRIMARY KEY IDENTITY(100,1) '
-- SELECT @strsql = @strsql +' ,Name VARCHAR(30) '
--SELECT @strsql = @strsql +' ,City VARCHAR(10) '
-- SELECT @strsql = @strsql +' )'

exec(@strsql)
EXEC @retcd = sp_executesql @strsql
if @retcd <>0
begin
raiserror('error',16,1)
end

END

try to run this ..this gives an error ...by using alter command

mani_12345
Starting Member

35 Posts

Posted - 2012-05-31 : 03:54:17

though in declare of paramter i hve changed it to varchar bt again its showing me an error
Msg 173, Level 15, State 1, Line 1
The definition for column 'Namevarchar' must include a data type.
Msg 173, Level 15, State 1, Line 1
The definition for column 'Namevarchar' must include a data type.
Msg 50000, Level 16, State 1, Procedure col_dynamic_proc, Line 24
error

i thnks column name is not proply concatenatd with datatype

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:37:05

add a space before datatype and check

I still feel the approach is not recommended one. You should be trying to add the detail as rows itself rather than keeping on building columns like this at runtime.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mani_12345
Starting Member

35 Posts

Posted - 2012-05-31 : 23:26:49

i know that it will not run fine for long number of columns list ..

bt i like to do it for small application.

though i have created column dynamically bt i like to add while loop so that i didnt have to write statement again for creating column list bt i throwing me an error...

see this
ALTER proc [dbo].[col_dynamic_proc] (@table nvarchar(100),

@col_int int ,
@col varchar(15)
--@col1 varchar(15) ,
--@col2 varchar(15) ,
--@col3 varchar(15)
)
as
Begin

set nocount on
Declare @strsql nvarchar(1000)
declare @retcd int
set @retcd = -1
SET @strsql = ''
SELECT @strsql = 'CREATE TABLE ' + @table + ' ( ' -- table creation

while ( @col_int<4)
begin
--declare @data_type sysname
--set @data_type = if exists( select name from sys.types )
select @strsql = @strsql +'' + rtrim(@col) +' varchar(15)'

set @col_int = @col_int+1
if @col_int >=4
begin
break
end
else
begin
continue
end
end
select @strsql = @strsql + ' )'

EXEC @retcd = sp_executesql @strsql
if @retcd <>0
begin
raiserror('error',16,1)
end

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 23:54:32

you missed , separator between columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources