How to use a select statement to get a parameter

• NineIron

  SSChampion

  Points: 12539

  More actions

  March 21, 2017 at 11:17 am

  #336167

  Here's the beginning of my sp. I want to be able to supply the AccountNumber when executing the procedure then, the @VisitID will be supplied by the set statement. Is this possible?

  
ALTER procedure [dbo].[spRadialAnalyticsDataExtract_ADLAssessment]
(
@VisitID varchar(30),
@AccountNumber varchar(30)
)
as
set @VisitID=(select VisitID from livefdb.dbo.RegAcct_Main where SourceID='BRO' and AccountNumber=@AccountNumber)


  Execute stored procedure...............
  exec spRadialAnalyticsDataExtract_ADLAssessment '2345678'

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 21, 2017 at 11:32 am

  #1934202

  Try something like:

  ALTER procedure [dbo].[spRadialAnalyticsDataExtract_ADLAssessment]
(
@AccountNumber varchar(30)
)
as

  DECLARE @VisitID varchar(30)

  SELECT @VisitID = VisitID from livefdb.dbo.RegAcct_Main where SourceID='BRO' and AccountNumber=@AccountNumber)

  You'd want to keep in mind that you need to make sure you aren't going to have multiple, different VisitIDs with that query.

  Sue

• NineIron

  SSChampion

  Points: 12539

  More actions

  March 21, 2017 at 11:49 am

  #1934205

  Wonderful. Thanx.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  March 21, 2017 at 12:07 pm

  #1934208

  Or, if you want to allow @VisitID to be specified but not to require it:

  
ALTER procedure [dbo].[spRadialAnalyticsDataExtract_ADLAssessment]
(
@AccountNumber varchar(30),
@VisitID varchar(30) = NULL
)
AS
IF @VisitID IS NULL
    SET @VisitID=(select VisitID from livefdb.dbo.RegAcct_Main where SourceID='BRO' and AccountNumber=@AccountNumber)

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  March 21, 2017 at 2:39 pm

  #1934224

  One rule of thumb I follow is:
  If you want the user to be able to enter a value, include it as a required parameter, but supply a default in case the user leaves it out. Or have coding to alert the user that the parameter is required. - Scott's post.
  If the value is only going to be set by some other code within the stored procedure, then the parameter/variable needs to be within the stored proc - Sue's post.

  So it all depends on how the value can be set.

  -SQLBill