Cyclomatic Complexity For Sprocs

May 8, 2015

there's a concept named cyclomatic complexity in software dev which measures the complexity of code by its number of decision points. This would be measured by # of if statements, nested if statements, etc in a method.

Do SQL queries have any type of equivalent? For example, # of joins, # of conditions, etc. Factors into a complexity metric which indicate how complex, risky or error-prone a sproc might be based on certain factors?

View 1 Replies


ADVERTISEMENT

Advantages Of Using CRL Sprocs Over T-SQL Sprocs?

Apr 23, 2006

I am wondering what the advantages of using CRL Sprocs over T-SQL sprocs and what not.

Looking for such comparison and articles on websites resulted in only "how to create CRL sprocs" but none of them were talking about what they are used for in what situations.

I would really appreciate it if you guys can post comments, links and external articles.

Thank you in advance.

View 1 Replies View Related

Need To Take A Step Up In Complexity--please Help

Aug 5, 2005

Hi, I've been writing simple database queries for a long time, but I've come to a point where I need to do more work with fewer queries, and I've not been able to make a dent in it. Please excuse my ignorance.

I have a relational database of prices that holds the following info:

Currencies (yen, euro, dollar)
Industries (chemistry, biology, etc)
Company/Institute size (<500, 500-1000, +1000)
Main Products (lab equipment, classroom equipment, etc.)
Series (subproducts) (100ml beaker, chalk, etc)

I want to build a form where my customers can select their currency, their industry and their company size, and then display prices for a specific product (series).

My problem is on the way to the product select. I don't want to display a huge list of EVERYTHING--I want the customer to be able to first select a Main Product, and then populate a list with the relevant sub product for them to select.

However my database is set up with three tables for the individual products: a MainProducts table with main products and ids, a Series table with sub products (we call them series) and ids, and a third table Products that combines the mainproductid with the subproductid to create a unique Productid that is used throughout the rest of the database to get the prices.

(see the relationships here: http://magneticmirror.com/db.gif. The reason for this is that Series in two different MainProducts can have the same name, and so constitute different Products)

The only way I know how to display the name of the relevant series (sub products) is to get the user to:

*select a MainProduct from a list;
*then select all ProductIDs from the the Products table with that MainProductID;
*then for each row from the Products table, take the SeriesID (sub product) and run that against the Series table to get the name of the series corresponding to that ID

if there are 50 series in a product line, that would be 50 individual queries just to populate the list.

There must be a better way. Is this what joining tables is about?

Thanks for any help.

Luud

View 3 Replies View Related

DTS Package Complexity

Nov 9, 2006

Hi All,

Can anyone tellme how can we determine a DTS Package's complexity. I want to know the elements that determine the complexity of DTS Package.The very little knowledge I have is number of Connections, number of tasks, Number of Activex Script Tasks. Can anybody please give some more data on this.

Thanks in Advance

Regards,
Ravi Kiran

View 2 Replies View Related

Need Help With Password Complexity

Sep 5, 2007

I need to restore a v2000 database to a v2005 database including all users. It's a brand new server and database. When I try to copy all of the new accounts, I get this error:

Msg 15118, Level 16, State 1, Line 1

Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

What do I do with the password complexity? Any ideas?

Regards,

View 3 Replies View Related

How To Enforce Password Complexity

Apr 13, 2004

Hi,
We testing our security.
For NT logins user password complexity and expiration enforced by NT


1.Any way to enforce password complexity and expiration for standard sql login ?

2.any way to check if existing sql login passwords less then N number of characters?

Thank you
Alex

View 3 Replies View Related

Password Complexity Problem

Aug 23, 2007

I need to restore a v2000 database to a v2005 database including all users.

How can I disable password complexity so that I can achieve this ???? I'm sure others have run into this prblem.

Thanks in advance,
Nancy

View 9 Replies View Related

Password Complexity Problem

Aug 23, 2007

I need to restore a v2000 database to a v2005 database including all users.

How can I disable password complexity so that I can achieve this ???? I'm sure others have run into this prblem.

Thanks in advance,
Nancy

View 1 Replies View Related

Complexity Of Nested Self Left Joins?

Jul 23, 2005

Hi,I'm curious about the computational complexity of a query I have. Thequery contains multiple nested self left joins, starting with a simpleselect, then doing a self left join with the results, then doing a selfleft join with those results, etc. What puzzles me is that the timerequired for the query seems to grow exponentially as I add additionalleft joins, which I didn't expect. I expected the inner select toreturn about 25 rows (it does), then I expected the self join to resultin about 25 rows (it does), etc. Each join just adds another column; itdoesn't add more rows. So the left part of the join is staying the samesize, and so is the right part of the join, since I'm always joiningwith the same table.So I would think the time for this query should be (time to join 25rows against the source table) * (num joins), but it seems to besomething like (num rows) ^ (num joins). Any ideas? I'm just trying tounderstand the system a little better. (But if you have any ideas aboutimproving the query, I'm always open to those, too.)The execution plan is what you'd expect: an index seek loop-joined withanother index seek, the results of which are merge-joined with anotherindex seek, the results of which are merge-joined with another indexseek, ad nauseum, until a final "compute scalar cost (39%)" and "select(0%)"For the brave and curious, I've pasted the query below.Thanksselect right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((selectdistinct cp_yyyymm from cp_deal_history where cp_deal_id in('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E','BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymmbetween 200304 and 200504) as x left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') astable0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') astable10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymm

View 4 Replies View Related

Using Temporary Tables To Reduce Complexity

Dec 10, 2007

Right now I have some code, part SQL, mostly C#, to generate reports from a few tables.
I have the following tables:
UserGroups
-GroupId
-UserId

Users
-UserId
-Name

ItemLogs
-LogId
-ItemId
-UserId
* there will be multiple log entries for each item

I'm given a list of GroupIds.

I need to retrieve all the users within those groups
for each user I need to retrieve the UserId and the Name, along with the number of total rows in the ItemLogs with that user's UserId, and the number of distinct ItemIds in ItemLogs that belong to that User.

I'm able to easily do this using C# with minimal SQL, but that involves a lot of looping and small queries which is extremely slow.

I'm thinking that I could use some more advance SQL strategies to retrieve what I need a lot faster, but I don't know how.
My thought is to first create a temporary table with the group ids I'm given so I can quickly retrieve all of the users I need.
Once I have the UserIds, getting the Name is simple, but I don't know how to grab the data I need from ItemLogs without looping through for each User.

Any help would be greatly appreciated.

View 12 Replies View Related

Function To Test Password Complexity

Feb 11, 2008

My aim is to write a function that returns a bit determining if a varchar(30) argument is "complex enough" to serve as a password. I am open to suggestions of better heuristics, but the rules I was initially planning to use were:

1) Greater than 8 characters long.

2) Contains at least one upper case, lower case, number, and punctuation (loosely defined as anything not in the first three categories).

I can loop over the characters in the PW and use CHARINDEX to test against these three subsets, but I thought there just might be a more clever way to accomplish this.

I also mention the "meta" problem since I am all ears if anyone has a better idea of how to measure complexity.

Cheers,

Lyman Hurd

PS Only a hash gets stored in the DB. This is to be used in the stored proecdure where people set their passwords. Thanks in advance for any thoughts.

View 13 Replies View Related

Enforcing Password Complexity In Sql Server 2000

May 23, 2006

Hi all,

Are there any built-ins, utilities, ... within sql server 2000 that can be used to enforce password complexity in the database. i.e at least 8 characteres, upper and lowercase mix, includes at least on number and one punctuation (underscore,...).

I understand that this requirement can be achieved using windows authentication. Unfortunately we do not have that luxury (at the moment).

tx.

View 3 Replies View Related

Complexity Problem With Large Amount Of Records In A Link Table

Feb 1, 2008

A friend reminded me of a problem we tried to solve a few years ago and were unsuccessful.  Below is a copy of the email he sent me.  We would very much appreciate any ideas from the community.  Thanks!Lets start with a simple schema where you have 4
tables:

View 3 Replies View Related

About Those Sp_ Sprocs

Mar 6, 2007

I find the replication put many sprocs with sp_ prefix in our database. Do you think that should be changed? I have been told not to use sp_. See http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html.

View 3 Replies View Related

Sprocs Vs Sql Queries.

Apr 29, 2007

i have a question.  how do i protect my website from sql injection.right now most of my queries are in the form of:  Public Sub updateCredits(ByVal deduct As Int16, ByVal userid As Guid)            Dim cmd As New SqlCommand            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer2").ConnectionString)            cmd.Connection = con            cmd.CommandType = Data.CommandType.Text            cmd.CommandText = "Update [userprofile] SET credits = credits - @c WHERE userID= @id"            cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = userid            cmd.Parameters.Add("@c", SqlDbType.Int).Value = deduct            Using con                con.Open()                cmd.ExecuteNonQuery()                con.Close()            End Using            cmd.Dispose()        End Sub is that a safe way to do it? using parameters and stuff? or should i completely switch over to stored procedures as i hear they are safer. 

View 12 Replies View Related

Sprocs- What Actually Got Executed?

Sep 21, 2005

I know you can use sql profiler to see what sqlcode actually executed when you run a sproc, but is there any way toget this information in asp.net? After executing a sproc, I'd like to send the sqlcode that was sent, to my Audit class. Is there any wayto retrieve this in asp.net itself?cheers!

View 1 Replies View Related

Variables In Sprocs

Sep 27, 2001

While trying to assign a variable a table name then later use the variable name in a select statement (ie select sys_id from @table_name) it fails and says incorrect syntax.

How can I use a variable for a table name to later use within the sproc?

View 2 Replies View Related

Exec And Sprocs

Nov 7, 2000

is it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?

i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.

View 1 Replies View Related

SQL Server Views Vs Sprocs

Aug 30, 2006

I know that stored procedures(sql server) caches stored procedures in memory where it keeps the compiled execution plan in memory, how does it work with the views does sql server store /cache the views. Just wondering Thanks 

View 2 Replies View Related

Generated Sprocs Of VS2005

Oct 24, 2006

Can someone explain the generated sprocs of VS2005 if one column can be nullableDependentOfSeqID = @Original_DependentOfSeqID OR ((@IsNull_DependentOfSeqID = 1) AND (DependentOfSeqID IS NULL))In VS2003 the generated sprocs would beDependentOfSeqID = @Original_DependentOfSeqID OR ((@Original_DependentOfSeqID IS NULL) AND (DependentOfSeqID IS NULL))Which is the best? 

View 1 Replies View Related

UNION 2 Sprocs From Within A Sproc

Aug 21, 2004

Hello,

I have 3 stored procedures.

A, B, C

sproc B has input variable of int



In sproc A, I want to execute B, then C and UNION them together

Can some one possible help me out with syntax?

Thanks a lot.

View 3 Replies View Related

Search For A Phrase In Sprocs

Jul 10, 2007

Due to a business rule change, I had to take what was 1 column in a table and split it off into a new table. Now I need to find every time that column is used in a SPROC and change those sprocs. Is there a way to sift through the sprocs to search for a "phrase" (the column name) -- other than reading through every one manually?

Thanks
Mark

View 11 Replies View Related

Alternative To EM For Writing Sprocs?

Oct 11, 2005

I've been using EM for writing stored procedures. I am ready to upgrade to something that works like an IDE. What do you use?

View 7 Replies View Related

How Can I Search All My Sprocs To See If Any Use A Function?

May 6, 2008

Anyone have the code that would allow me to see if any of my sprocs contain references to a function? I imagine it would someting like select name from sysobjecst where charindex(whatevertextis, 'ufnName') > 0

Thanks

View 5 Replies View Related

Rollback When Debugging T-sql Sprocs In VS2005

Apr 27, 2007

Hi,

Is it possible to rollback changes made to the DB when debugging a t-sql sproc in VS2005? i.e. step through the sproc, then hit rollback and be able to step through it again in the same state

Thanks, moff.

View 4 Replies View Related

Copying Sprocs, DTS To Multiple DBs And Servers

May 3, 2007

We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.

We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.

View 4 Replies View Related

Script To Grant Execute For Sprocs

Aug 7, 2007

Hello.

I'm using what looks to be a popular script to grant execute privileges to stored procedures, and it works great as long as the user account that you want to grant to is not a domain account.
For example, I need to grant execute to myDomaindbUsers, but get a syntax error when the script tries to execute this statement:


SET @SQL = 'GRANT EXECUTE ON [' + @Owner

+ '].[' + @StoredProcedure

+ '] TO myDomaindbUsers'


Incorrect syntax near ''.




The script works fine if a non-concatenated user account is given.
We use Active Directory to manage our access, thus the domaingroup.
Has anyone found a way around this?
Thanks in advance.

Tess


Here's the entire script for anyone who's interested:




USE whateverDatabase

GO

DECLARE @SQL nvarchar(4000),

@Owner sysname,

@StoredProcedure sysname,

@RETURN int

-- Cursor of all the stored procedures in the current database

DECLARE cursStoredProcedures CURSOR FAST_FORWARD

FOR

SELECT USER_NAME(uid) Owner, [name] StoredProcedure

FROM sysobjects

WHERE xtype = 'P'

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name)), 'IsMSShipped') = 0

AND name LIKE 'p%'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursStoredProcedures

INTO @Owner, @StoredProcedure

-- Set the return code to 0

SET @RETURN = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@@FETCH_STATUS = 0) AND (@RETURN = 0))

BEGIN

-- Create the SQL Statement. Since we€™re giving

-- access to all stored procedures, we have to

-- use a two-part naming convention to get the owner.

SET @SQL = 'GRANT EXECUTE ON [' + @Owner

+ '].[' + @StoredProcedure

+ '] TO myDomaindbUsers'

-- Execute the SQL statement

EXEC @RETURN = sp_executesql @SQL

-- Get the next row

FETCH NEXT FROM cursStoredProcedures

INTO @Owner, @StoredProcedure

END

-- Clean-up after the cursor

CLOSE cursStoredProcedures

DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.

IF (@RETURN = 0)

BEGIN

-- Exited fine, commit the permissions

COMMIT TRAN

END

ELSE

BEGIN

-- Exited with an error, rollback any changes

ROLLBACK TRAN



-- Report the error

SET @SQL = 'Error granting permission to ['

+ @Owner + '].[' + @StoredProcedure + ']'

RAISERROR(@SQL, 16, 1)

END

GO

View 3 Replies View Related

Very Basic Questions About Tsql Sprocs

Apr 2, 2008



Hi,

I really confused , I wanna get an rowid on sql 2000 table so I have created a sproc and it's syntax is OK
How can I check it on sql query analyzer? this sql server 2000
Also How can I use that in select statement?

thanks..

here is my select statement which I have to use sproc inside
select custid,ordernum,sku,amount,
dbo.get_rownums (custid,ordernum,sku ) ???
from tp_cod cod

here is my sproc:
CREATE PROCEDURE [dbo].[get_rownums] @custid as varchar(10),@ordernum as varchar(5),@sku as varchar(10) , @i as int output
AS
BEGIN
DECLARE @SkuID as varchar(10)
--DECLARE @i as int
DECLARE got_sku CURSOR FOR
Select sku from tp_cod where custid=@custid and ordernum=@ordernum
set nocount on
set @i=0
OPEN got_sku
FETCH NEXT FROM got_sku INTO @SkuID
WHILE @@FETCH_STATUS = 0
BEGIN
Set @i =@i + 1
if @SkuID=@sku
begin
return @i
end
else
begin FETCH NEXT FROM got_sku INTO @SkuID end
END
CLOSE got_sku
DEALLOCATE got_sku
END
GO

View 25 Replies View Related

Best Way To Execute Heavy Sprocs Wihtout Slowdown

Aug 27, 2007

Hi,

Just a general question here.. I'm designing a web application that might have 50 million - 100 million rows plus. Basically its a simple logging table each row probably only 24 bytes wide, however I can see it taking quite awhile to execute.

The query is basically a group by, showing the amount of "hits" per day.

Are there any special types of strategies I should implement ? Or is a properly designed structure with indexes likely sufficient (on the right hardware of course)

Thanks for any advice!,
Mike

View 7 Replies View Related

Issues With 1.1 Framework Calling SQL 2005 CLR Sprocs?

Oct 5, 2006

Are there any issues calling SQL 2005 CLR bases stored procedures and functions from a web application which uses the dotnet 1.1 framework?

I assume not, but would like advice from those who've been there...

Thanks,

Marie

View 3 Replies View Related

Config Files For .Net Code In Managed Sprocs

Oct 3, 2006

I have been attempting to create a managed stored procedure which calls a web service using WSE 3.0 for security.

It appears that the WSE-generated config file (or possibly the app.config file) is not accessible to the .Net code.

Is there a method for using config files with CLR managed sprocs?


Thanks,
Max

View 1 Replies View Related

How To Script Muplitple SPROCS/VIEWS At One Time

Mar 30, 2006

In Enterprise Manager one can select several SPROCS/VIEWS using the CONTROL key and then Right-Click to script out those objects. Alternativly, pressing CONTROL-C copies, to the clipboard, the T-SQL to create the selected objects.

SQL Management Studio seems to only allow you to script one object at a time.

Is there a way in SQL Management Studio to select multiple objects and generate create or modify scripts?

View 5 Replies View Related

T-SQL (SS2K8) :: Find And Replace Text For All Sprocs On A Server

Apr 24, 2014

There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved