?
Solved

Building a Stored Procedure based on parameters and variables

Posted on 2005-05-08
1
Medium Priority
?
189 Views
Last Modified: 2010-08-05
If you were building a Stored Procedure and working on the FROM clause, how would you code it to handle a situation where the FROM clause part of the SQL statement that you are building could have 1 of 2 outcomes ?

Let's assume a parameter named @DomFor is passed to the Stored Proc and if it has a value of 1, then the first statement that follows is used to build the FROM clause. If @DomFor has a value of 2, then the second statement is used to build the FROM clause.

1)
FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON
S.StateFS =  C.State

2)
FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON
S.StateFS <>  C.State
 
0
Comment
Question by:zimmer9
1 Comment
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13955660
If @DomFot = 1
   Begin
        ...
      FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON
         S.StateFS =  C.State
   End
Else
   Begin
          ....
        FROM tblStatesAll AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON
        S.StateFS <>  C.State
    End
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question