asked on

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

Pratima

Yes you can get all data frm one query like this

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

Alpesh Patel

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

Pratima

PatelAlpesh , what is the difference between your and my answer ?

TheWebGuy38

ASKER

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

Pratima

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

Pratima

If that not work do one thing
check what you are getting in

print @CheckMakePulic

and change the coditon on that

TheWebGuy38

ASKER

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

Pratima

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

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TheWebGuy38

ASKER

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.

Pratima

glad to help you :)