I want to run multiple Select queries in one stored procedure

Question

I want to run two select queries in my stored procedure. Currently it is only running one Select query but I want the Stored procedure to run two select queries. The other query I want to run is

Select companyUDIDDescription from companyudid where companyKey=@CompanyKey and isDeleted=0

The above query returns a column which I want to use everytime this stored proc is executed.

    CREATE OR ALTER PROCEDURE [dbo].[GetFilteredUsers]
    (
      @Sitekey   AS INT,
      @CompanyKey   AS INT,
      @FilterType AS SMALLINT,
      @FiterValue VARCHAR(25))
     AS
    BEGIN 
         SELECT userKey
         INTO #TempUsersTable
         FROM Vault..[User]
         WHERE IsDeleted=0 and siteKey=@Sitekey and companyKey=@CompanyKey
    -- ----------------------------------------------------------------Filter based on udid value------------------------------------------------------------------------
        IF(@FilterType) =1 --udid
        BEGIN
            SELECT AUX.userUDIDValue,
                   AUX.userKey,AUX.userLogin,AUX.userCreated,AUX.userFirstName,AUX.userLastName,AUX.userGender,
                   AUX.userBirthdate,AUX.Usertitle,AUX.cellphone,AUX.homephone,AUX.fax,
                   AUX.emergencycontact,AUX.useremail,AUX.emergencyContactRelation,AUX.emergencyPhoneNo1,AUX.emergencyPhoneNo2,
                   AUX.firstPassportName,AUX.firstPassportNo,AUX.firstPassportExpirationDate,
                   AUX.firstPassportIssuingCountry,AUX.redressNumber,AUX.redressNumber2,AUX.secondPassportExpirationDate, 
                   AUX.secondPassportIssuingCountry,AUX.secondPassportname,AUX.ApproverEmail,
                   AUX.originAirportCode, AUX.airSeatingName,AUX.airMealName,
                   AUX.hotelSmokingPreference,
                   AUX.hotelBedPreference,
                   AUX.carVehicleTypeKey,
                   AUX.addressLine1,AUX.addressLine2,AUX.countryCode,AUX.stateCode,AUX.zip ,Aux.city,Aux.groupName
              FROM ( SELECT (SELECT DISTINCT SUBSTRING(
                                (
                                SELECT '|'+ST1.userUDIDValue AS [text()]
                                FROM Vault.dbo.userudid ST1
                                WHERE ST1.userkey = ST2.userkey
                                ORDER BY ST1.userkey
                                FOR XML PATH ('')
                                ), 2, 1000) [Students]
                                FROM Vault.dbo.userudid ST2
                                WHERE ST2.userkey = T.userkey
                                ) AS userUDIDValue,
                           t.userKey,us.userLogin,us.userCreated,us.userFirstName,us.userLastName,us.userGender,us.userBirthdate,
                           p.Usertitle,p.cellphone,p.homephone,p.fax,
                           p.emergencycontact,p.useremail,p.emergencyContactRelation,p.emergencyPhoneNo1,p.emergencyPhoneNo2,p.firstPassportName,
                           p.firstPassportNo,p.firstPassportExpirationDate,
                           p.firstPassportIssuingCountry,p.redressNumber,p.redressNumber2,p.secondPassportExpirationDate, p.secondPassportIssuingCountry,
                           p.secondPassportname,p.ApproverEmail,a.originAirportCode,asl.airSeatingName,aml.airMealName,hsl.hotelSmokingPreference,
                           hbl.hotelBedPreference,c.carVehicleTypeKey,ad.addressLine1,ad.addressLine2,ad.city,ad.countryCode,ad.stateCode,ad.zip,g.groupName
                      FROM #TempUsersTable t 
                      INNER JOIN [user] us ON t.userkey = us.userkey  
                      INNER JOIN UserProfile p  ON t.userkey = p.userkey
                      LEFT OUTER JOIN AirPreference a  ON t.userkey = a.userkey
                      LEFT OUTER JOIN AirSeatingLookup asl ON a.airSeatingKey=asl.airSeatingKey
                      LEFT OUTER JOIN AirMealLookup aml ON a.airMealKey=aml.airMealKey
                      LEFT OUTER JOIN HotelPreference h  ON t.userkey = h.userkey AND h.hotelPreferenceKey = (SELECT MAX(hotelPreferenceKey) FROM HotelPreference WHERE userKey = t.userKey)
                      LEFT OUTER JOIN HotelSmokingLookup hsl ON h.hotelSmokingKey=hsl.hotelSmokingKey 
                      LEFT OUTER JOIN HotelBedLookup hbl ON h.hotelBedKey=hbl.hotelBedKey
                      LEFT OUTER JOIN CarPreference c  ON t.userkey = c.userkey
                      LEFT OUTER JOIN ADDRESS ad ON p.workAddressKey=ad.addressKey
                      LEFT OUTER JOIN [GroupMembers] gm ON us.userKey=gm.groupMemberTableKey
                      LEFT OUTER JOIN [Group] g ON gm.groupKey=g.groupKey
                   ) AUX
             WHERE AUX.userUDIDValue  LIKE '%'+@FiterValue+'%' -- UDid text 
        END

Does this answer your question? [Retrieve data from stored procedure which has multiple result sets](https://stackoverflow.com/questions/20082889/retrieve-data-from-stored-procedure-which-has-multiple-result-sets) — Mark Schultheiss, Jun 24 '21 at 11:49
if I were on your team and saw `Select * from` I would recommend you put the specific columns in not an asterisk for all of them — Mark Schultheiss, Jun 24 '21 at 11:51
Note that your `@FiterValue` is susceptible to sql injection so I hope it is sanitized and not from a user directly. — Mark Schultheiss, Jun 24 '21 at 12:01

score 0 · Answer 1 · answered Jun 24 '21 at 11:49

you can do something like this. first declare a variable for the first select statement result (considering the column name is id)

declare @id int
set @id = (Select id from companyudid where companyKey=@CompanyKey and 
isDeleted=0)

--now you have @id which is the result of the first select statement. and you 
--can use it for the second one in the same procedure

I want to run multiple Select queries in one stored procedure

1 Answers1