|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help With Stored Procedure!This stored procedure isn't working and I'm not sure why. ANy suggestions would be greatly appreciated. CREATE PROCEDURE dbo.[proc_update_urn] @case_id as int, @service_area varchar(3) AS begin declare @max_id int set @max_id = (select max(sd_s_id) from tbl_surveillance_dates where sd_s_id = @case_id) update tbl_surveillance set s_urn = (@service_area + @case_id + '/' + @max_id) where s_id = @case_id end GO Thanks Damon Nomad wrote:
> Hi, It would have been helpful if you told us what "isn't working" means. Error> > This stored procedure isn't working and I'm not sure why. messages? Incorrect results? Does nothing? Show quote > ANy Huh? What is this supposed to do? @max_id is always going to be the same as> suggestions would be greatly appreciated. > > CREATE PROCEDURE dbo.[proc_update_urn] > > > @case_id as int, > @service_area varchar(3) > > AS > > begin > declare @max_id int > set @max_id = (select max(sd_s_id) > from tbl_surveillance_dates > where sd_s_id = @case_id) @case_id, unless no row exists in tbl_surveillance_dates where sd_s_id = @case_id > You cannot concatenate ints with strings. You must explicitly cast the ints> update tbl_surveillance > set s_urn = (@service_area + @case_id + '/' + @max_id) as varchars: set s_urn = @service_area + CAST(@case_id AS varchar(10)) + '/' + CAST(@max_id AS varchar(10)) If these suggestions don't fix your problem, give us a little more to go on .... Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Hi,
No error messages it's just not working. Any ideas? Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:eqhOEzq9FHA.4076@tk2msftngp13.phx.gbl... > Nomad wrote: >> Hi, >> >> This stored procedure isn't working and I'm not sure why. > > It would have been helpful if you told us what "isn't working" means. > Error > messages? Incorrect results? Does nothing? > >> ANy >> suggestions would be greatly appreciated. >> >> CREATE PROCEDURE dbo.[proc_update_urn] >> >> >> @case_id as int, >> @service_area varchar(3) >> >> AS >> >> begin >> declare @max_id int >> set @max_id = (select max(sd_s_id) >> from tbl_surveillance_dates >> where sd_s_id = @case_id) > > Huh? What is this supposed to do? @max_id is always going to be the same > as > @case_id, unless no row exists in tbl_surveillance_dates where sd_s_id = > @case_id > >> >> update tbl_surveillance >> set s_urn = (@service_area + @case_id + '/' + @max_id) > > You cannot concatenate ints with strings. You must explicitly cast the > ints > as varchars: > set s_urn = @service_area + CAST(@case_id AS varchar(10)) + '/' + > CAST(@max_id AS varchar(10)) > > > If these suggestions don't fix your problem, give us a little more to go > on > ... > > Bob Barrows > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > Nomad wrote:
> Hi, You still haven't given us anything to go on (did you read my entire> > No error messages it's just not working. Any ideas? response?) "not working" does not help. What are the symptoms? How can you tell that it's "not working". -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. I'm basically trying to update a field with a variable which consists of the
first three letters of a service area, the id of the record it's associated to and the number of records associated to it. When I save the record which then runs that stored procedure and updates the record the field I want updating is blank, in other words it has done nothing. Does that explain it a bit better? Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:uDPqaNr9FHA.1032@TK2MSFTNGP11.phx.gbl... > Nomad wrote: >> Hi, >> >> No error messages it's just not working. Any ideas? > You still haven't given us anything to go on (did you read my entire > response?) > > "not working" does not help. What are the symptoms? How can you tell that > it's "not working". > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > Nomad wrote:
> I'm basically trying to update a field with a variable which consists Number of records? Why are you using "select max(sd_s_id)" instead of> of the first three letters of a service area, the id of the record > it's associated to and the number of records associated to it. "select count(sd_s_id)" > When Now you're going to have to help us reproduce the problem by providing DDL> I save the record which then runs that stored procedure and updates > the record the field I want updating is blank, in other words it has > done nothing. Does that explain it a bit better? > Yes. Much. and sample data. Show quote > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:uDPqaNr9FHA.1032@TK2MSFTNGP11.phx.gbl... >> Nomad wrote: >>> Hi, >>> >>> No error messages it's just not working. Any ideas? >> You still haven't given us anything to go on (did you read my entire >> response?) >> >> "not working" does not help. What are the symptoms? How can you tell >> that it's "not working". >> -- >> Microsoft MVP -- ASP/ASP.NET >> Please reply to the newsgroup. The email account listed in my From >> header is my spam trap, so I don't check it very often. You will get >> a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. you need to convert the @caseid and @max_id to varchar first
e.g., update tbl_surveillance set s_urn = (@service_area + convert(varchar,@case_id) + '/' + convert(varchar,@max_id)) where s_id = @case_id Nomad wrote: Show quote > Hi, > > This stored procedure isn't working and I'm not sure why. ANy suggestions > would be greatly appreciated. > > CREATE PROCEDURE dbo.[proc_update_urn] > > > @case_id as int, > @service_area varchar(3) > > AS > > begin > declare @max_id int > set @max_id = (select max(sd_s_id) > from tbl_surveillance_dates > where sd_s_id = @case_id) > > update tbl_surveillance > set s_urn = (@service_area + @case_id + '/' + @max_id) > where s_id = @case_id > end > GO > > > Thanks > > Damon > > Thank you very much for the reply. I will give that a go.
Show quote "Trey Walpole" <treypole@newsgroups.nospam> wrote in message news:%238lp0yq9FHA.1224@TK2MSFTNGP12.phx.gbl... > you need to convert the @caseid and @max_id to varchar first > > e.g., > update tbl_surveillance > set s_urn = (@service_area + convert(varchar,@case_id) + '/' + > convert(varchar,@max_id)) > where s_id = @case_id > > Nomad wrote: >> Hi, >> >> This stored procedure isn't working and I'm not sure why. ANy >> suggestions would be greatly appreciated. >> >> CREATE PROCEDURE dbo.[proc_update_urn] >> >> >> @case_id as int, >> @service_area varchar(3) >> >> AS >> >> begin >> declare @max_id int >> set @max_id = (select max(sd_s_id) >> from tbl_surveillance_dates >> where sd_s_id = @case_id) >> >> update tbl_surveillance >> set s_urn = (@service_area + @case_id + '/' + @max_id) >> where s_id = @case_id >> end >> GO >> >> >> Thanks >> >> Damon oh - and i was going to ask the same thing Bob asked - why the max()
query, since you'll just be getting the same id as the given @case_id? from what was posted, it looks like all you need is the update (substituting @case_id for @max_id, of course). Nomad wrote: Show quote > Thank you very much for the reply. I will give that a go. > "Trey Walpole" <treypole@newsgroups.nospam> wrote in message > news:%238lp0yq9FHA.1224@TK2MSFTNGP12.phx.gbl... > >>you need to convert the @caseid and @max_id to varchar first >> >>e.g., >>update tbl_surveillance >>set s_urn = (@service_area + convert(varchar,@case_id) + '/' + >>convert(varchar,@max_id)) >>where s_id = @case_id >> >>Nomad wrote: >> >>>Hi, >>> >>>This stored procedure isn't working and I'm not sure why. ANy >>>suggestions would be greatly appreciated. >>> >>>CREATE PROCEDURE dbo.[proc_update_urn] >>> >>> >>>@case_id as int, >>>@service_area varchar(3) >>> >>>AS >>> >>>begin >>> declare @max_id int >>> set @max_id = (select max(sd_s_id) >>> from tbl_surveillance_dates >>> where sd_s_id = @case_id) >>> >>> update tbl_surveillance >>> set s_urn = (@service_area + @case_id + '/' + @max_id) >>> where s_id = @case_id >>>end >>>GO >>> >>> >>>Thanks >>> >>>Damon > > >  |
|||||||||||||||||||||||
Other interesting topics