Solved

Dynamic SQL syntax problem

Posted on 2006-11-29
5
404 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 500 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

733 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