combining a multiple queries into a single query (in SQL Server, with variables?)

Question

I'd like to apply the following query to two other circ_slip values ('payment' and 'refund') for a large number of locations.

delete from circ_slip_field 
where location = 'NEW LOCATION' and circ_slip = 'waive'
go
declare @copy_loc varchar(7), @new_loc varchar(7) 
select @copy_loc = 'OLD LOCATION' 
select @new_loc = 'NEW LOCATION'
insert circ_slip_field
       (circ_slip,location,section,ord,circ_slip_field_type,label,field_column,
        append_to_prev,justify,max_field_len,min_field_len,data_mask)
select  circ_slip,@new_loc,section,ord,circ_slip_field_type,label,field_column,
        append_to_prev,justify,max_field_len,min_field_len,data_mask
from circ_slip_field 
where circ_slip = 'waive' and location = @copy_loc

So I added the following line to the beginning and then copied the original query three times, replacing the two instances of 'waive' with 'payment' and 'refund' respectively.

 declare @copy_loc varchar(7), @new_loc varchar(7) 
 select @copy_loc = 'COPY LOCATION' 
 select @new_loc = 'NEW LOCATION'

I also removed the two select variable statements (@copy_loc and @new_loc) since they have already been declared.

Any thoughts on why this did not work for me? I would be greatly appreciative. Needless to say I'm quite new to SQL in general.

** EDIT: by "did not work" I meant the query ran without errors but did not make any changes to the NEW LOCATION.

* 2nd EDIT: I think the issue may be with the "go" command. In order to have the variables work throughout I need to remove the 'go' from my three replicated queries.

"Any thoughts on why this did not work for me?" And how do you define "did not work"? — mustaccio
– mustaccio, Commented Jun 21, 2013 at 20:22
As we have no idea what you're talking about when you say "did not work for me", it's very hard to speculate on what the reasons might be. We can't read your mind, and we don't have your data or the rest of your code in front of us. "Did not work" is meaningless unless you also explain how it did not work. Did you get an error message? If so, what did it say? Did it do something unexpected? Or did it not do something you think it should? Please edit your question to be specific about what the problem is you're asking us to help you solve. Thanks. — Ken White
– Ken White, Commented Jun 21, 2013 at 22:44
What results do you get if you run the select part of the query alone? — user359040
– user359040, Commented Jun 25, 2013 at 16:42
If I run the 3 beginning Declare and Select lines the command completes successfully — Marc
– Marc, Commented Jun 27, 2013 at 18:11
So This may be the issue, you are deleting records with a WHERE location = @new_loc and circ_slip = 'waive'...Then you try to re-enter records where WHERE circ_slip = 'waive' and location = @copy_loc...This appears to be only selecting records to enter where location is equal to the old location. Try switching it to location = @new_loc so it will enter the new records. — Nick A
– Nick A, Commented Jan 17, 2019 at 22:53

criticalfix · Accepted Answer · 2013-06-25 17:28:34Z

1

Notice @copy_loc and @new_loc are defined as varchar(7), but you're putting 12 characters into them. They're truncated and won't match the fields in the table. Make the variables varchar(50). Don't worry, it won't use more space than it needs.

answered Jun 25, 2013 at 17:28

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

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

1 Comment

Marc Over a year ago

Good point, and that would be a problem. But the actual data I would be using consists of three-letter location codes, so I think varchar(7) is sufficient

Collectives™ on Stack Overflow

combining a multiple queries into a single query (in SQL Server, with variables?)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related