Solved

Dynamic SQL syntax problem

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now