?
Solved

Dynamic SQL syntax problem

Posted on 2006-11-29
5
Medium Priority
?
443 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 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

807 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