0

I am trying to insert an account info for example cash in table TBL_Account and at the same time I want to create a table with the same account name Cash.

ALTER PROCEDURE [dbo].[usp_AccountSaveAccount]
   @AccountId int,
   @AccountName nvarchar(Max),
   @PortalId int,
   @CultureCode nvarchar(100),
   @UserModuleId int,
   @AddedBy nvarchar(100)
AS
BEGIN
   DECLARE @SQLInsertString NVARCHAR(MAX)

   if(@AccountId = 0)
   BEGIN
      INSERT INTO dbo.AccountTable
    (
    AccountId,
    AccountName,
    PortalId,
    CultureCode,
    UserModuleId,
    AddedBy,
    AddedOn,
    UpdatedBy,
    UpdatedOn,
    IsDeleted
    )
    VALUES
    (
    @AccountId,
    @AccountName,
    @PortalId,
    @CultureCode,
    @UserModuleId,
    @AddedBy,
    GETDATE(),
    @AddedBy,
    GETDATE(),
    0
    );

    END

    DECLARE @SQLString NVARCHAR(MAX)

    SET @SQLString = 'CREATE TABLE [dbo].['+@AccountName+'](
    [AccountCatId] [int] IDENTITY(1,1) NOT NULL,
    [AccountCatName] [nvarchar](max) NULL,
    [InsertedDate] [datetime] NULL,
    [Particulars] [nvarchar](max) NULL,
    [ParticularsBy] [nvarchar](max) NULL,
    [ParticularsTo] [nvarchar](max) NULL,
    [Dr] [int] NULL,
    [Cr] [int] NULL,
    [DebitedAccountId] [int] NULL,
    [CreditedAccountId] [int] NULL,
    [DebitAmount] [float] NULL,
    [CreditAmount] [float] NULL,
    [AddedBy] [nvarchar](100) NULL,
    [AddedOn] [datetime] NULL,
    [UpdatedBy] [nvarchar](100) NULL,
    [UpdatedOn] [datetime] NULL,
    [IsDeleted] [bit] NULL,
    [DeletedBy] [nvarchar](100) NULL,
    [DeletedOn] [datetime] NULL,
 CONSTRAINT [PK_'+ @AccountName +'] PRIMARY KEY CLUSTERED 
(
    [AccountCatId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

EXEC (@SQLString)

    END

But it only creates table but does not insert into the table.

Any suggestion will be appreciated.

4
  • 3
    It's not really a good idea to create tables dynamically like that. It's not the way SQL is intended to work. I don't see why your insert wouldn't be working though, unless you're not passing 0 for @AccountId like your if is checking for? Commented Jul 6, 2015 at 3:54
  • Your problem is with your If statement where @AccountId = 0? Where is the value being passed for the AccountId? Commented Jul 6, 2015 at 4:19
  • 1
    I agree with Blorgbeard - this looks like a horrifically bad idea. Why would you want to create a whole new table for each account? As to your question - my guess is that the @AccountId you're passing is NULL, which does not = 0. Commented Jul 6, 2015 at 4:22
  • I have passed @AccountId = 0.Creating a dynamic Table is important as user must create its own category.previously i have placed the create table inside the if then also it only executes to create the table but it doesnot insert the name inside the dbo.AccountTable.What can be a better way for creating dynamic table? Commented Jul 6, 2015 at 4:31

1 Answer 1

1

You can convert nvarchar to varchar in create query

like this

SET @SQLString = 'CREATE TABLE [dbo].['+convert(varchar(200),@AccountName)+'](

or

CONSTRAINT [PK_'+ convert(varchar(200),@AccountName) +'] PRIMARY KEY CLUSTERED 
Sign up to request clarification or add additional context in comments.

3 Comments

The dynamic table created sucessfully but my problem is that the insert query is not working tho i am passing @AccountId= 0
Check you Accountid coulmn design specification. IF it is primary key then not inserted o value. If any foreign key then not inserted o value.
What a silly mistake i have made.I made AccountId as primary key and i was inserting AccountId.Thank you @Mukesh

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.