?
Solved

SQL query syntax question

Posted on 2011-04-24
11
Medium Priority
?
294 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:TheWebGuy38
  • 7
  • 3
11 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35458083
Yes you can get all data frm one query like this

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

Expert Comment

by:Alpesh Patel
ID: 35458112
SELECT @Address1 = Address1, @address2 = address2 FROM UserAccounts WHERE (UserAccountsID = @UserAccountsID)
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35458125
PatelAlpesh , what is the difference between your and my answer ?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:TheWebGuy38
ID: 35458138
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35458142
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35458149
If that not work do one thing
check what you are getting in

print @CheckMakePulic

and change the coditon on that
0
 

Author Comment

by:TheWebGuy38
ID: 35458204
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35458213
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35458224
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
 

Author Comment

by:TheWebGuy38
ID: 35458262
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35458274
glad to help you :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

864 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