asked on

Dynamic SQL syntax problem

I am passing this as execute and below is my SPROC and error result. Any ideas????

Actual SQL:
[dbo].[Advologix_UserPromotion_AddEmployee]
       ClientNasslaw,
1,
17,
89,
28,
'Stephanie',
'Rosemeyer',
'Stephanie Rosemeyer',
'01/01/2000',
27,
27,
0,
1

SPROC:
ALTER PROCEDURE [dbo].[Advologix_UserPromotion_AddEmployee]

      @DatabaseName                  varchar(50),
      @FirmId                              int,
      @TZoneId                        int,
      @ExpenseCurrId                  int,
      @UserId                              int,
      @FirstName                        varchar(50),
      @LastName                        varchar(50),
      @FullName                        varchar(20),
      @StartDate                        datetime,
      @EditUserId                        int,
      @CreateUserId                  int,
      @PortalId                        int,
      @LevelId                        int

AS
BEGIN
      EXEC ('INSERT INTO ' + @DatabaseName + '.dbo.[Employee](
                   [Firm_Id]
                   ,[Time_Zone_Id]
                   ,[Expense_Currency_Id]
                   ,[DNNUserId]
                   ,[First_Name]
                   ,[Last_Name]
                   ,[Full_Name]
                   ,[Start_Date]
                   ,[Create_Date]
                   ,[Edit_Date]
                   ,[Edit_UserId]
                   ,[Create_UserId]
                   ,[PortalId]
                   ,[Level_Id])
                  VALUES( ' +
                        @FirmId + ',' +
                        @TZoneId + ',' +
                        @ExpenseCurrId + ',' +
                        @UserId + ',''' +
                        @FirstName + ''', ''' +
                        @LastName + ''', ''' +
                        @FullName + ''',''' +
                        @StartDate + ''',
                        getdate(),
                        getdate(),''' +
                        @EditUserId + ',' +
                        @CreateUserId + ',' +
                        @PortalId + ',' +
                        @LevelId + ')

                  select scope_identity() as clientcontactid

              END')

END

ERROR:
Msg 105, Level 15, State 1, Line 18
Unclosed quotation mark after the character string '27,27,0,1)

                  select scope_identity() as clientcontactid

              END'.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '27,27,0,1)

                  select scope_identity() as clientcontactid

              END'.

ASKER CERTIFIED SOLUTION

Nightman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nightman

Note that you will probably have to cast your non-text parameters to varchar to concatenate:

e.g.
cast(@PortalId as nvarchar(10)+ ',' +
cast(@LevelId as nvarchar(10)+ ')

Aneesh

Some of the arguments are integer, you need either a cast operation to string suring the string addition or change that datatype to varchar

ALTER PROCEDURE [dbo].[Advologix_UserPromotion_AddEmployee]

@DatabaseName varchar(50),
@FirmId varchar(10),
@TZoneId varchar(10),
@ExpenseCurrId varchar(10),
@UserId varchar(10),
@FirstName varchar(50),
@LastName varchar(50),
@FullName varchar(20),
@StartDate datetime,
@EditUserId varchar(10),
@CreateUserId varchar(10),
@PortalId varchar(10),
@LevelId varchar(10)

AS
BEGIN
EXEC ('INSERT INTO ' + @DatabaseName + '.dbo.[Employee](
[Firm_Id]
,[Time_Zone_Id]
,[Expense_Currency_Id]
,[DNNUserId]
,[First_Name]
,[Last_Name]
,[Full_Name]
,[Start_Date]
,[Create_Date]
,[Edit_Date]
,[Edit_UserId]
,[Create_UserId]
,[PortalId]
,[Level_Id])
VALUES( ' +
@FirmId + ',' +
@TZoneId + ',' +
@ExpenseCurrId + ',' +
@UserId + ',''' +
@FirstName + ''', ''' +
@LastName + ''', ''' +
@FullName + ''',''' +
@StartDate + ''',
getdate(),
getdate(),''' +
@EditUserId + ',' +
@CreateUserId + ',' +
@PortalId + ',' +
@LevelId + ')

Aneesh

you need to change the StartDate tooo

ALTER PROCEDURE [dbo].[Advologix_UserPromotion_AddEmployee]

@DatabaseName varchar(50),
@FirmId varchar(10),
@TZoneId varchar(10),
@ExpenseCurrId varchar(10),
@UserId varchar(10),
@FirstName varchar(50),
@LastName varchar(50),
@FullName varchar(20),
@StartDate varchar(12) , ---------------
@EditUserId varchar(10),
@CreateUserId varchar(10),
@PortalId varchar(10),
@LevelId varchar(10)

AS
BEGIN
EXEC ('INSERT INTO ' + @DatabaseName + '.dbo.[Employee](
[Firm_Id]
,[Time_Zone_Id]
,[Expense_Currency_Id]
,[DNNUserId]
,[First_Name]
,[Last_Name]
,[Full_Name]
,[Start_Date]
,[Create_Date]
,[Edit_Date]
,[Edit_UserId]
,[Create_UserId]
,[PortalId]
,[Level_Id])
VALUES( ' +
@FirmId + ',' +
@TZoneId + ',' +
@ExpenseCurrId + ',' +
@UserId + ',''' +
@FirstName + ''', ''' +
@LastName + ''', ''' +
@FullName + ''',''' +
@StartDate + ''',
getdate(),
getdate(),''' +
@EditUserId + ',' +
@CreateUserId + ',' +
@PortalId + ',' +
@LevelId + ')
select scope_identity() as clientcontactid

END')

END

Aneesh

I hope u have accepted the wrong answer