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?
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
0
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
0
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!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
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
0
Om PrakashCommented:
Yes

DECLARE @EmployerCode INT
SELECT * FROM TABLE_claims_Ver2_View_Employer WHERE employer_code  =  @EmployerCode
0
halfbloodprinceCommented:
Declare @EmployerCode Varchar(50)
Set @EmployerCode = 'TEST00050'
Select * From YourTableName
Where EmployerCode = @EmployerCode
0
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

0
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?

0
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....
0
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

0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.