create proc City_Info
@StateRef nvarchar(20)
as
begin
declare @StateCod nvarchar(3);
declare @Check int;
select @StateCod = StateCod from State_Cod where State_Nam = @StateRef
create table C0NCAT(@StateCod' ,'City')(Sno int identity(1,1))
end
Can Anyone tell how can i fetch a Particular Name from Column and Make table using Procedure in mssql?
First of all it looks like classic example of SELECT * FROM sales + @yymm
This is a variation of the previous case, where there is a suite of tables that actually do describe the same entity. All tables have the same columns, and the name includes some partitioning component, typically year and sometimes also month. New tables are created as a new year/month begins.
In this case, writing one stored procedure per table is not really feasible. Not the least, because the user may want to specify a date range for a search, so even with one procedure per table you would still need a dynamic dispatcher.
If you still want to go this way you could use Dynamic-SQL.
create proc City_Info
@StateRef nvarchar(20)
as
begin
declare @StateCod nvarchar(3);
declare @Check int;
select @StateCod = StateCod from State_Cod where State_Nam = @StateRef;
DECLARE @sql NVARCHAR(MAX) =
'create table '
+ QUOTENAME(C0NCAT(@StateCod ,'City'))
+ '(Sno int identity(1,1))';
EXEC sp_executesql @sql
end
