Solved

Dynamic SQL syntax problem

Posted on 2006-11-29
5
397 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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
SQL Pivot with row total 5 26
SQL Activity Monitor detail 2 23
SQL Syntax Grouping Sum question 7 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

792 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