How to declair a variable to add a parameter to a query?

How can I declair a variable so I can collect a variable in the where (having) clause of a query?  I would like the user to populate MyTable.employer.  I have tried @EmployerCode, and receive.  I do not see how to add this in the design tool.

Msg 137, Level 15, State 2, Line 37
Must declare the scalar variable "@EmployerCode".
kgittingerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
You can declare and use as follows:

DECLARE @EmployerCode INT

SELECT sum(amount) , empCode FROM  MyTable.employer Group by empCode  having empCode  >  @EmployerCode

SELECT * FROM Table.employer WHERE empCode  =  @EmployerCode
Rajkumar GsSoftware EngineerCommented:
I am not sure, what you mean ?

To select employee details based on a empid parameter

DECLARE @EmpID INT
SET @EmpID = 1
SELECT * FROM tblEmployee WHERE  EmployeeID = @EmpID


Your error message says that you have not declared the parameter "@EmployerCode

Check it
Raj
kgittingerAuthor Commented:
I would like to prompt for the code.  Do I use this decalre statement before I start my select statement?  This is my first time attempting the prompt.

Thanks!
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Om PrakashCommented:
You need to use the declare statement before select statement (First declare and then use)
DECLARE @EmployerCode INT
SELECT * FROM Table.employer WHERE empCode  =  @EmployerCode
kgittingerAuthor Commented:
Do I understan you correctly?  I have entered the following in the first line of my SQL

DECLARE @EmployerCode INT
SELECT * FROM TABLE_claims_Ver2_View_Employer.employer_code WHERE employer_code  =  @EmployerCode

Thansk so much
Om PrakashCommented:
Yes

DECLARE @EmployerCode INT
SELECT * FROM TABLE_claims_Ver2_View_Employer WHERE employer_code  =  @EmployerCode
halfbloodprinceCommented:
Declare @EmployerCode Varchar(50)
Set @EmployerCode = 'TEST00050'
Select * From YourTableName
Where EmployerCode = @EmployerCode
kgittingerAuthor Commented:
Here is my query.  I seem to not understand the instructions.  I appreciate your patience...  The end result I am after is a prompt to enter my 3 digit code "FCH" in this example my last line below
SELECT Table_claims_Ver2_View_Participant.participant_last_name, 
Table_claims_Ver2_View_Participant.participant_first_name, 
Table_claims_Ver2_View_Participant.participant_last_name +', '+ Table_claims_Ver2_View_Participant.participant_first_name as Participant_Name,
Table_Claims_Ver2_N.planyear_start_dt, 
Table_Claims_Ver2_N.planyear_end_dt, 
Table_Claims_Ver2_N.claim_claim_amt, 
Table_Claims_Ver2_N.claimaccount_denied_amt, 
Table_Claims_Ver2_N.claim_create_dt, 
Table_Claims_Ver2_N.claim_claim_submission_dt, 
Table_Claims_Ver2_N.claim_service_start_dt, 
Table_Claims_Ver2_N.claim_service_end_dt, 
Table_Claims_Ver2_N.claimaccount_submitted_amt, 
Table_Claims_Ver2_N.claim_service_desc, 
Table_Claims_Ver2_N.plan_name, 
Table_Claims_Ver2_N.plan_display_name, 
TABLE_claims_Ver2_View_Employer.employer_code
FROM (Table_Claims_Ver2_N INNER JOIN TABLE_claims_Ver2_View_Employer 
ON Table_Claims_Ver2_N.table_claims_id2 = TABLE_claims_Ver2_View_Employer.table_claims_id2_employer_instance) 
INNER JOIN Table_claims_Ver2_View_Participant 
ON Table_Claims_Ver2_N.table_claims_id2 = Table_claims_Ver2_View_Participant.table_claims_id2_Participant_instance
GROUP BY Table_claims_Ver2_View_Participant.participant_last_name, 
Table_claims_Ver2_View_Participant.participant_first_name,
Table_Claims_Ver2_N.planyear_start_dt, Table_Claims_Ver2_N.planyear_end_dt, 
Table_Claims_Ver2_N.claim_claim_amt, 
Table_Claims_Ver2_N.claimaccount_denied_amt, 
Table_Claims_Ver2_N.claim_create_dt, 
Table_Claims_Ver2_N.claim_claim_submission_dt, 
Table_Claims_Ver2_N.claim_service_start_dt, 
Table_Claims_Ver2_N.claim_service_end_dt, 
Table_Claims_Ver2_N.claimaccount_submitted_amt, 
Table_Claims_Ver2_N.claim_service_desc, 
Table_Claims_Ver2_N.plan_name, 
Table_Claims_Ver2_N.plan_display_name, 
TABLE_claims_Ver2_View_Employer.employer_code
Having ((datepart(yy,Table_Claims_Ver2_N.planyear_end_dt)=2008) 
AND ((Table_Claims_Ver2_N.claim_service_desc) like '%Debit%')
AND ((TABLE_claims_Ver2_View_Employer.employer_code)='FCH'));

Open in new window

mdagisCommented:
"The end result I am after is a prompt to enter my 3 digit code"

Which program do you want to produce the prompt?

Is it a report?

A custom software?

A shell script?

kgittingerAuthor Commented:
I am creating the query in SQL Studio Express.  I will then paste into Excel.  and run the query in excel.  I cannot create a prompt....
mdagisCommented:
Here you
Declare @MyCode varchar(10)

Set @MyCode = 'FCH'


SELECT Table_claims_Ver2_View_Participant.participant_last_name, 
Table_claims_Ver2_View_Participant.participant_first_name, 
Table_claims_Ver2_View_Participant.participant_last_name +', '+ Table_claims_Ver2_View_Participant.participant_first_name as Participant_Name,
Table_Claims_Ver2_N.planyear_start_dt, 
Table_Claims_Ver2_N.planyear_end_dt, 
Table_Claims_Ver2_N.claim_claim_amt, 
Table_Claims_Ver2_N.claimaccount_denied_amt, 
Table_Claims_Ver2_N.claim_create_dt, 
Table_Claims_Ver2_N.claim_claim_submission_dt, 
Table_Claims_Ver2_N.claim_service_start_dt, 
Table_Claims_Ver2_N.claim_service_end_dt, 
Table_Claims_Ver2_N.claimaccount_submitted_amt, 
Table_Claims_Ver2_N.claim_service_desc, 
Table_Claims_Ver2_N.plan_name, 
Table_Claims_Ver2_N.plan_display_name, 
TABLE_claims_Ver2_View_Employer.employer_code
FROM (Table_Claims_Ver2_N INNER JOIN TABLE_claims_Ver2_View_Employer 
ON Table_Claims_Ver2_N.table_claims_id2 = TABLE_claims_Ver2_View_Employer.table_claims_id2_employer_instance) 
INNER JOIN Table_claims_Ver2_View_Participant 
ON Table_Claims_Ver2_N.table_claims_id2 = Table_claims_Ver2_View_Participant.table_claims_id2_Participant_instance
GROUP BY Table_claims_Ver2_View_Participant.participant_last_name, 
Table_claims_Ver2_View_Participant.participant_first_name,
Table_Claims_Ver2_N.planyear_start_dt, Table_Claims_Ver2_N.planyear_end_dt, 
Table_Claims_Ver2_N.claim_claim_amt, 
Table_Claims_Ver2_N.claimaccount_denied_amt, 
Table_Claims_Ver2_N.claim_create_dt, 
Table_Claims_Ver2_N.claim_claim_submission_dt, 
Table_Claims_Ver2_N.claim_service_start_dt, 
Table_Claims_Ver2_N.claim_service_end_dt, 
Table_Claims_Ver2_N.claimaccount_submitted_amt, 
Table_Claims_Ver2_N.claim_service_desc, 
Table_Claims_Ver2_N.plan_name, 
Table_Claims_Ver2_N.plan_display_name, 
TABLE_claims_Ver2_View_Employer.employer_code
Having ((datepart(yy,Table_Claims_Ver2_N.planyear_end_dt)=2008) 
AND ((Table_Claims_Ver2_N.claim_service_desc) like '%Debit%')
AND ((TABLE_claims_Ver2_View_Employer.employer_code)=@MyCode));

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgittingerAuthor Commented:
Thanks for laying this out for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.