[dba-SQLServer]Prompting for Value

In a sproc this would look like this...

CREATE PROCEDURE sproc_MyPromoID (@Promo_ID AS Integer)
AS
SELECT prospect.prospect_no, prospect.prospect_name,
     prospect.prospect_addr1, prospect.prospect_addr2,
     prospect.city_name, prospect.state_id, prospect.zip_code,
     prospect.branch_no, prospect.mktsrc_id, prospect.phone1,
     prospect.prospstat_id, prospect.sales_emp_no, prospect.udf9,
     prospect.udf10, prospect.promo_id,
     prospect.referring_cust_no, prospect.udf7,
     prospect_archive.prospstat_id AS prospstat_id_archive,
     customer.cust_name, customer.cust_addr1,
     customer.cust_addr2, customer.city_name AS Expr1,
     customer.state_id AS Expr2, customer.zip_code AS Expr3,
     customer.phone1 AS Expr4
FROM dbo.prospect INNER JOIN
     dbo.prospect_archive ON
     dbo.prospect.prospect_no = dbo.prospect_archive.prospect_no LEFT
      OUTER JOIN
     dbo.customer ON
     dbo.prospect.referring_cust_no = dbo.customer.cust_no
WHERE (dbo.prospect.promo_id = @Promo_ID)

note, if you're trying to prompt for this value you'll need to have your 
gui acctually ask from an input box or if in Access create a form that 
would ask for this information.

to execute the command would be

exec sproc_MyPromoId 1234

if 1234 was the promoid.

Nicholson, Karen wrote:
> Need hints, please.  I need to ask the user for a value for filtering
> records in an sql statement.  Just a simple, what promo_id that I have
> underlined in the select statement below.  Looking everywhere, must not be
> asking the correct question.  I assume a variable is declared and then the
> variable pops in the place of the underline?  Thanks in advance.  Happy
> Monday.
> 
> SELECT prospect.prospect_no, prospect.prospect_name, 
>     prospect.prospect_addr1, prospect.prospect_addr2, 
>     prospect.city_name, prospect.state_id, prospect.zip_code, 
>     prospect.branch_no, prospect.mktsrc_id, prospect.phone1, 
>     prospect.prospstat_id, prospect.sales_emp_no, prospect.udf9, 
>     prospect.udf10, prospect.promo_id, 
>     prospect.referring_cust_no, prospect.udf7, 
>     prospect_archive.prospstat_id AS prospstat_id_archive, 
>     customer.cust_name, customer.cust_addr1, 
>     customer.cust_addr2, customer.city_name AS Expr1, 
>     customer.state_id AS Expr2, customer.zip_code AS Expr3, 
>     customer.phone1 AS Expr4
> FROM dbo.prospect INNER JOIN
>     dbo.prospect_archive ON 
>     dbo.prospect.prospect_no = dbo.prospect_archive.prospect_no LEFT
>      OUTER JOIN
>     dbo.customer ON 
>     dbo.prospect.referring_cust_no = dbo.customer.cust_no
> WHERE (dbo.prospect.promo_id = ____________________ )
> _______________________________________________



-- 
-Francisco