CODEWITHSUNDEEP
sql-serverstored-procedures
Avinash
Avinash

Reputation: 193

How to execute multiple queries in SQL Server?

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.

Upvotes: 0

Views: 101

Answers (1)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

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

Upvotes: 1

Related Questions