SQL syntax error

  • eobiki10 (12/10/2013)


    This is exactly my problem and my sql. The report has cascading parameters and it works well for a single value. I tried to make it work for multi- select but to no avail. The error as shown is "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','."

    IF @Action = 'U'

    BEGIN

    UPDATE p

    Set P.Employee_uno = e.Empl_uno,

    p.Employee_name = E.employee_name,

    p.email = E.email

    FROM BO_custom.dbo.property_coorindators P cross JOIN

    BO_live3.dbo.hbm_persnl E

    WHERE P.employee_uno = @ChangeEmpl AND p.offc_code = @Offc AND e.empl_uno = @employee

    END

    If @Action = 'I'

    Begin

    INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]

    ([Employee_Uno]

    ,[Employee_Name]

    ,[Offc_Code]

    ,[Email])

    Select @employee, employee_name, @Offc, EMAIL

    from BO_live3.dbo.HBM_persnl E

    where E.empl_uno = @employee

    End

    If @Action = 'X'

    Begin

    Delete from [BO_CUSTOM].[dbo].[Property_Coorindators]

    where offc_code = @Offc

    and @ChangeEmpl = employee_uno

    End

    SELECT *

    FROM BO_custom.dbo.property_coorindators P

    WHERE offc_code = @Offc

    Thanks so much

    You have to realize that each time you post the story is a little different. All of a sudden this is part of a much larger code that look suspiciously like a stored proc. We can't see your screen, we have no idea what your tables are like, we have no idea what your project is supposed to do, we have no idea what values you have in these parameters. In short, we don't really know much of anything. Without these details what you are getting is a best guess from experience about the issue might be. If you would PLEASE take a few minutes and read the first article in my signature it will make it so that myself and others can actually help with your issue. If you had done this several hours ago your issue would almost certainly be resolved. Instead we are on the second page of posts, you are no closer to a solution and you are likely getting frustrated that "those guys as SSC" can't figure out even a simple problem.

    Give us the details so we can work on your problem and we will give you an answer that will work.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • With formatting

    IF @Action = 'U'

    BEGIN

    UPDATE p

    SET P.Employee_uno = e.Empl_uno ,

    p.Employee_name = E.employee_name ,

    p.email = E.email

    FROM BO_custom.dbo.property_coorindators P

    CROSS JOIN BO_live3.dbo.hbm_persnl E

    WHERE P.employee_uno = @ChangeEmpl

    AND p.offc_code = @Offc

    AND e.empl_uno = @employee

    END

    IF @Action = 'I'

    BEGIN

    INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]

    ( [Employee_Uno] ,

    [Employee_Name] ,

    [Offc_Code] ,

    [Email]

    )

    SELECT @employee ,

    employee_name ,

    @Offc ,

    EMAIL

    FROM BO_live3.dbo.HBM_persnl E

    WHERE E.empl_uno = @employee

    END

    IF @Action = 'X'

    BEGIN

    DELETE FROM [BO_CUSTOM].[dbo].[Property_Coorindators]

    WHERE offc_code = @Offc

    AND @ChangeEmpl = employee_uno

    END

    SELECT *

    FROM BO_custom.dbo.property_coorindators P

    WHERE offc_code = @Offc

    I cannot see any errors in that. Do any of the tables have triggers?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • There's actually no problem with the code for single value. I want to know how to tweak the code to work for multi-select so users can choose all offices. I have set my offc parameters to use multi-value and tried

    changing the code as in:

    SELECT *

    FROM BO_custom.dbo.property_coorindators P

    WHERE offc_code IN (@Offc)

    But still the error persits which is "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','."

    I really dont know how to tweak the insert since it is where I am having the error.

  • It is not a stored procedure. What I want is for the user to be able to select multiple offices in the report. I initially included a portion of the code which is the INSERT section that I believe was giving me problem as shown by the error. My problem is how to make the cascading parameter to work for the multi-select. The recent code that I posted works well for single office.

  • eobiki10 (12/10/2013)


    There's actually no problem with the code for single value. I want to know how to tweak the code to work for multi-select so users can choose all offices. I have set my offc parameters to use multi-value and tried

    changing the code as in:

    SELECT *

    FROM BO_custom.dbo.property_coorindators P

    WHERE offc_code IN (@Offc)

    But still the error persits which is "the Select list for the insert statement contains more items than the insert list. The number of select values must match the number of INSERT columns. Incorrect syntax near ','."

    I really dont know how to tweak the insert since it is where I am having the error.

    In order for that to work you will need to parse your delimited list. The fastest and easiest way to do that is to take a look at the link in my signature about splitting strings.

    Your final code will end up being something like this:

    SELECT *

    FROM BO_custom.dbo.property_coorindators P

    cross apply dbo.DelimitedSplit8K(@Offc, ',')

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks, I will try that tomorrow in office

  • eobiki10 (12/10/2013)


    Thanks, I will try that tomorrow in office

    No problem. Make sure you read that article. This code is highly unlikely to work as is on your system. 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Post some actual values for your variables like @Offc. That may help identify the issue.

    Mark

  • Luis Cazares (12/10/2013)


    Are you using SSRS to call tha procedure? SSRS might be changing your variables to the actual values in the query using the Join function. To correctly insert the values, you have to remove the JOIN function for your variable.

    You could either use 2 variables (one for the insert with a single string and one which will return the values) or use a single variable and handle the single string with T-SQL.

    I think Luis is probably on the right track here. If you write this query in SSRS:

    INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]

    ([Employee_Uno]

    ,[Employee_Name]

    ,[Offc_Code]

    ,[Email])

    Select @employee, employee_name, @Offc, EMAIL

    from BO_live3.dbo.HBM_persnl E

    where E.empl_uno IN (@employee)

    and map the query variable @employee to a multi-valued report parameter (with, for example, the values 1534, 2356, 5576 selected), SSRS will format and submit the query for execution like this:

    INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]

    ([Employee_Uno]

    ,[Employee_Name]

    ,[Offc_Code]

    ,[Email])

    Select 1534, 2356, 5576, employee_name, @Offc, EMAIL

    from BO_live3.dbo.HBM_persnl E

    where E.empl_uno IN (1534, 2356, 5576)

    Et voila - suddenly, you have four columns in the INSERT list and six columns in the SELECT list.

    I think what the OP wants is this:

    INSERT INTO [BO_CUSTOM].[dbo].[Property_Coorindators]

    ([Employee_Uno]

    ,[Employee_Name]

    ,[Offc_Code]

    ,[Email])

    Select E.empl_uno, E.employee_name, @Offc, E.EMAIL

    from BO_live3.dbo.HBM_persnl E

    where E.empl_uno IN (@employee)

    Jason Wolfkill

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply