?
Solved

Dynamic SQL syntax problem

Posted on 2006-11-29
5
Medium Priority
?
422 Views
Last Modified: 2012-06-27
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'.
0
Comment
Question by:pauldes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 18039289
Give this a try:

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

0
 
LVL 29

Expert Comment

by:Nightman
ID: 18039306
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)+ ')
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18039330
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 + ')
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18039362
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


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18039488
I hope u have accepted the wrong answer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question