Code review tool for Stored procedures

Hi,
Is there any tool (freeware or licensed) which can automate code reviews for
stored procedures based on various parameters. I am looking out for somethin
g
similar to FXCop for stored procedures on SQL Server 2000.
Afaq ChoonawalaYou can start with SQL BPA
http://www.microsoft.com/downloads/...&displaylang=en
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Afaq" <Afaq@.discussions.microsoft.com> wrote in message
news:36129FBF-13FB-4492-B7F3-C21C75BD2B7A@.microsoft.com...
> Hi,
> Is there any tool (freeware or licensed) which can automate code reviews
> for
> stored procedures based on various parameters. I am looking out for
> something
> similar to FXCop for stored procedures on SQL Server 2000.
> --
> Afaq Choonawala

Code inside! --> How to return the @@identity parameter without using stored procedures

Hi.
here is my code with my problem described in the syntax.
I am using asp.net 1.1 and VB.NET
Thanks in advance for your help.
I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.

PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName); SELECT @.NewID = @.@.identity"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

'Here is my problem.
'What do I have to do in order to add the parameter @.NewID and
'how do I read and return the value of @.NewID within that function howToReturnID
'any help is greatly appreciated!
'I cannot use SPs in this application - have to do it this way! :-(

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()

'here i want to return the new ID!
Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

Why don't you put your SQL statement something like this;
Insert Into table (col1, col2) Values (1, 2); Select @.@.IDENTITY
And from there, to retrieve the @.@.IDENTITY, you will execute a scalar return. For example:
Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
|||

Hi,

thank you very much for your help.
I tried your suggestion but got always two rows inserted.

Obviously the command object executed the insert statement two times?

first here: db.command.ExecuteNonQuery()
and here:Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
this is what I did:
Is this correct ? -at least it works ;-) - but is it the "right way" to do it?
PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName);"

'I separated the SQL query string
Dim strSQL2AsString ="Select @.@.IDENTITY;"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
'execute first query
dbCommand.ExecuteNonQuery()

'execute second query - this actually returns the id of the currently inserted row.
'But is this the CORRECT WAY to do it?? Any objections?
'this solution only inserts one row not two - as it did when the SQL-query was in one string
dbCommand.CommandText = strSQL2
newID = Convert.ToInt32(dbCommand.ExecuteScalar)

Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

|||Don't call the ExecuteNonQuery() method. Every Execute*() method that you call runs it as a completely new query, if you know what I mean.
Regards,
Justin|||Well, not really
You mean the .ExecuteScalar method of the command object does also run the insert query?
If so why is there a ExecuteNonQuery method at all?
Is the way I did it in my second source code example not recommendable?
But it works for me - or is there a good reason not to do it that way (performance issues, etc.) ?|||Well, with your second code example, you have described what is wrong with it - it inserts the same data twice into the table. So this breaks logics reason. Besides calling the Execute*() twice, I don't see anything else wrong with the second code posting.
The reason why we have the three Execute() methods (ExecuteNonQuery, ExecuteReader, and ExecuteScalar) on the commands are that each one tells the 'executer' (loosely saying it here) on what type of result to expect. With ExecuteNonQuery(), it returns how many rows were affected. With ExecuteScalar() method, you are expect a simple value type to be returned. And with the ExecuteReader(), it returns a data reader...
They all 'execute' but you have to decide on what information you need to retrieve from execution of that command. Am I making sense now?|||Hi,
yes, everything works now the way it is supposed to :)
Thank you very much for your help!
But in my second code example that i have posted it does not insert the row twice ;-)
Just take a closer look at it - I have provided the command object with a second query that only selects @.@.identity .
But I did that "workaround" because I didn't know that executeScalar also "executes" insert queries ...!
My problem is solved now!|||You are right. I did overlook that. Silly me. Anyway, it was a pleasure.

Code Coverage Tool

I want to analyze what statemetns were executed in stored procedures,
for example.
I'm trying hard to find tools for sql code coverage while there are
tons of such tools for Java or C#.
I was able to find only : http://www.sqlpower.com/dsa.html but I want
to compare it with other solutions.Andrei
Have you tried using SQL Server Profiler?
<andrei_tapt@.mail.ru> wrote in message
news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
>I want to analyze what statemetns were executed in stored procedures,
> for example.
> I'm trying hard to find tools for sql code coverage while there are
> tons of such tools for Java or C#.
> I was able to find only : http://www.sqlpower.com/dsa.html but I want
> to compare it with other solutions.
>|||Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> >I want to analyze what statemetns were executed in stored procedures,
> > for example.
> > I'm trying hard to find tools for sql code coverage while there are
> > tons of such tools for Java or C#.
> > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > to compare it with other solutions.
> >|||<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
?me any info about what sql statements
>were executed ... or I don't know how to get it.
Why> There are some events to provide it. Under TSQL events select
SQL:StmtCompleted
And see in the TEXT column the resuts
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant '?(?):
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> >I want to analyze what statemetns were executed in stored procedures,
> > for example.
> > I'm trying hard to find tools for sql code coverage while there are
> > tons of such tools for Java or C#.
> > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > to compare it with other solutions.
> >|||AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> >were executed ... or I don't know how to get it.
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
> > Andrei
> > Have you tried using SQL Server Profiler?
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > >I want to analyze what statemetns were executed in stored procedures,
> > > for example.
> > > I'm trying hard to find tools for sql code coverage while there are
> > > tons of such tools for Java or C#.
> > > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > > to compare it with other solutions.
> > >|||Andrei, ty chital chto ya tebe napisal? Link etot chital?
<andrei_tapt@.mail.ru> wrote in message
news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant '?(?):
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> >were executed ... or I don't know how to get it.
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
> > Andrei
> > Have you tried using SQL Server Profiler?
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > >I want to analyze what statemetns were executed in stored procedures,
> > > for example.
> > > I'm trying hard to find tools for sql code coverage while there are
> > > tons of such tools for Java or C#.
> > > I was able to find only : http://www.sqlpower.com/dsa.html but I want
> > > to compare it with other solutions.
> > >|||Spasibo, kak raz to chto nado:) Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > ?me any info about what sql statements
> > >were executed ... or I don't know how to get it.
> >
> > Why> There are some events to provide it. Under TSQL events select
> > SQL:StmtCompleted
> > And see in the TEXT column the resuts
> >
> > http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> >
> >
> >
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > Yes, I use it as performance analyzer. I know that I can save trace in
> > database but it doesn't provide me any info about what sql statements
> > were executed ... or I don't know how to get it.
> >
> > Uri Dimant '?(?):
> >
> > > Andrei
> > > Have you tried using SQL Server Profiler?
> > >
> > >
> > > <andrei_tapt@.mail.ru> wrote in message
> > > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > > >I want to analyze what statemetns were executed in stored procedures,
> > > > for example.
> > > > I'm trying hard to find tools for sql code coverage while there are
> > > > tons of such tools for Java or C#.
> > > > I was able to find only : http://www.sqlpower.com/dsa.html but I wa=nt
> > > > to compare it with other solutions.
> > > >|||Ok, ydachi, obrashaysya esli est' problemy
<andrei_tapt@.mail.ru> wrote in message
news:1155122187.034692.167400@.n13g2000cwa.googlegroups.com...
Spasibo, kak raz to chto nado:) Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant '?(?):
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > ?me any info about what sql statements
> > >were executed ... or I don't know how to get it.
> >
> > Why> There are some events to provide it. Under TSQL events select
> > SQL:StmtCompleted
> > And see in the TEXT column the resuts
> >
> > http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> >
> >
> >
> >
> >
> > <andrei_tapt@.mail.ru> wrote in message
> > news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> > Yes, I use it as performance analyzer. I know that I can save trace in
> > database but it doesn't provide me any info about what sql statements
> > were executed ... or I don't know how to get it.
> >
> > Uri Dimant '?(?):
> >
> > > Andrei
> > > Have you tried using SQL Server Profiler?
> > >
> > >
> > > <andrei_tapt@.mail.ru> wrote in message
> > > news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
> > > >I want to analyze what statemetns were executed in stored procedures,
> > > > for example.
> > > > I'm trying hard to find tools for sql code coverage while there are
> > > > tons of such tools for Java or C#.
> > > > I was able to find only : http://www.sqlpower.com/dsa.html but I
> > > > want
> > > > to compare it with other solutions.
> > > >|||I did not know there were ANY code coverage tools for SQL Server. Now
I know there is at least one - thanks!
Roy
On 9 Aug 2006 00:43:24 -0700, andrei_tapt@.mail.ru wrote:
>I want to analyze what statemetns were executed in stored procedures,
>for example.
>I'm trying hard to find tools for sql code coverage while there are
>tons of such tools for Java or C#.
>I was able to find only : http://www.sqlpower.com/dsa.html but I want
>to compare it with other solutions.

Code Coverage Tool

I want to analyze what statemetns were executed in stored procedures,
for example.
I'm trying hard to find tools for sql code coverage while there are
tons of such tools for Java or C#.
I was able to find only : http://www.sqlpower.com/dsa.html but I want
to compare it with other solutions.Andrei
Have you tried using SQL Server Profiler?
<andrei_tapt@.mail.ru> wrote in message
news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...
>I want to analyze what statemetns were executed in stored procedures,
> for example.
> I'm trying hard to find tools for sql code coverage while there are
> tons of such tools for Java or C#.
> I was able to find only : http://www.sqlpower.com/dsa.html but I want
> to compare it with other solutions.
>|||Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...|||<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
?me any info about what sql statements
>were executed ... or I don't know how to get it.
Why> There are some events to provide it. Under TSQL events select
SQL:StmtCompleted
And see in the TEXT column the resuts
http://www.sql-server-performance.c...ofiler_tips.asp
<andrei_tapt@.mail.ru> wrote in message
news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
Yes, I use it as performance analyzer. I know that I can save trace in
database but it doesn't provide me any info about what sql statements
were executed ... or I don't know how to get it.
Uri Dimant '?(?):
[vbcol=seagreen]
> Andrei
> Have you tried using SQL Server Profiler?
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155109404.400115.317600@.h48g2000cwc.googlegroups.com...|||AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
>|||Andrei, ty chital chto ya tebe napisal? Link etot chital?
<andrei_tapt@.mail.ru> wrote in message
news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
AFAIK, I will see full text of stored procedure in TEXT column, like
using sp_helptext with stored procedure name, but I want to know which
statements were executed in this stored procedure.
Uri Dimant '?(?):
[vbcol=seagreen]
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> ?me any info about what sql statements
> Why> There are some events to provide it. Under TSQL events select
> SQL:StmtCompleted
> And see in the TEXT column the resuts
> http://www.sql-server-performance.c...ofiler_tips.asp
>
>
> <andrei_tapt@.mail.ru> wrote in message
> news:1155116699.048307.322460@.i3g2000cwc.googlegroups.com...
> Yes, I use it as performance analyzer. I know that I can save trace in
> database but it doesn't provide me any info about what sql statements
> were executed ... or I don't know how to get it.
> Uri Dimant '?(?):
>|||Spasibo, kak raz to chto nado Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant =D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
[vbcol=seagreen]
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
>
nt[vbcol=seagreen]|||Ok, ydachi, obrashaysya esli est' problemy
<andrei_tapt@.mail.ru> wrote in message
news:1155122187.034692.167400@.n13g2000cwa.googlegroups.com...
Spasibo, kak raz to chto nado Povozitsya pridetsya, no lucshe chem
nichego. Ran'she etu stat'u videl, no bystro probezhalsya b vnimania ne
obratil. Spasibo
Uri Dimant '?(?):
[vbcol=seagreen]
> Andrei, ty chital chto ya tebe napisal? Link etot chital?
> <andrei_tapt@.mail.ru> wrote in message
> news:1155119817.600289.225800@.i42g2000cwa.googlegroups.com...
> AFAIK, I will see full text of stored procedure in TEXT column, like
> using sp_helptext with stored procedure name, but I want to know which
> statements were executed in this stored procedure.
> Uri Dimant '?(?):
>|||I did not know there were ANY code coverage tools for SQL Server. Now
I know there is at least one - thanks!
Roy
On 9 Aug 2006 00:43:24 -0700, andrei_tapt@.mail.ru wrote:

>I want to analyze what statemetns were executed in stored procedures,
>for example.
>I'm trying hard to find tools for sql code coverage while there are
>tons of such tools for Java or C#.
>I was able to find only : http://www.sqlpower.com/dsa.html but I want
>to compare it with other solutions.

CM for stored procedures

What methods are people using out there to keep their stored procedures
under CM control? We are using CVS for our CM system. Looking for an easy
way to get stored procedures scripted out as text so we can check in/check
out of a CM system.

Thanks!

-- Mike BrownMike Brown (mbrownATautotecDOTcom) writes:
> What methods are people using out there to keep their stored procedures
> under CM control? We are using CVS for our CM system. Looking for an
> easy way to get stored procedures scripted out as text so we can check
> in/check out of a CM system.

It appears that the most commonly used version-control system is
Microsoft's own Visual SourceSafe. The prime reason for this is
probably because it's easily available. It does not have the name
for being the best tool in the game. (But it's OK for smaller shops.)

Anyway, you can use Query Analyzer or Enterprise Manger to script out
your SQL objects, but this something you should only do once. Once you
have your SQL code i CVS, CVS should be your source, nothing else. If
you need to change a procedure, you check it out and open it in Query
Analyzer. Or even better you use a 3rd party text editor that permits
you to invoke a command-line tool from the editor. That command-line
tool would be ISQL or OSQL. The point with this is that since the
file hits the disk before it hits the database, you have greater
certainty that what you check in is what's the database. (If you use
QA, you can forget to save after that last fix.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp