SQL query syntax question

I'm trying to figure out some syntax

I have a stored procedure that checks the account type, then gathers the field data accordingly

the line
SET @Address1 = (SELECT Address1 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID))

I need to get all the fields,

do I do it like this

SET @Address1 = (SELECT Address1 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID))
SET @Address2 = (SELECT Address2 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID))

or can I do it something like

SET @Address1, address2 = (SELECT Address1, address2 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID))

etc

I know the first way will work, but it seems like a waster of resources

any input would be great








 
USE [ReefJunkiesDev]
GO
/****** Object:  StoredProcedure [dbo].[GetAddressByAccountType]    Script Date: 04/24/2011 21:23:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetAddressByAccountType]

@UserAccountsID int

As

Set Nocount ON


Declare @AccountType int
Declare @Address1 varchar(max)
Declare @Address2 varchar(max)
Declare @City varchar(max)
Declare @Region varchar(max)
Declare @PostalCode varchar(max)
Declare @Country varchar(max)

Declare @CheckMakePulic int

 
 SET @AccountType = (SELECT AccountType.AccountTypeID
	FROM  UserAccountsDetails INNER JOIN
               UserAccounts ON UserAccountsDetails.UserAccountsID = UserAccounts.UserAccountsID INNER JOIN
               AccountType ON UserAccountsDetails.AccountTypeID = AccountType.AccountTypeID
	WHERE (UserAccounts.UserAccountsID = @UserAccountsID))
	
		IF (@AccountType = 1) Or (@AccountType = 3) Or (@AccountType = 4) 
		Begin
		
			SET @Address1 = (SELECT Address1 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID))
		End
	else if (@AccountType = 2)
		Begin
			SET @Address1 = (SELECT CompanyAddress1 FROM UserAccountsCompany WHERE (UserAccountsID = @UserAccountsID))
		End
	else if (@AccountType = 5)
		Begin
			SET @Address1 = (SELECT  ClubAddress1 FROM FishClubs WHERE (UserAccountsID = @UserAccountsID))
		End

	SET @CheckMakePulic = (SELECT MakeContactInfoPublic FROM UserAccountsSettings WHERE (UserAccountsID = @UserAccountsID))
	
	if (@CheckMakePulic = 1)
		Begin
			
			SELECT @Address1 As 'Address1'
			SELECT @Address2 As 'Address2'
			SELECT @City As 'City'
			SELECT @Region As 'Region'
			SELECT @PostalCode As 'PostalCode'
			SELECT @Country As 'Country'
	
		End
	else if (@AccountType = 0)
		Begin
		
			SELECT @City As 'City'
			SELECT @Region As 'Region'
			SELECT @PostalCode As 'PostalCode'
			SELECT @Country As 'Country'
		End

Open in new window

TheWebGuy38Asked:
Who is Participating?
 
Pratima PharandeCommented:
also change it like this

if (@CheckMakePulic = 'True')
            Begin
                  
                  SELECT @Address1 As 'Address1', @Address2 As 'Address2',@City As 'City',@Region As 'Region',@PostalCode As 'PostalCode'
                  
      
            End
      else if (@CheckMakePulic = 'False')
            Begin
            
                        SELECT '' As 'Address1', '' As 'Address2',@City As 'City',@Region As 'Region',@PostalCode As 'PostalCode'
            End
0
 
Pratima PharandeCommented:
Yes you can get all data frm one query like this

SELECT @Address1 = Address1 , @Address2= Address2 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID)
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT @Address1 = Address1, @address2 = address2 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pratima PharandeCommented:
PatelAlpesh , what is the difference between your and my answer ?
0
 
TheWebGuy38Author Commented:
yes, I think that worked.

now
something isn't working with the bit field

SET @CheckMakePulic = (SELECT MakeContactInfoPublic FROM UserAccountsSettings WHERE (UserAccountsID = @UserAccountsID))
      
      if (@CheckMakePulic = 'True')
            Begin
                  
                  SELECT @Address1 As 'Address1'
                  SELECT @Address2 As 'Address2'
                  SELECT @City As 'City'
                  SELECT @Region As 'Region'
                  SELECT @PostalCode As 'PostalCode'
                  SELECT @Country As 'Country'
      
            End
      else if (@CheckMakePulic = 'False')
            Begin
            
                  SELECT @City As 'City'
                  SELECT @Region As 'Region'
                  SELECT @PostalCode As 'PostalCode'
                  SELECT @Country As 'Country'
            End
      

I don't think it is using the true, or the false


 
USE [ReefJunkiesDev]
GO
/****** Object:  StoredProcedure [dbo].[GetAddressByAccountType]    Script Date: 04/24/2011 21:23:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetAddressByAccountType]

@UserAccountsID int

As

Set Nocount ON


Declare @AccountType int

Declare @Address1 varchar(max)
Declare @Address2 varchar(max)
Declare @City varchar(max)
Declare @Region varchar(max)
Declare @PostalCode varchar(max)
Declare @Country varchar(max)

Declare @CheckMakePulic bit

 
 SET @AccountType = (SELECT AccountType.AccountTypeID
	FROM  UserAccountsDetails INNER JOIN
               UserAccounts ON UserAccountsDetails.UserAccountsID = UserAccounts.UserAccountsID INNER JOIN
               AccountType ON UserAccountsDetails.AccountTypeID = AccountType.AccountTypeID
	WHERE (UserAccounts.UserAccountsID = @UserAccountsID))
	
		IF (@AccountType = 1) Or (@AccountType = 3) Or (@AccountType = 4) 
		Begin
		
		SELECT @Address1 = Address1 , @Address2 = Address2, @City = City, @Region = Region, @PostalCode = PostalCode, @Country = Country FROM  UserAccounts INNER JOIN
               Countries ON UserAccounts.CountryID = Countries.CountryID INNER JOIN
               Regions ON UserAccounts.StateID = Regions.RegionID
WHERE (UserAccounts.UserAccountsID = @UserAccountsID)
		
		End
	else if (@AccountType = 2)
		Begin
			
			SELECT @Address1 = CompanyAddress1 , @Address2 = CompanyAddress2, @City = CompanyCity, @Region = Region, @PostalCode = CompanyPostalCode, @Country = Country FROM  UserAccountsCompany INNER JOIN
               Countries ON UserAccountsCompany.CompanyCountryID = Countries.CountryID INNER JOIN
               Regions ON UserAccountsCompany.CompanyStateID = Regions.RegionID
WHERE (UserAccountsCompany.UserAccountsID = @UserAccountsID)
		
		End
	else if (@AccountType = 5)
		Begin
			
			SELECT @Address1 = ClubAddress1 , @Address2 = ClubAddress2, @City = ClubCity, @Region = Region, @PostalCode = ClubPostalCode, @Country = Country FROM  FishClubs INNER JOIN
               Regions ON FishClubs.ClubStateID = Regions.RegionID INNER JOIN
               Countries ON FishClubs.ClubCountryID = Countries.CountryID
WHERE (FishClubs.UserAccountsID = @UserAccountsID)
		
		End

	SET @CheckMakePulic = (SELECT MakeContactInfoPublic FROM UserAccountsSettings WHERE (UserAccountsID = @UserAccountsID))
	
	if (@CheckMakePulic = 'True')
		Begin
			
			SELECT @Address1 As 'Address1'
			SELECT @Address2 As 'Address2'
			SELECT @City As 'City'
			SELECT @Region As 'Region'
			SELECT @PostalCode As 'PostalCode'
			SELECT @Country As 'Country'
	
		End
	else if (@CheckMakePulic = 'False')
		Begin
		
			SELECT @City As 'City'
			SELECT @Region As 'Region'
			SELECT @PostalCode As 'PostalCode'
			SELECT @Country As 'Country'
		End

Open in new window

0
 
Pratima PharandeCommented:
I think it will check 0 and 1


  if (@CheckMakePulic = 1)
            Begin
                 
                  SELECT @Address1 As 'Address1'
                  SELECT @Address2 As 'Address2'
                  SELECT @City As 'City'
                  SELECT @Region As 'Region'
                  SELECT @PostalCode As 'PostalCode'
                  SELECT @Country As 'Country'
     
            End
      else if (@CheckMakePulic = 0)
            Begin
           
                  SELECT @City As 'City'
                  SELECT @Region As 'Region'
                  SELECT @PostalCode As 'PostalCode'
                  SELECT @Country As 'Country'
            End
0
 
Pratima PharandeCommented:
If that not work do one thing
check what you are getting in

print @CheckMakePulic

and change the coditon on that
0
 
TheWebGuy38Author Commented:
Actually, I think it was working the whole time.

My bad.

I was passing the wrong ID into the stored procedure

it seems to be working, but now the code is blowing up when it passes back the procedure.

I get

Address2
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: Address2

Source Error:

Line 1324:
Line 1325:            Dim Address1 = Dr("Address1") & ""
Line 1326:            Dim Address2 = Dr("Address2") & ""
Line 1327:            Dim City = Dr("City") & ""
Line 1328:            Dim Region = Dr("Region") & ""

Am I not passing it back right?

 
DAL

 Public Function GetAddressByAccountType(ByVal UserAccountsID As Integer) As String

        Dim Conn As New SqlConnection(ConnectionString)
        Dim CmdSelect As New SqlCommand("GetAddressByAccountType", Conn)
        CmdSelect.CommandType = CommandType.StoredProcedure
        CmdSelect.Parameters.Add(New SqlParameter("@UserAccountsID", UserAccountsID))

        ' Try
        Conn.Open()
        Dim Dr As SqlDataReader = CmdSelect.ExecuteReader()

        While Dr.Read()

            Dim Address1 = Dr("Address1") & ""
            Dim Address2 = Dr("Address2") & ""
            Dim City = Dr("City") & ""
            Dim Region = Dr("Region") & ""
            Dim PostalCode = Dr("PostalCode") & ""
            Dim Country = Dr("Country") & ""

            Dim HTML As String = Address1 & "<br>" & City & " " & Region & ", " & PostalCode & "<br>" & Country


            Return HTML

        End While

        ' Catch ex As Exception
        'Throw New ApplicationException("Data Error")
        ' Finally
        'Conn.Close()
        ' End Try

    End Function


SP

USE [ReefJunkiesDev]
GO
/****** Object:  StoredProcedure [dbo].[GetAddressByAccountType]    Script Date: 04/24/2011 21:23:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetAddressByAccountType]

@UserAccountsID int

As

Set Nocount ON


Declare @AccountType int

Declare @Address1 varchar(max)
Declare @Address2 varchar(max)
Declare @City varchar(max)
Declare @Region varchar(max)
Declare @PostalCode varchar(max)
Declare @Country varchar(max)

Declare @CheckMakePulic bit

 
 SET @AccountType = (SELECT AccountType.AccountTypeID
	FROM  UserAccountsDetails INNER JOIN
               UserAccounts ON UserAccountsDetails.UserAccountsID = UserAccounts.UserAccountsID INNER JOIN
               AccountType ON UserAccountsDetails.AccountTypeID = AccountType.AccountTypeID
	WHERE (UserAccounts.UserAccountsID = @UserAccountsID))
	
		IF (@AccountType = 1) Or (@AccountType = 3) Or (@AccountType = 4) 
		Begin
		
		SELECT @Address1 = Address1 , @Address2 = Address2, @City = City, @Region = Region, @PostalCode = PostalCode, @Country = Country FROM  UserAccounts INNER JOIN
               Countries ON UserAccounts.CountryID = Countries.CountryID INNER JOIN
               Regions ON UserAccounts.StateID = Regions.RegionID
WHERE (UserAccounts.UserAccountsID = @UserAccountsID)
		
		End
	else if (@AccountType = 2)
		Begin
			
			SELECT @Address1 = CompanyAddress1 , @Address2 = CompanyAddress2, @City = CompanyCity, @Region = Region, @PostalCode = CompanyPostalCode, @Country = Country FROM  UserAccountsCompany INNER JOIN
               Countries ON UserAccountsCompany.CompanyCountryID = Countries.CountryID INNER JOIN
               Regions ON UserAccountsCompany.CompanyStateID = Regions.RegionID
WHERE (UserAccountsCompany.UserAccountsID = @UserAccountsID)
		
		End
	else if (@AccountType = 5)
		Begin
			
			SELECT @Address1 = ClubAddress1 , @Address2 = ClubAddress2, @City = ClubCity, @Region = Region, @PostalCode = ClubPostalCode, @Country = Country FROM  FishClubs INNER JOIN
               Regions ON FishClubs.ClubStateID = Regions.RegionID INNER JOIN
               Countries ON FishClubs.ClubCountryID = Countries.CountryID
WHERE (FishClubs.UserAccountsID = @UserAccountsID)
		
		End

	SET @CheckMakePulic = (SELECT MakeContactInfoPublic FROM UserAccountsSettings WHERE (UserAccountsID = @UserAccountsID))
	
	if (@CheckMakePulic = 'True')
		Begin
			
			SELECT @Address1 As 'Address1'
			SELECT @Address2 As 'Address2'
			SELECT @City As 'City'
			SELECT @Region As 'Region'
			SELECT @PostalCode As 'PostalCode'
			SELECT @Country As 'Country'
	
		End
	else if (@CheckMakePulic = 'False')
		Begin
		
		    Set @Address1 = null
			Set @Address2 = null
			
		    SELECT @Address1 As 'Address1'
			SELECT @Address2 As 'Address2'
			SELECT @City As 'City'
			SELECT @Region As 'Region'
			SELECT @PostalCode As 'PostalCode'
			SELECT @Country As 'Country'
		End

Open in new window

0
 
Pratima PharandeCommented:
Make sure that you need to send Adress1 and adress 2 in bothe the cases that is true and flase
If you don't have data end null atleast.
as you are trying to fetch the data


if (@CheckMakePulic = 'True')
            Begin
                  
                  SELECT @Address1 As 'Address1'
                  SELECT @Address2 As 'Address2'
                  SELECT @City As 'City'
                  SELECT @Region As 'Region'
                  SELECT @PostalCode As 'PostalCode'
                  SELECT @Country As 'Country'
      
            End
      else if (@CheckMakePulic = 'False')
            Begin
            
                  SELECT '' As 'Address1'
                  SELECT '' 2 As 'Address2'
                  SELECT @City As 'City'
                  SELECT @Region As 'Region'
                  SELECT @PostalCode As 'PostalCode'
                  SELECT @Country As 'Country'
            End
0
 
TheWebGuy38Author Commented:
OH YA! that was it!

works great now!

I appreciate all your guys help. I'm not very good with programming syntax.
getting lil better though.
0
 
Pratima PharandeCommented:
glad to help you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.