April 21, 2010 at 10:54 am
I am trying to convert this query into a stored proecedure. I need a parameter for Name and a StartDate/EndDate. These paramaters will be added to an ASPX page for user input so that the results are displayed back to the use in a GridView.
Thanks in advance.
SELECT
(select name+','+state from [nms_cfg].[dbo].[ATCS_Group] where Group_Address = [WEA])as Wayside
,(SELECT 'G'
+SUBSTRING (CONVERT(varchar(100), CAST(WEA AS decimal(38,2))),3, 3 )
+'/'
+ SUBSTRING (CONVERT(varchar(100), CAST(WEA AS decimal(38,2))),6,3 )
) as 'Group ID'
,(SELECT [Name]+','+[State] FROM [NMS_CFG].[dbo].[Base_Equipment] where Base_Equip_Address= (select top 1 Base_Equip_Address from [NMS_CFG].[dbo].[be_xref_oa] where x_pbase = master.dbo.ufnStringToPbase([base1]) ))
+ ' ( ' + [base1] + ')' as Base1
,[SSI1]
,(SELECT [Name]+','+[State] FROM [NMS_CFG].[dbo].[Base_Equipment] where Base_Equip_Address= (select top 1 Base_Equip_Address from [NMS_CFG].[dbo].[be_xref_oa] where x_pbase = master.dbo.ufnStringToPbase([base2]) ))
+ ' ( ' + [base2] + ')' as Base2
,[SSI2]
,(SELECT [Name]+','+[State] FROM [NMS_CFG].[dbo].[Base_Equipment] where Base_Equip_Address= (select top 1 Base_Equip_Address from [NMS_CFG].[dbo].[be_xref_oa] where x_pbase = master.dbo.ufnStringToPbase([base3]) ))
+ ' ( ' + [base3] + ')' as Base3
,[SSI3]
,cast([Date_Time]as Date) as Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
order by WEA, Date_Time
Brian
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 1:52 am
What can I say? Good luck and go ahead 😛
I don't see the question here... Which problems did you find doing this?
If you are looking for a sort of template to do this it could be something like:
CREATE PROCEDURE someProcedure
@someParameter VARCHAR(500)
AS
BEGIN
SET NOCOUNT ON
SELECT ( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
) AS Wayside,
( SELECT 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
) AS 'Group ID',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS Base1,
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS Base2,
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS Base3,
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage]
WHERE WaySide = @someParameter
ORDER BY WEA, Date_Time
END
Is this what you're after?
-- Gianluca Sartori
April 22, 2010 at 6:39 am
That is what I was looking for. I appologize for not asking it well.
Thank you very much!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 6:55 am
You're welcome!
Glad this solved your issue
-- Gianluca Sartori
April 22, 2010 at 7:52 am
Looks like I still have a problem with the Parameter type.
I am passing the parameter from asp.net to the SP. When I run in the debugger I get an error "Converting varchar to float".
This subquery;
( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
) AS Wayside
The Name and State portion are both varchar's. the Group_Address is a FLOAT.
Do I need to cast/convert this into a different data type so that I can pass the data to the SP?
If so, then what type do I need to change it to?
Thanks,
Hope this is better worded...
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 8:00 am
You'd better post the whole procedure text, including parameters and their data type.
It would also be extremely useful if you could post table scripts, in order to understand which data types are involved.
-- Gianluca Sartori
April 22, 2010 at 8:35 am
Here is the ATCS_Group
USE [NMS_CFG]
GO
/****** Object: Table [dbo].[ATCS_Group] Script Date: 04/22/2010 14:15:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ATCS_Group](
[Group_Address] [float] NOT NULL,
[Subregion] [int] NULL,
[Line_Address] [float] NOT NULL,
[left_neighbor_group] [float] NULL,
[right_neighbor_group] [float] NULL,
[ATCS_Group] [int] NOT NULL,
[Name] [varchar](50) NULL,
[State] [varchar](3) NULL,
[Description] [varchar](255) NULL,
[Milepost] [varchar](12) NULL,
[Longitude] [varchar](20) NULL,
[Latitude] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And this is the RT_Group_Coverage
USE [NMS_RT]
GO
/****** Object: Table [dbo].[RT_Group_Coverage] Script Date: 04/22/2010 14:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RT_Group_Coverage](
[WEA] [float] NOT NULL,
[Date_Time] [datetime] NOT NULL,
[Base1] [char](10) NULL,
[Base2] [char](10) NULL,
[Base3] [char](10) NULL,
[SSI1] [int] NULL,
[SSI2] [int] NULL,
[SSI3] [int] NULL,
[Lock1] [bit] NULL,
[Lock2] [bit] NULL,
[Lock3] [bit] NULL,
[Pref_base] [char](10) NULL,
[dbu] [bit] NULL,
[alarm] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Here is a line of what each table looks like;
Each Column is seperated by a PIPE "|" symbol.
ATCS_Group
Group_Address|Subregion|Line_Address|left_neighbor_group|right_neighbor_group|ATCS_GroupName|State|Description|Milepost|Longitude|Latitude
762020020000|NULL|762020000000|NULL|NULL|2|Denver Test 02|??|NULL|NULL|NULL|NULL
RT_Group_Coverage
WEA|Date_Time|Base1|Base2|Base3|SSI1|SSI2|SSI3|Lock1|Lock2|Lock3|Pref_base|dbu|alarm
761920230000|2008-06-17|16:33:25.000|210.2.01|217.1.01|213.2.01|118|109|22|0|0|0|210.2.01|0|0
Here is the User Function that converts the numeric number e.g. 217.1.01 to a name.
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[ufnStringToPbase] Script Date: 04/22/2010 14:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[ufnStringToPbase](@string char(8))
returns int
as
begin
declare @line int, @port int,@poll int,@pbase int
SET @line = 0
SET @port = 0
SET @poll = 0
set @pbase=0
set @line =
(ASCII(substring(@string,1,1)))
set @line =
(ASCII(substring(@string,1,1))-48)*100 +
(ASCII(substring(@string,2,1))-48)*10 +
(ASCII(substring(@string,3,1))-48)
set @port =
(ASCII(substring(@string,5,1))-48)
set @poll =
(ASCII(substring(@string,7,1))-48)*10 +
(ASCII(substring(@string,8,1))-48)
set @pbase = (@line * 1000)+(@port*100)+(@poll)+6000000
return @pbase
end
GO
This is the table that the User Function runs against.
SE [NMS_CFG]
GO
/****** Object: Table [dbo].[Base_Equipment] Script Date: 04/22/2010 14:31:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Base_Equipment](
[Base_Equip_Address] [float] NOT NULL,
[Subregion] [int] NULL,
[Equipment_Type] [int] NOT NULL,
[Node] [int] NOT NULL,
[Device] [int] NOT NULL,
[Office_App_Address] [float] NOT NULL,
[Port_Number] [smallint] NOT NULL,
[Poll_Address] [smallint] NOT NULL,
[Name] [varchar](50) NULL,
[State] [varchar](3) NULL,
[Description] [varchar](255) NULL,
[HW_Version] [varchar](12) NULL,
[SW_Version] [varchar](12) NULL,
[Date_In_Service] [datetime] NULL,
[Longitude] [varchar](20) NULL,
[Latitude] [varchar](20) NULL,
[cov_region] [smallint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
This is a sample of what the table has in it;
Base_Equip_Address|Subregion|Equipment_Type|Node|Device|Office_App_Address|Port_Number|Poll_Address|Name|State|Description|HW_Version|SW_Version|Date_In_Service|Longitude|Latitude|cov_region
76016260|NULL|213|1|6260|999999999|0|1|Brush IP GTC|CO|CNA1000 IP Address 172.22.96.75|NULL|NULL|2009-05-14 00:00:00.000|NULL|NULL|0
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 8:45 am
Very well. Still missing definition for table [be_xref_oa]. Can you post it?
Can you post the procedure signature as well?
-- Gianluca Sartori
April 22, 2010 at 8:54 am
Sorry, thought I got all of them.
Here is the be_xref_oa
USE [NMS_CFG]
GO
/****** Object: Table [dbo].[be_xref_oa] Script Date: 04/22/2010 14:51:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_xref_oa](
[base_equip_address] [float] NOT NULL,
[office_app_address] [float] NOT NULL,
[x_pbase] [int] NOT NULL,
[region] [int] NOT NULL,
[port] [tinyint] NOT NULL,
[poll] [tinyint] NOT NULL,
[xprimary] [bit] NOT NULL,
[path_type] [tinyint] NOT NULL,
[designator] [nvarchar](24) NULL
) ON [PRIMARY]
GO
This is the sample results of the table;
base_equip_address|office_app_address|x_pbase|region|port|poll|xprimary|path_type|designator
22015036|22016487|6487105|6487|0|5|1|0|NULL
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 9:05 am
Which datatype did you choose for the stored procedure parameter?
Which table field are you comparing to the parameter?
-- Gianluca Sartori
April 22, 2010 at 9:10 am
Gianluca Sartori (4/22/2010)
Which datatype did you choose for the stored procedure parameter?Which table field are you comparing to the parameter?
I chose VARCHAR for the SP, and compared it to the RT_Group_Coverage table Column WEA.
I also tried FLOAT, and REAL.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 9:20 am
The parameter should be definded as float, if it holds float data and is compared to a float column.
What value are you passing?
-- Gianluca Sartori
April 22, 2010 at 9:23 am
Gianluca Sartori (4/22/2010)
The parameter should be definded as float, if it holds float data and is compared to a float column.What value are you passing?
I am passing the name of the location to the SP.
The users know the name of locations, not the value of the WEA column.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 22, 2010 at 9:27 am
Try defining the parameter as varchar(50) and filter it in the query like this:
( SELECT name + ',' + state
FROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
AND name = @param
) AS Wayside
Does it work?
-- Gianluca Sartori
April 22, 2010 at 9:36 am
Gianluca Sartori (4/22/2010)
Try defining the parameter as varchar(50) and filter it in the query like this:
( SELECT name + ',' + stateFROM [nms_cfg].[dbo].[ATCS_Group]
WHERE Group_Address = [WEA]
AND name = @param
) AS Wayside
Does it work?
for the AND name = @param, should I enter the parameter name? e.g. @Wayside or leave it as is?
I tried it with the @Wayside, and I get the same error Error converting varchar to float.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply