DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: MS SQL 2008: Unsure on how to change views into stored procedures

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    MS SQL 2008: Unsure on how to change views into stored procedures

    First of all, I am pretty new to SQL so don't be too hard on me if what I have done so far is totally wrong :P

    Basically I am trying to make a booking system to display a Gridview table in ASP.NET.

    The Gridview table will show a crosstab equivalent query I have made in SQL Express 2008 which to be honest works great!

    I have 2 Views involved which I'll show somehow (sorry this might look messy)


    dbo.qry_AllFields (Gathers all fields needed)

    Code:
    SELECT     dbo.tbl_Bookings.BookingID, dbo.tbl_Bookings.BookingDate, dbo.tbl_Status.Status, dbo.tbl_Teachers.Username, dbo.tbl_Usergroups.Usergroup, 
                          dbo.tbl_Periods.PeriodID, dbo.tbl_Weeks.WeekBeginning, dbo.tbl_Bookings.Reason
    FROM         dbo.tbl_Bookings INNER JOIN
                          dbo.tbl_Status ON dbo.tbl_Bookings.StatusID = dbo.tbl_Status.StatusID INNER JOIN
                          dbo.tbl_Teachers ON dbo.tbl_Bookings.TeacherID = dbo.tbl_Teachers.TeacherID INNER JOIN
                          dbo.tbl_Usergroups ON dbo.tbl_Teachers.GroupID = dbo.tbl_Usergroups.GroupID INNER JOIN
                          dbo.tbl_Weeks ON dbo.tbl_Bookings.WeekID = dbo.tbl_Weeks.WeekID INNER JOIN
                          dbo.tbl_Periods ON dbo.tbl_Bookings.PeriodID = dbo.tbl_Periods.PeriodID
    WHERE     (dbo.tbl_Weeks.WeekBeginning = CONVERT(DATETIME, '2009-02-03 00:00:00', 103))

    and dbo.qry_BookingsTable (crosstab attempt which uses first View)

    Code:
    SELECT     dbo.tbl_Periods.PeriodID, MAX(CASE DATENAME(WEEKDAY, BookingDate) WHEN 'Monday' THEN Username END) AS Monday, MAX(CASE DATENAME(WEEKDAY, 
                          BookingDate) WHEN 'Tuesday' THEN Username END) AS Tuesday, MAX(CASE DATENAME(WEEKDAY, BookingDate) WHEN 'Wednesday' THEN Username END) 
                          AS Wednesday, MAX(CASE DATENAME(WEEKDAY, BookingDate) WHEN 'Thursday' THEN Username END) AS Thursday, MAX(CASE DATENAME(WEEKDAY, 
                          BookingDate) WHEN 'Friday' THEN Username END) AS Friday
    FROM         dbo.qry_AllFields RIGHT OUTER JOIN
                          dbo.tbl_Periods ON dbo.qry_AllFields.PeriodID = dbo.tbl_Periods.PeriodID
    GROUP BY dbo.tbl_Periods.PeriodID
    It works nicely but I need to only show bookings with a certain 'week beginning' date
    Both work fine in View form but I think I need change these into stored procedures so that I can use parameters to filter the bookings in dbo.qry_AllFields

    To be specific:

    WHERE (dbo.tbl_Weeks.WeekBeginning = CONVERT(DATETIME, @WeekBeginning, 103)) I think.


    I also need to keep all 5 rows showing each period which I managed to get my View showing finally.

    However I can't work out the best approach to take to being able to use parameters


    Hope this makes sense a little at least

    Many many thanks for any help or even criticisms offered,
    Alex

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by Exias View Post
    However I can't work out the best approach to take to being able to use parameters
    The answer to that question depends largely on what you will be using to pass the parameters.

    Are you going to run this code directly from SQL Server or are you going to run this code from an application that uses the SQL Server database as a backend?

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Thank you for replying Hack, what I wanted to do was create a web application using SQL as a backend to lookup and store the data, using query strings to pass the parameter in the URL, which I think I should be able to do. It's just changing those queries into stored procedures which I have just ran out of ideas for.
    Last edited by Exias; 03-05-2009 at 10:50 AM. Reason: More precise answer

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Ok, and what will you be using to develop the web app?

    Classic ASP?
    ASP.NET
    Java?
    PHP?
    Something entirely different?

  5. #5
    Join Date
    Mar 2009
    Posts
    3
    Visual Studio (VB) 2008 along with MS SQl Server 2008 (Express)

    and ASP.NET (3.5)

Similar Threads

  1. Replies: 4
    Last Post: 12-12-2005, 11:47 AM
  2. Returning errors from SQL Server Stored Procs
    By Khalizan in forum VB Classic
    Replies: 1
    Last Post: 11-28-2001, 02:32 AM
  3. SQL Stored Procedures
    By Randy Lee in forum Database
    Replies: 2
    Last Post: 10-26-2001, 01:47 PM
  4. ACCESS XP, SQL Server 2000 Stored Procedures
    By Mike in forum VB Classic
    Replies: 0
    Last Post: 08-11-2001, 09:44 PM
  5. Dynamic SQL or Stored Procedures..your opinion
    By Jamie Cross in forum authorevents.mitchell
    Replies: 1
    Last Post: 10-17-2000, 05:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center