MSSQL: Find the next available number in a sequence

 So I have this data table, with a non-key column, and I need to write a SQL Query to find the next unused number in the sequence that is greater than a certain starting number.



It's like this:

A long time ago, the table started somewhere around 1000.  This is not a key column, and some of the older records have been archived out of the table.  So I have a weird number that will serve as my minimum.   BUT! there are also some wacky big numbers in the system and I don't want to do a max(n)+1.



Here's a beakdown:

0-35956	these numbers are there, with lots of missing numbers and gaps due to archiving.
35957-1904121900	a few of these are there, I want to find the next available number in this region.
1904121901 and up	I want to ignore these numbers, and not use them (until all the numbers leading up to here are filled).

My Goal here is to create a SQL Query I can run once to find the next available number (meaning the lowest number above 35957 that does not exist in the column).



Here is what worked.




select min(Item_No)+1 as NextID

from Data_Table DT

where not exists

(select 1 from Data_Table DT2 where DT2.Item_No=DT.Item_No+1)

and Item_No > 35956



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Avoiding SQL Injection Attacks

Take a simple SQL Query.




Select account_number from users where username='USER' and password='PASSWORD'
This might be the kind of query you'd use to see if a user entered his username and password correctly.  If you get an account_id then the user is logged in.



So, you put a couple of text boxes on a login page and try to pass the values from the text boxes to the query, like this.




string USER=tbUser.text;

string PASSWORD=tbPassword.text;

string SQL="Select account_number from users where username='" + USER + "' and password='" + PASSWORD + "'"

That way if the user types in "Bob" and "MyPassword" the query executed looks like this.




Select account_number from users where username='Bob' and password='MyPassword'
 Perfect.  This works great, and was a pretty standard way of doing this kind of query for a very long time, dating back to before the world wide web.  But what if a SQL-savvy hacker wants to see what kind of mischief he can cause, and tries playing with your SQL's head.  What if he entered something like this...



Username: ' or ''='

Password: ' or ''='



What does that do to our query?




Select account_number from users where username='' or ''='' and password='' or ''=''
There is a really good chance that this query will bring back all the records in your database, and then think that this user is correctly logged into the very first one.  Note that login queries are not the only kind that can be hacked this way, but they are the easiest targets.  It could just as easily be your help ticket system, or anything else exposed to the web.



This is called a SQL injection attack. It's very common, but luckily it's very easy to thwart.  But there are some programmers who go about it the wrong way.



How not to protect yourself.

1. Don't think your site is too small to get noticed and attacked.


2. Don't  rely on Javascript, as it is easily disabled.


3. Don't rely on Flash, as there are still people who hate it and will not load it on their browsers.


4. Don't rely on HTML settings like maximum character lengths. They can be dispatched.

How to protect yourself.

An easy way to render SQL Injection attacks ineffective is to use sql parameters.  The not so easy part of that - if you have a massive web site - is that you have to edit ALL of your SQL that is exposed to the web.  in SQL Server, you do that like this:


string SQL="Select account_number from users where username=@USER and password=@PASSWORD";
Then, in your query you use parameters to fill in the values, like this:

myCommand.Parameters.AddWithValue("@USER", username.text);

myCommand.Parameters.AddWithValue("@PASSWORD", password.text);
Now (at least in SQL Server), no matter what they enter, it will be treated as query values.  if there is no user who's name and password are ' or ''=', then they will not be logged in.  I know it's a hassle to recode all your website queries.  But nowhere near as big a hassle as having a massive data breach to deal with.





...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Using a Stored Procedure that Returns Multiple Results

Microsoft did a brilliant thing in SQL Server.  I applaud them for this.  You can actually pack more than one SQL Query into a stored procedure and have a single procedure return multiple result sets in one pass.  So I used this feature to write a stored procedure that gathers all the data I need for a certain .rdlc report in my app.  Now I can use this one stored procedure to gather all the data in one fell swoop.

I have done this kind of multiple result set stored procedures in the past and it's a simple matter in code to sort out the data tables, as they are returned in order. Here is some sample code for another instance of this method.

/// <summary>
/// gets the dataset from the database.
/// </summary>
public void Load_Data()
{
    SqlCommand myCommand = new SqlCommand("[dbo].[ExtractWebData]", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;

    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(myCommand);
    ds = new DataSet();
    adapter.Fill(ds);
    ds.Tables[0].TableName = "Customers";
    ds.Tables[1].TableName = "Purchase_Orders";
    ds.Tables[2].TableName = "Purchase_Order_Details";
    ds.Tables[3].TableName = "Inventory";           
}

Now if there was just some way to use that in a report.  It would be great to use a stored procedure to twist all the data into shape before handing it off to a reporting tool!  So you create an xsd. You add a table adapter.

You tell it to use your existing stored procedure.

 You select your stored procedure, and there's the first dataset... But wait.  How do you tell it where the other result sets are?  I want to use all 3 datasets! 







Except that you can't do that.  You see, the .rdlc report requires that you have the data available at design time in order to design the report.  And there is no way to import multiple datasets at once into the .xsd at design time.



From this document: http://msdn.microsoft.com/en-us/library/dd239331.aspx

If multiple result sets are retrieved through a single query, only the
first result set is processed, and all other result sets are ignored.
For example, when you run the following query in the text-based query
designer, only the result set for Production.Product appears in the result pane:

SELECT ProductID FROM Production.Product
GO
SELECT ContactID FROM Person.Contact

I have no idea what the text-based query designer is, but as we saw in SQL Server Management Studio...

In my opinion, this is an EPIC DESIGN FAIL on the part of Microsoft.



We know from the earlier code snippet that Visual Studio can access the data, it just - for some stupid reason - is designed in such a way as to disable this feature in certain cases.  This is completely unacceptable.  But until Microsoft fixes this glaring, stupid, boneheaded omission, we're stuck with it.



Now, I know this blog has no regular readers.  You didn't find this page because you're a fan of Visual Studio Journey.  You found it because you were googling for this problem, and there were no answers anywhere else.  I wish I had better news, but I don't.



Here is the only way I know to work around this issue.  Unravel your stored procedure and execute the whole thing in your client.  Convert each piece into individual queries, and add those to your .xsd.



Alternately, you could split your stored procedure into multiple pieces, like Proc1, Proc2, Proc3... but then you've kind of lost the convenience of the one-stop shopping the stored procedure offers.



Just to be clear: I think that the ability to return multiple result sets from a stored procedure is awesome!  Kudos to the SQL Server development team.  If only the Visual Studio guys would catch some of that brilliance, things would be great.



Addendum:

Just to make myself clear, I think that this feature has awesome potential and multi-table stored procedures are still incredibly useful in Visual Studio.  They just are not usable for rdlc reports (the one thing they would be most ideally suited for in an ideal world).



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

How to easily compare 2 SQL Server Databases.

NOTE: this is for SQL SERVER databases only.  It will not sync Oracle to Interbase or MySql to Sybase.  However, application of this technique may apply to any two databases of the same type. (i.e. oracle to oracle)



I had lost access to the  production database for a time, and wanted to ensure that I had propagated all my recent changes from Development to production - without spending $895 for a SQL management and database analysis system.



So here's how I proceeded to sync my tables from one database to the other.



Open the Production database (or a current backup of it) in SQL Server Management studio.

use this query:

SELECT

    TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION

FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_SCHEMA='dbo'

order by 1,2,4

Note: these columns were important to me, feel free to modify the columns as you see fit.  Also I only cared about the 'dbo' schema, so I filtered for that.  Your needs may vary.



This will give you the column info for all tables in the 'dbo' schema.



Now, save the data by right clicking the grid and selecting Save Results As.

Give it a name like Production_Schema and save as .txt.



Note: csv works too, but I find txt easier to compare.



Next do the same with the development database, naming it something like Development_Schema.txt.



Now you need a diff tool like WinMerge.  Compare these 2 files to see where they differ.  The text files cover all columns in all datatables and views in the schema.

Now go through the differences and see what changes have to be made in production so your software doesn't crash.  When I did this, I noted that there were some changes that were not ready for prime-time yet, so I left them unfixed in production.



The only thing left to compare once this is complete are the stored procedures and functions.  I just made a fresh, empty query and did a Script Stored Procedure As > Drop and Create to > Clipboard and pasted every one of them into the query. I saved it as AllFunctions.SQL and ran it in production to sync the functions.

 I hope this helps!



Comments are welcome!

...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Incrementing a Non Numeric Index in SQL Server

So I have this client, and all his sales invoices in the old system are either numeric, or they are numeric (1000) with a single character prefix (C1000).   We want to auto increment the index automatically, but the autoincrement stuff is not going to work here.



So I wrote a function based on this SQL.

select 'C' + isnull(cast(1+max( cast(SUBSTRING(Invoice_no,2,99) as int)) as varchar),'1000')
from Sales where Invoice_No like 'C%'

Let's work from the inside out.

1. Invoice_no is the column to increment.  ('C1234')


2. first we substring the first character off with SUBSTRING(Invoice_no,2,99) ('1234')


3. we use cast to find the integer of it (1234)


4. we use 1+max to aggregate (find the max value of this integer) and add one. (1235)


5. we cast the result back to a varchar ('1235')


6. then - if isnull gives us a nulll, we use the hard-coded value of '1000'


7. we prepend the 'C' back on  ('C1235')

Now we make this into a function so we can use it like "GetDate()" in the default value of the column.

ALTER FUNCTION [dbo].[NextSalesID] ()

RETURNS varchar(10)
AS
BEGIN
    DECLARE @Answer varchar(10)   
    select @Answer= 'C' + isnull(cast(1+max( cast(SUBSTRING(Invoice_no,2,99) as int)) as varchar),'1000')
    from Sales where Invoice_No like 'C%'
    RETURN @Answer
END

 Now we have to add it as the default value for our column.  This is accomplished by editing the table in SQL Server Management Studio, selecting the column, and...

Binding a scalar Function to a Column Default

MAKE SURE that management studio doesn't add quotes '' around your function name.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Using Return Values From Stored Procedures in C#.NET

Ok, let's say you have a stored procedure that returns a value.






Now lets say you want to test it in SQL Server Management Studio...






This gives us a value of 1, as expected.



Now if we want to call this from C#, there is a little trick we have to play with Parameters.






Note the trick we had to play here (line 19) to access the return value after the procedure runs.  At first, I tried using:

i=myCommand.ExecuteNonQuery();

...but that always yielded a value of -1.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Massive ReportViewer Headache Relieved

Ok I like organization.  Too much perhaps.  But when my project root folder gets full (Microsoft likes everything in there)  I get tired of fishing around for all the parts of everything.  Did I name that thing reportInventory, ir InventoryReport?  So I like to make folders for everything, like so.

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.