Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » ASP.NET »
Sqlserver
Posted Date: 13 Oct 2008 Posted By: kalaimanichozhan Member Level: Gold Points: 1
Responses:
2
|
My need is create a table at runtime and insert a values given at runtime.
If a table is already exist means simply insert a value.
For That Purpose I written following coding in stored procedure. It is not working .......
create procedure reservation as
declare @sql varchar(1000)
declare @sql1 varchar(1000)
declare @Tablename varchar(50)
declare @Date datetime
--declare @date1 datetime
declare @Day varchar(50)
declare @Flightno varchar(50)
declare @Fromplce varchar(50)
declare @Toplace varchar(50)
Declare @Totalsheet int
Declare @reserved int
Declare @balance int
begin
set @sql1='select * from '+@Tablename+''
if exists(exec(@sql1))
set @sql='insert into '+@Tablename+' values(@Date,@day,@flightno,@fromplace,@Toplace,@TotalSheet,@reserved,@balance)'
exec(@sql)
else
set @sql='create table '+@Tablename+'(Date Datetime,Day Varchar(50),Flightno varchar(50),Fromplace Varchar(50),Toplace varchar(50),Totalsheet int,Reserved int,BalanceSheet int)'
exec(@sql)
end
urgently i need answer.
|
Responses
|
| Author: Vignesh 13 Oct 2008 | Member Level: Silver | Rating:  Points: 6 |
The Procedure could be slightly modified as below,
create procedure reservation as
declare @sql varchar(1000)
declare @sql1 varchar(1000)
declare @Tablename varchar(50)
declare @Date datetime
--declare @date1 datetime
declare @Day varchar(50)
declare @Flightno varchar(50)
declare @Fromplce varchar(50)
declare @Toplace varchar(50)
Declare @Totalsheet int
Declare @reserved int
Declare @balance int
begin
DECLARE @cnt INT
SELECT @cnt = COUNT(1) FROM SYSOBJECTS WHERE xtype = 'u' AND name = @Tablename
if(@cnt > 0)
set @sql='insert into '+@Tablename+' values(@Date,@day,@flightno,@fromplace,@Toplace,@TotalSheet,@reserved,@balance)'
exec(@sql)
else
set @sql='create table '+@Tablename+'(Date Datetime,Day Varchar(50),Flightno varchar(50),Fromplace Varchar(50),Toplace varchar(50),Totalsheet int,Reserved int,BalanceSheet int)'
exec(@sql)
end
Here we check the sysobjects table for the existence of the given table name since the given table being a physical table.
| | Author: Preet 13 Oct 2008 | Member Level: Gold | Rating: Points: 6 |
The Procedure could be slightly modified as below,
create procedure reservation as
declare @sql varchar(1000)
declare @sql1 varchar(1000)
declare @Tablename varchar(50)
declare @Date datetime
declare @Day varchar(50)
declare @Flightno varchar(50)
declare @Fromplce varchar(50)
declare @Toplace varchar(50)
Declare @Totalsheet int
Declare @reserved int
Declare @balance int
begin
DECLARE @cnt INT
set @sql='insert into '+@Tablename+' values(@Date,@day,@flightno,@fromplace,@Toplace,@TotalSheet,@reserved,@balance)'
SELECT @cnt = COUNT(*) FROM SYSOBJECTS WHERE xtype = 'u' AND name = @Tablename
if(@cnt > 0)
exec(@sql)
else
set @sql='create table '+@Tablename+'(Date Datetime,Day Varchar(50),Flightno varchar(50),Fromplace Varchar(50),Toplace varchar(50),Totalsheet int,Reserved int,BalanceSheet int)'
exec(@sql)
exec(@sql1)
end
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|