December 10, 2013 at 2:24 pm
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/
December 10, 2013 at 2:27 pm
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 ,
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
December 10, 2013 at 2:51 pm
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.
December 10, 2013 at 2:59 pm
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.
December 10, 2013 at 3:00 pm
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 triedchanging 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/
December 10, 2013 at 3:23 pm
Thanks, I will try that tomorrow in office
December 10, 2013 at 3:27 pm
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/
December 12, 2013 at 5:34 am
Post some actual values for your variables like @Offc. That may help identify the issue.
Mark
December 13, 2013 at 12:47 pm
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