By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,934 Members | 1,804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,934 IT Pros & Developers. It's quick & easy.

Convert a multiple table query to Stored Procedure

P: 4
I have a rather complicated query that I need to convert to a Stored Procedure so that I can more easily call this query in an asp.net page.

The idea is to have two calander controls on the web page, and textbox/button.

Enter the name of the location and select the date range from the calander. Click the button and display the results in a GridView.

Paramaters needed are Name, StartDate and EndDate.

Here is the query
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.                     (select name+','+state from [nms_cfg].[dbo].[ATCS_Group] where Group_Address = [WEA])as Wayside
  3.                     ,(SELECT 'G'
  4.                     +SUBSTRING (CONVERT(varchar(100), CAST(WEA AS decimal(38,2))),3, 3 )
  5.                     +'/'
  6.                     + SUBSTRING (CONVERT(varchar(100), CAST(WEA AS decimal(38,2))),6,3 )
  7.                     ) as 'Group ID'
  8.                     ,(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]) )) 
  9.                     + '  ( ' + [base1] + ')' as Base1
  10.                     ,[SSI1]
  11.                     ,(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]) )) 
  12.                     + '  ( ' + [base2] + ')' as Base2
  13.                     ,[SSI2]
  14.                     ,(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]) )) 
  15.                     + '  ( ' + [base3] + ')' as Base3
  16.                     ,[SSI3]          
  17.                     ,cast([Date_Time]as Date) as Date
  18.                     FROM [NMS_RT].[dbo].[RT_Group_Coverage]
  19.                     order by WEA, Date_Time
  20.  
Thanks
Apr 21 '10 #1
Share this Question
Share on Google+
1 Reply


yarbrough40
100+
P: 320
you failed to actually ask a question... are you wondering about the syntax of creating a stored procedure? or are you curious on how to create and apply @variables? are you stuck on how to call a procedure from a .net app?
Apr 22 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more Microsoft SQL Server Questions on Bytes