|
[x]
The Solution Rating System
|
|
| With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating. - The Grade of the Solution
- The Zone Rank of the Expert Providing the Solution
- The Number of Author and Expert Comments
- The Number of Experts Contributing
- The Feedback of the Community
Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site. If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support. Thank you! |
|
|
|
|
Asked by Cyber-Drugs in MS SQL Server
Hi guys,
Just fair warning, i am going to be posting a lot of code, but there are only 4 errors I need to fix. If somebody could help me, by telling me what needs to be changed, I would highly appreciate it.
Here are the errors:
Msg 120, Level 15, State 1, Procedure usp_HandleData, Line 257
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
Msg 120, Level 15, State 1, Procedure usp_HandleData, Line 413
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
Msg 120, Level 15, State 1, Procedure usp_HandleData, Line 528
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
Msg 120, Level 15, State 1, Procedure usp_HandleData, Line 603
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'usp_HandleData'. The stored procedure will still be created.
and here is the code:
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_ParseCSVString]') and xtype in (N'FN', N'IF', N'TF'))
DROP Function [dbo].[fn_ParseCSVString]
GO
CREATE Function fn_ParseCSVString
(
@CSVString varchar(8000),
@Delimiter varchar(10)
)
returns @tbl TABLE (s varchar(1000))
AS
BEGIN
DECLARE @i int ,
@j int
SELECT @i = 1
WHILE @i <= len(@CSVString)
BEGIN
SELECT @j = charindex(@Delimiter, @CSVString, @i)
IF @j = 0
BEGIN
SELECT @j = len(@CSVString) + 1
END
INSERT @tbl select substring(@CSVString, @i, @j - @i)
SELECT @i = @j + len(@Delimiter)
END
RETURN
END
GO
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[JustinProc]'))
DROP Procedure JustinProc
GO
CREATE Procedure JustinProc
AS
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_ParseCSVString]') and xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION fn_ParseCSVString
DECLARE @sql nvarchar(4000)
SET @sql = 'CREATE FUNCTION fn_ParseCSVString
(
@CSVString varchar(8000),
@Delimiter varchar(10)
)
returns @tbl TABLE (s varchar(1000))
AS
BEGIN
DECLARE @i int ,
@j int
SELECT @i = 1
WHILE @i <= len(@CSVString)
BEGIN
SELECT @j = charindex(@Delimiter, @CSVString, @i)
IF @j = 0
BEGIN
SELECT @j = len(@CSVString) + 1
END
INSERT @tbl select substring(@CSVString, @i, @j - @i)
SELECT @i = @j + len(@Delimiter)
END
RETURN
END'
EXEC sp_executeSQL @sql
GO
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_HandleData]'))
DROP Procedure usp_HandleData
GO
CREATE Procedure [dbo].[usp_HandleData]
(
@data as nvarchar(50),
@equipment as nvarchar(50),
@serial as nvarchar(50),
@description as nvarchar(80),
@systemstatus as nvarchar(50),
@superequip as nvarchar(50),
@material as nvarchar(50),
@position as nvarchar(50)
)
/*
returns @dump TABLE (Results nvarchar(50))
*/
AS
BEGIN
DECLARE c CURSOR FOR
SELECT * FROM dbo.fn_ParseCSVString (@data,'-')
DECLARE @varA nvarchar(50)
DECLARE @varB nvarchar(50)
DECLARE @varC nvarchar(50)
DECLARE @varD nvarchar(50)
DECLARE @varE nvarchar(50)
DECLARE @varF nvarchar(50)
DECLARE @varG nvarchar(50)
OPEN c
FETCH NEXT FROM c INTO @varA
IF @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @varB
ELSE
GOTO tes
IF @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @varC
ELSE
GOTO tes
IF @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @varD
ELSE
GOTO tes
IF @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @varE
ELSE
GOTO tes
IF @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @varF
ELSE
GOTO tes
IF @@FETCH_STATUS = 0
FETCH NEXT FROM c INTO @varG
ELSE
GOTO tes
Tes:
CLOSE c
DEALLOCATE c
DECLARE @site nvarchar(50)
DECLARE @room nvarchar(50)
DECLARE @cab nvarchar(50)
DECLARE @device nvarchar(50)
DECLARE @module nvarchar(50)
SELECT @site = @varA + '-' + @varB
SELECT @room = @varA + '-' + @varB + '-' + @varC
SELECT @cab = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
SELECT @device = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
SELECT @module = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG
IF @varA IS NULL
BEGIN
/*
Empty string
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` is a blank field.',
@data,
GetDate()
)
END
ELSE IF @varB IS NULL
BEGIN
/*
Country
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not contain enough data.',
@data,
GetDate()
)
END
ELSE IF @varC IS NULL
BEGIN
/*
Country-Site
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not contain enough data.',
@data,
GetDate()
)
END
ELSE IF @varD IS NULL
BEGIN
/*
Country-Site-Building
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not contain enough data.',
@data,
GetDate()
)
END
ELSE IF @varE IS NULL
BEGIN
/*
Country-Site-Building-Room
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not contain enough data.',
@data,
GetDate()
)
END
ELSE IF @varF IS NULL
BEGIN
/*
Country-Site-Building-Room-Cab
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not contain enough data.',
@data,
GetDate()
)
END
ELSE IF @varG IS NULL
BEGIN
IF @superequip IS NOT NULL
BEGIN
DECLARE @ManDeviceUID INT
DECLARE @ManDeviceCatUID INT
DECLARE @ManDeviceID nvarchar(50)
DECLARE @ManModuleCatUID INT
DECLARE @ManSlotUID INT
SELECT @ManDeviceID = @varA+'-'+@varB+'-'+@varC+'-'+@varD+'-'+@varE+'-'+@varF
SELECT @ManDeviceUID = UID, @ManDeviceCatUID = DeviceCatID FROM dbo.tblDevice WHERE DeviceID = @ManDeviceID
SELECT @ManModuleCatUID = UID FROM dbo.tblModule_Cat WHERE ProductNumber = @material
SELECT @ManSlotUID = UID FROM dbo.tblSlot_Cat WHERE DeviceCatID = @ManDeviceCatUID AND SlotNumber = @position
IF @ManDeviceUID IS NOT NULL
BEGIN
/*
Insert Management Module Record
*/
INSERT INTO dbo.tblModule
(
DeviceID,
ModuleCatID,
SerialNumber,
Module_FL,
SlotID,
SAP_EQ
) VALUES (
@ManDeviceUID,
@ManModuleCatUID,
@serial,
@data,
@ManSlotUID,
@equipment
)
/*
Insert Ports
*/
INSERT INTO dbo.tblPorts
(
ModuleID,
PortCatID,
Man_PortName,
Man_PortType,
Man_PortPos,
NodeName,
Man_Left,
Man_Top,
Man_Right,
Man_Bottom
)
SELECT
@@IDENTITY,
UID,
PortName,
PortType,
PortPos,
'Module:'+@@IDENTITY,
[Left]
[Top],
[Right],
[Bottom]
FROM dbo.tblPort_Cat
WHERE ModuleCatID = @ManModuleCatUID
/*
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Insert',
'Inserted: `' + @data + '` into Management Module table.',
@data,
GetDate()
)
END
ELSE IF @ManModuleCatUID IS NOT NULL
BEGIN
/*
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not have a matching DeviceID.',
@data,
GetDate()
)
END
ELSE
BEGIN
/*
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Error',
'Error: `' + @data + '` does not have a matching SlotID.',
@data,
GetDate()
)
END
END
IF @systemstatus = 'INST'
BEGIN
IF EXISTS(SELECT 1 FROM dbo.tblDevice WHERE SAP_EQ = @Equipment AND DeviceID = @data)
BEGIN
/*
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Update',
'Update for `' + @Equipment + '` not required.',
@data,
GetDate()
)
END
ELSE IF EXISTS(SELECT 1 FROM dbo.tblDevice WHERE SAP_EQ = @Equipment)
BEGIN
DECLARE @varCabID nvarchar(50)
SELECT @varCabID = UID FROM dbo.tblCab WHERE Cab_FL = @cab
/*
Update tblDevice
*/
UPDATE dbo.tblDevice
SET DeviceID = @data,
CabID = @varCabID
WHERE SAP_EQ = @Equipment
/*
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Update',
'Updated `' + @Equipment + '` in tblDevice.',
@data,
GetDate()
)
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM dbo.tblCab WHERE Cab_FL = @cab)
BEGIN
DECLARE @varUID nvarchar(50)
DECLARE @DeviceCatID INT
SELECT @varUID = UID FROM dbo.tblCab WHERE Cab_FL = @cab
SELECT @DeviceCatID = UID FROM dbo.tblDevice_Cat WHERE Model = @description
/*
Insert new Device Record
*/
INSERT INTO dbo.tblDevice
(
CabID,
DeviceID,
DeviceCatID,
SAP_EQ,
SerialNumber,
RackMounted
) VALUES (
@varUID,
@data,
@DeviceCatID,
@Equipment,
@serial,
1
)
/*
Insert Ports
*/
INSERT INTO dbo.tblPorts
(
DeviceID,
PortCatID,
Man_PortName,
Man_PortType,
Man_PortPos,
NodeName,
Man_Left,
Man_Top,
Man_Right,
Man_Bottom
)
SELECT
@@IDENTITY,
UID,
PortName,
PortType,
PortPos,
'Module:'+@@IDENTITY,
[Left]
[Top],
[Right],
[Bottom]
FROM dbo.tblPort_Cat
WHERE ModuleCatID = @ManModuleCatUID
/*
Update Log File
*/
INSERT INTO dbo.tblLogs
(
Action,
Description,
FunctLocation,
[Timestamp]
) VALUES (
'Insert',
'Inserted `' + @cab + '` into Device `Front` Table',
@data,
GetDate()
)
IF EXISTS(SELECT 1 FROM dbo.tblDevice_Cat WHERE Model = @description)
BEGIN
DECLARE @varCatUID nvarchar(50)
SELECT @varCatUID = UID FROM dbo.tblDevice_Cat WHERE Model = @description
/*
Update DeviceCatID in tblDevice