Run two SQL queries with one variable

Question

My SQL knowledge is pretty limited and this seems like something that should be easy, but I can't figure it out.

I have two queries that I run with the same where clause. Previously, I've pasted the number in the where clause twice. I thought there had to be an easier way so I found that you can do variables.

My problem is when declaring the variable in the first query, it forgets it and doesn't run it for the second query. The terminology may be off on a lot of what I've explain, but the simple query below should explain it better. The second query fails and says

must declare the scalar variable @p_repid

which I have declared in the first query.

DECLARE @p_repid int=3427115
select fldRBuddyId, count(fldRBuddyId) "Repeats"
from tblMsgsOnAir_Type8 typ8
where fldCBuddyId = @p_repid
group by fldRBuddyID
having count(fldRBuddyId) > 1
order by "Repeats" desc
go

select FLDREPID,moa.FLDTGBID,tt.FLDNAME, count(*) "Count"
from TBLMSGSONAIR_1 moa
join TBLTOWERS_1 tt on moa.FLDTGBID=tt.FLDTGBID
where  fldrepid = @p_repid
group by  FLDREPID,moa.FLDTGBID,tt.FLDNAME
order by "Count" desc
go

Thanks in advance for any help.

Re-tagged with SQL Server and T-SQL based on the code given and the error message — user330315
– user330315, Commented Feb 4, 2016 at 13:47

Tom H · Accepted Answer · 2016-02-04 13:40:24Z

3

Remove the GO between the two statements. That's MS SQL Server's batch separator.

answered Feb 4, 2016 at 13:40

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alroc Over a year ago

Additional info to clarify: Variables leave scope when the batch terminates. So when you hit "go", any variables previously declared cease to exist.

AdamH Over a year ago

Thanks. That seemed simple enough. I was given the query by someone else and wasn't sure about the "Go" between the two statements.

Cem Şengezer · Accepted Answer · 2016-02-04 13:41:14Z

0

If you use "GO" command all declared variables are gone. Try removing the "GO" which is in the middle.

answered Feb 4, 2016 at 13:41

Cem Şengezer

2131 silver badge12 bronze badges

Collectives™ on Stack Overflow

Run two SQL queries with one variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related