Complicated Stored Procedure - Need Guidance


  • Hello All, I am developing a ASP.NET webforms app using SQL Server 2008 R2 Database. AppData database for logging in is stored on same SQL Server (I plan to have other intranet sites to use them). This is my first attempt at web development, so please be kind.

    First, I have a winforms client/server app that is the main application we use in house. I am making a piece of it available to our employees on construction sites out of the office, but connected through VPN. The winforms application has a specific username and password and through logging in I have some global variables stored that dictates their authorization and security levels.

    The webforms application, I have replicated their un/pw stored in the config, this way their UserName ID and Password ID on the webform controls will match what is in our database. Here is where I am needing help, The users that will be using this are either a project manager or a superintendent. Project Managers have more than one project ongoing and Superintendents have only one assigned. In the tblProjects table I have a field "PMID" that holds an integer value for that project manager, and I have a field "SuptID" that holds an integer value with the superintendents ID. When the user logs in successfully I need the next page to be a page that shows the projects that they are assigned. I need to create a Stored Proc that will query tblLogin to select their personal ID based on the username, then another query to query tblProjects 'PMID' and 'SuptID', and then an If statement to see which field is not null, and then query the projects based on the user personal ID. Then I will need to display the list of projects for them to select.

    Any help and guidance will be greatly appreciated. Thanks in advance.

    Saturday, December 21, 2013 5:11 PM


  • As I understand what you need is to pass username as a parameter to stored procedure and retrieve the id and using it retrieve the project id. 

    ie something like

    @UserName varchar(100)
    DECLARE @UserID int
    SELECT @UserID = UserID
    FROM tblLogin
    WHERE userName = @UserName
    SELECT *
    FROM tblProjects
    WHERE PM_ID = @userID
    OR SuptID = @UserID

    I've assumed column names so make sure you use correct column names instead

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Saturday, December 21, 2013 5:21 PM

All replies