auto format of stored procedures?

hi,
i have many stored procedures, and i'm currently working on one with many
levels of nested IF's. it came from someone else, and i'm modifying it.
So...i'd love a utility, that i've seen in most other dev environments, that
will 'pretty print', or automatically format all the levels of a stored
procedure.
any one know of such a thing? i don't find much when googling!
thanks,
fred
You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
what is called 'Smart formatting' -though many may quibble about how 'smart'
it may be...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> hi,
> i have many stored procedures, and i'm currently working on one with many
> levels of nested IF's. it came from someone else, and i'm modifying it.
> So...i'd love a utility, that i've seen in most other dev environments,
> that will 'pretty print', or automatically format all the levels of a
> stored procedure.
> any one know of such a thing? i don't find much when googling!
> thanks,
> fred
>
|||thanks arnie, i'll take a look!
fred
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQDB%23CP4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> You could look at SQLEdit from www.ApexSQL.com, and SSMS for SQL 2005 has
> what is called 'Smart formatting' -though many may quibble about how
> 'smart' it may be...
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Freddy" <frosenbergNOSpam@.globaltouchtelecom.com> wrote in message
> news:%23eICHNO4GHA.1848@.TK2MSFTNGP06.phx.gbl...
>
sql

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrés
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrés
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>>Hi,
>>I'm new to MSSQL world and I have a little question:
>>I have just added a new field to a table containing some 3000 rows...
>>The new field has the same value for all the existent rows...
>>How can I automatically update all rows ?
>>I would like to put "EN" string in all rows... What tool do I need to
>>use ? (SQL query analyzer ?)
>>Any site, if this is to complex to explain here ?
>>TIA
>
>

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
Something like (from Query Analyzer):
UPDATE tblname
SET colname = 'EN'
I suggest you purchase some entry level books about the SQL language, knowing basics of SQL is a
definite advantage whatever type you do with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Osawa" <posawa@.sun.es> wrote in message news:eKdOTXzjEHA.2908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIA
You could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>
|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
sql

Auto filling

Hi,
I'm new to MSSQL world and I have a little question:
I have just added a new field to a table containing some 3000 rows...
The new field has the same value for all the existent rows...
How can I automatically update all rows ?
I would like to put "EN" string in all rows... What tool do I need to
use ? (SQL query analyzer ?)
Any site, if this is to complex to explain here ?
TIAYou could write a simple UPDATE command in Query Analyzer, something like
this:
UPDATE myTable
SET newColumn = 'EN'
If you exclude the WHERE clause, all rows will be affected.
HTH,
Andrs
"Peter Osawa" <posawa@.sun.es> wrote in message
news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm new to MSSQL world and I have a little question:
> I have just added a new field to a table containing some 3000 rows...
> The new field has the same value for all the existent rows...
> How can I automatically update all rows ?
> I would like to put "EN" string in all rows... What tool do I need to
> use ? (SQL query analyzer ?)
> Any site, if this is to complex to explain here ?
> TIA
>|||Thanks
King regards
Andres Taylor wrote:
> You could write a simple UPDATE command in Query Analyzer, something like
> this:
> UPDATE myTable
> SET newColumn = 'EN'
> If you exclude the WHERE clause, all rows will be affected.
> HTH,
> Andrs
> "Peter Osawa" <posawa@.sun.es> wrote in message
> news:ulIkXhzjEHA.3056@.TK2MSFTNGP10.phx.gbl...
>
>
>

Auto fill in colums trough foreign key relationship

Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers WimYou want help creating a denormalized database?

[sigh...]

If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.

Why are you duplicating data like this?|||You want help creating a denormalized database?

[sigh...]

If you absolutely need to do this, you can accomplish it through an INSERT trigger on your KRS table.

Why are you duplicating data like this?I keep trying to shoot myself it the foot, but the rifle wobbles when I pull the trigger. Can one of you guys hold it for me?

I actually thought about trying to formulate a reply to this question, but nothing I wrote seemed fit to post. I'm thinking that a VIEW would make life a lot easier in the long run, what do you think?

-PatP

Auto fill collumn

I have an existing collumn in a table that contains a 5 digit number used as
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:

>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)