Stone Coast Web Design Blog

http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

http://www.calsql.com/2009/10/pivot-with-out-aggregate.html

Suppose you have a SQL Server stored procedure that returns multiple result sets and you want to store all these results into another table.

CREATE procedure [dbo].[mytest] 
as 
select 1 as id, 'test1' as myname 
select 2 as id, 'test2' as myname

When you execute this procedure, it returns two result sets. The following code will copy these two result sets in a table variable.

declare @t table(id int, names varchar(100)) 
insert into @t 
exec mytest
select * from @t

http://csvexpress.expressor-software.com/

Some cool software to move CSV data around to different databases.

SELECT * FROM Employees
SELECT * FROM EmployeeHistory

If table Employees has a one to many relationship with EmployeeHistory and you need to find any missing records from EmployeeHistory, then you could run the two queries above and compare them side-by-side but this pretty tedious and kind of dumb.  A better way is to run an unmatched query. A left outer join of the Employee table to the EmployeeHistory table on the EmpID field will get all matching and non-matching records. To look for every record where the Employee table does not have a matching record in the EmployeeHistory table, we look for a null in the EmployeeHistory table.

SELECT E.* FROM Employees
LEFT OUTER JOIN EmployeeHistory EH
ON E.EmpID = EH.EmpID
WHERE EH.EmpID IS NULL

Similarly, to insert the missing record into the EmployeeHistory table, you could do this:

INSERT INTO EmployeeHistory
SELECT E.* FROM Employee E
LEFT OUTER JOIN EmployeeHistory EH
ON E.EmpID = EH.EmpID
WHERE EH.EmpID IS NULL

DECLARE @Startdate DATETIME, @Enddate DATETIME
SET @Startdate = '2011-01-02 11:35:26'
SET @Enddate = '2011-03-25 03:15:31' 

-- Total seconds in a day
DECLARE @TotalSec int
SET @TotalSec = 24*60*60; 

-- Convert DateDiff into seconds
DECLARE @DiffSecs int
SET @DiffSecs = DATEDIFF(SECOND, @Startdate, @Enddate) 

SELECT
CONVERT(char(2), (@DiffSecs/@TotalSec))as [Days],
CONVERT(char(2), ((@DiffSecs%@TotalSec)/3600)) as [Hours],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)/60)) as [Minutes],
CONVERT(char(2), (((@DiffSecs%@TotalSec)%3600)%60)) as [Seconds]

DECLARE @MyString VARCHAR(50)  

SET @MyString = 'THIS IS MY TEST STRING'    

--REMOVE THE LAST CHARCTER BY USING LEFT FUNCTION  
SET @MyString = LEFT(@MyString, LEN(@MyString) - 1)  
SELECT @MyString AS [OUTPUT] 

I have an SQL query where I am using the SUM function with the GROUP BY clause. Now what I require is that I need to select a couple of more columns from the table without adding those columns in the GROUP BY clause. Is there any way I can do this?

The answer is yes, but only in a very certain way.

Let's imagine that this is your current query:

 select foo , sum(bar) as sumbar from qux group by foo

Now, we want to add two more columns—let's call them other1 and other2—but we don't want to add them to the GROUP BY, because we still want only one row per foo in the results.

The very certain and only way that this will be allowed is by ensuring that the two new columns are inside aggregate functions.

Here's one example of many possibilities:

 select foo , sum(bar) as sumbar , max(other1) as maxcol1 , avg(other2) as avgcol2 from qux group by foo

What is NOT ALLOWED is to put "naked" columns (not inside aggregate functions) into the SELECT clause but leave them out of the GROUP BY clause:

 select foo , sum(bar) as sumbar , other1 , other2 from qux group by foo

Here is the T-SQL code:

'xxx-xx-' + SUBSTRING(dbo.SSN, 6, 9) AS SSN

To display the code in your aspx file

SSN xxxxx<asp:Label ID="lblSSN" runat="server" Text='<%# Eval("SSN") %>' />

Nice article on debugging in SQL Server Management Studio

http://www.diaryofaninja.com/blog/2010/11/23/debugging-sql-queries-function-amp-stored-procedures-with-sql-management-studio

One of the better SQL blogs out there with content for DBAs of all levels:

http://blog.sqlauthority.com/

Great article on how to copy all of the database objects

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

Private Sub OnSelectedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)
        Dim cmd As IDbCommand
        cmd = e.Command
        Dim recordCount As Integer = e.AffectedRows()
        lblRecordCount.Text = "Showing " & recordCount & " IMFs"
End Sub


<asp:SqlDataSource ID="dsMyIMFs" runat="server" ConnectionString="<%$ ConnectionStrings:Tearsheet_ConString %>" 
SelectCommand="p_MyIMFs" SelectCommandType="StoredProcedure" OnSelected="OnSelectedHandler">
</asp:SqlDataSource>

<asp:Label ID="lblRecordCount" runat="server" />

DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)

-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(  
    SELECT id, col FROM @t  
    UNPIVOT  
        (col FOR ListofColumns IN (col1,col2,col3))   
    AS unpivott) AS p
    GROUP BY id 

The syntax shown below will drop a stored procedure if it exists and recreate it.

IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS
-- Logic Comes Here
GO

DECLARE @CurrentDate DateTime
SET @CurrentDate = '2010-03-27 14:15:15.390'

--Print Date in MM/YYYY format

SELECT DATENAME(m, @CurrentDate) 
+ ' ' + CONVERT(varchar(4), DATEPART(year, @CurrentDate))
as 'MM/YYYY'

--Print Date in MM-DD-YY format

SELECT CONVERT(varchar(10), @CurrentDate, 110)
as 'MM-DD-YY'

A very nice little free tool from RedGate software which gives you the ability to search through all of your SQL objects.  Examples from RedGate on how it is useful include:

You want to rename one of your table columns but aren't sure what stored procedures reference it. Using SQL Search, you can search for the column name and find all the stored procedures where it is used.

Finding anything in the SSMS object tree requires a lot of clicking. Using SQL Search, you can press the shortcut combo, start typing the name, and jump right there.

You need to find stored procedures you’ve not yet finished writing. Using SQL Search, you can search for stored procedures containing the text 'TODO'.

You are a DBA, and developers keep using 'SELECT *' in their views and stored procedures. You want to find all these and replace them with a correct list of columns to improve performance and prevent future bugs. Using SQL Search, you can look for 'SELECT *' in the text of stored procedures and views.

You can download the free tool here.

Here’s a simple query to find the Nth Maximum and Minimum values in a SQL Server table column using the Row_Number() function. We will find the 2nd highest and 3rd lowest values in the column.

DECLARE @tmp TABLE(id integer, amount integer)
INSERT INTO @tmp values(4, 9543)
INSERT INTO @tmp values(6, 34)
INSERT INTO @tmp values(3, 54)
INSERT INTO @tmp values(2, 6632)
INSERT INTO @tmp values(5, 645)
INSERT INTO @tmp values(1, 1115)
INSERT INTO @tmp values(7, 345)

-- FIND Nth Maximum value
SELECT id, amount
FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount DESC) AS highest FROM @tmp) as x
WHERE highest = 2
-- FIND Nth Minimum value
SELECT id, amount
FROM(SELECT id, amount, Row_Number() OVER(ORDER BY amount ASC) AS lowest FROM @tmp) as x
WHERE lowest = 3

To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. Here’s a query that first uses DBCC UPDATEUSAGE and then count all the rows in all the tables of a database using the undocumented stored procedure sp_msForEachTable.

DECLARE @DynSQL NVARCHAR(255) 
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@DynSQL) 
EXEC sp_msForEachTable'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'

Here is another method of doing this:

CREATE  Procedure dbo.RecordCountForAllTables 
AS
SELECT 
      'Table Name'=convert(char(25),t.TABLE_NAME),
      'Record Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
Exec RecordCountForAllTables 

Here’s a query that converts a Date to a String in SQL Server:

DECLARE @Dt as DateTime
SET @Dt = '2010-02-22 11:45:17'
SELECT CONVERT(CHAR(8), @Dt, 112)+ REPLACE(CONVERT(CHAR(8), @Dt, 114), ':', '')

In the query shown above, the style value 112 gives an output of yymmdd and a style value 114, gives an output of hh:mi:ss:mmm(24h). To display the milliseconds too, change Char(8) to Char(12).

Here are some simple T-SQL examples  I came across that I wasn’t aware of.

Select * from tblTest Where TestName Like '[ABC]%' (Return all rows of name start with A / B / C)

Select * from tblTest Where TestName Like '[^ABC]%' (Return all rows of name not start with A and B and C)

Select *  into #test from tblTest  (Create temporary table #test and insert all records from tblTest)

Select db_name() (shows the database name which you are working on)

Select @@Servername (Shows name of the server)

Here are some examples of stored procedures on how to do this

http://slashgeek.com/?p=105

The GROUP BY clause is logically processed before the SELECT clause, so at the GROUP BY phase, the OrderYear alias has not yet been created. By using a derived table that contains only the SELECT and FROM elements of the original query, you can create aliases and make them available to the outer query in any element.

SELECT ShipperID, CompanyName
FROM dbo.Shippers AS S
WHERE ShipperID NOT IN
    (SELECT O.ShipVia FROM dbo.Orders AS O
    WHERE O.CustomerID = N'LAZYK')
    
Derived tables Example

DECLARE @EmpID AS Int;
SET @EmpID = 3;

SELECT OrderYear, COUNT(DISTINCT CustomerID) As NumCusts
FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID
      FROM dbo.Orders
      WHERE EmployeeID = @EmpID) AS D
GROUP BY OrderYear

An interesting article on how to do this:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressBackups&referringTitle=Home

REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string.

SELECT Replace('Clarence Thomas is a crazy fool!', 'crazy', 'old')

gives us

Clarence Thomas is a old fool!

Here is how to use REPLACE in conjunction with an UPDATE statement.

UPDATE dbo.authors
SET    city = REPLACE(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';

Here are some simple examples on how to Begin, Commit and Rollback transactions in SQL Server 2008.

-- *********************
-- Explicit Transactions
-- *********************

-- Use @@TRANCOUNT
USE Northwind;
GO

BEGIN TRANSACTION;
UPDATE dbo.Employees
    SET LastName = 'Da Volio'
WHERE LastName = 'Davolio';

IF @@TRANCOUNT > 0
    BEGIN
        PRINT N'The transaction is active.';
        ROLLBACK TRANSACTION;
        PRINT N'The transaction has been rolled back.';
    END
GO

SELECT LastName FROM dbo.Employees;

-- Use @@ERROR 

SELECT message_id, text from sys.messages;

-- Simple error handling
DECLARE @err int
BEGIN TRANSACTION;
INSERT INTO dbo.Customers (CustomerID, CompanyName)
    VALUES ('ALFKI', 'New ALFKI');
SET @Err = @@ERROR
IF @Err = 0
    BEGIN
        PRINT 'Commit transaction.';
        COMMIT
    END
ELSE
    BEGIN    
        PRINT 'Error = ' + CAST(@Err AS varchar(10));
        ROLLBACK
    END
GO

Here are some examples on how to use Try/Catch and RaiseError to address error handling in SQL Server 2008

-- ************************
-- TRY/CATCH Error Handling
-- ************************

BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO dbo.Customers (CustomerID, CompanyName)
        VALUES ('ALFKI', 'New ALFKI');
    COMMIT TRANSACTION;
    PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back.';
END CATCH
GO

-- Display detailed error information
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO dbo.Customers (CustomerID, CompanyName)
        VALUES ('ALFKI', 'New ALFKI');
    COMMIT TRANSACTION;
    PRINT 'Transaction committed.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT
        ERROR_NUMBER() AS Number,
        ERROR_SEVERITY() AS Severity,
        ERROR_STATE() AS State,
        ERROR_PROCEDURE() AS procedureName,
        ERROR_LINE() AS Line,
        ERROR_MESSAGE() AS messageText;
    PRINT 'Transaction rolled back.';
END CATCH
GO

-- Use RAISERROR in a TRY/CATCH
BEGIN TRY
    RAISERROR ('Error severity 9', 9, 1);
    RAISERROR ('Error severity 16', 16, 1);
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;

    SELECT 
        @ErrorMessage = ('In CATCH block: ' + ERROR_MESSAGE()),
        @ErrorSeverity = ERROR_SEVERITY();
    RAISERROR (@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO

-- Create a stored procedure using TRY/CATCH and RAISERROR
CREATE PROCEDURE dbo.InsertCategoryTryCatch
    @CategoryName nvarchar(15) = NULL,
    @CategoryID int = NULL OUTPUT,
    @ReturnCode int = NULL OUTPUT,
    @ReturnMessage nvarchar(255) = NULL OUTPUT 
AS
    SET NOCOUNT ON

    BEGIN TRY
        -- Test if CategoryName null.
        IF @CategoryName IS NULL
            RAISERROR ('Validation failed: Null CategoryName.', 16, 1);

        -- Test if CategoryName already exists.
        IF EXISTS (SELECT CategoryName FROM dbo.Categories 
                WHERE CategoryName=@CategoryName)
            RAISERROR ('Validation failed: Duplicate CategoryName.', 16, 1);

        -- Begin the transaction.
        BEGIN TRANSACTION
        INSERT INTO dbo.Categories (CategoryName)
            VALUES (@CategoryName);
        COMMIT TRANSACTION;
        SET @CategoryID=SCOPE_IDENTITY();
        SELECT @ReturnCode=0, 
            @ReturnMessage='Success! ' + @CategoryName + ' added.';
    END TRY

    BEGIN CATCH
        -- Test to see if we're in a transaction.
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Return error information to client.
        SELECT @ReturnCode = ERROR_NUMBER();
        SELECT @ReturnMessage = ERROR_MESSAGE() +
            ' Severity=' + CAST(ERROR_SEVERITY() AS nvarchar(2)) + 
            '; State=' + CAST(ERROR_STATE() AS nvarchar(2)) + 
            '; Proc=' + CAST(ERROR_PROCEDURE() AS nvarchar(50)) + 
            '; Line=' + CAST(ERROR_LINE() AS nvarchar(10));
    END CATCH
GO

-- Scalar function with parameter
CREATE FUNCTION dbo.fnProductsSoldByProduct
    (@ProductID int = null) 
    RETURNS int
    WITH RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @total int;
        SELECT @Total = SUM(Quantity)
            FROM dbo.[Order Details]
        WHERE ProductID = @ProductID;
        
        RETURN @Total;
    END
GO

Examples of how to call the function above

SELECT dbo.fnProductsSoldByProduct(NULL);
SELECT dbo.fnProductsSoldByProduct(DEFAULT);
SELECT dbo.fnProductsSoldByProduct(3);

-- Causes an error:
SELECT dbo.fnProductsSoldByProduct();
GO

--Another example

-- Create a scalar function to calculate a discount
CREATE FUNCTION dbo.fnCalcDiscountPrice 
(
    @price money,
    @discount int
)
RETURNS money
WITH RETURNS NULL ON NULL INPUT
AS
    BEGIN
        DECLARE @work money
        SELECT @work = @price - (@price * (@discount * .01))
        RETURN @work
    END;


-- Execute the function against the Products table
SELECT ProductID, ProductName, UnitPrice, 
    dbo.fnCalcDiscountPrice(UnitPrice,5) AS [5% Off],
    dbo.fnCalcDiscountPrice(UnitPrice,3) AS [3% Off]
FROM dbo.Products
ORDER BY UnitPrice DESC;

Here is a nice example of how to use output parameters in a stored procedure to debug the proc.

CREATE PROC dbo.InsertShipperOutputParams
    @CompanyName nvarchar(40) = NULL,
    @Phone nvarchar(24) = NULL,
    @ShipperID int = NULL OUTPUT,
    @ReturnCode int OUTPUT,
    @ReturnMessage varchar(255) OUTPUT
AS
    SET NOCOUNT ON;
    SET @ReturnCode = 0;
    SET @ReturnMessage = '';

    IF @CompanyName IS NULL
        SET @ReturnMessage = 'ShipperID is null.';
    ELSE
        BEGIN
            DECLARE @id int, @rows int, @error int;
            INSERT INTO dbo.Shippers(CompanyName, Phone)
                VALUES (@CompanyName, @Phone);
            SELECT @error = @@ERROR, @rows = @@ROWCOUNT, 
                @id = SCOPE_IDENTITY();
            IF @error = 0 AND @rows > 0
                BEGIN
                    SET @ShipperID = @id;
                    SET @ReturnCode = @rows;
                    SET @ReturnMessage = 'Success!';
                END
            ELSE
                SET @ReturnMessage = 'Error=' 
                    + ISNULL(@error, 'No number for this error.');
        END
GO

This is a nice example of how to pass a delimitted list of values to SQL Server and perform some kind of dbase function with those whether that be an UPDATE, DELETE or whatever.

First here is the sample stored procedure we are going to call updating each company sent to it by the PK value, id_company

USE [my_dbase]
GO
CREATE PROCEDURE [dbo].[p_UpdateCompany] 
    @id_assign int = NULL
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE Companies 
    SET markedfordeletion = 1 --or whatever could be a delete statement as well    

END

Next, here is the VB.NET code used to call the sproc

Protected Sub btnCompanySubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strConnection As SqlConnection
        Dim strSql As String
        Dim cmd As SqlCommand
        Dim CompanyValues As String = txtid_company.Text
        
        Dim CompanyList As New ArrayList
        CompanyList.AddRange(Split(CompanyValues, ","))
        
        strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_Repository").ConnectionString)
        strSql = "p_UpdateCompany"
        cmd = New SqlCommand(strSql)
        cmd.CommandType = CommandType.StoredProcedure
        
        For i As Integer = 0 To CompanyList.Count - 1
            Dim CompanyVal As Integer = CompanyList(i)
            
            cmd.Parameters.Add("@id_Company", SqlDbType.Int, CompanyVal)
            Try
                strConnection.Open()
                cmd.Connection = strConnection
                cmd.ExecuteNonQuery()
            Finally
                strConnection.Close()
            End Try
        Next
    End Sub

<form id="form1" runat="server">
    <div>
        <table id="Companies">
            <tr>
                <td>Enter id_company values:<br />
                    (separated by commas)</td>
                <td><asp:TextBox ID="txtid_company" runat="server" TextMode="MultiLine" Rows="5" Columns="25" /></td>
            </tr>           
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnCompanySubmit" runat="server" onclick="btnCompanySubmit_Click" Text="Submit" />
                </td>
            </tr>           
        </table>
    </div>
    
    </form>

Here are some examples of using IN vs. EXISTS

--Customers from Spain who made no orders 

SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
 AND NOT EXISTS
    (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID)
SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
    AND CustomerID NOT IN(SELECT CustomerID FROM Orders 
                          WHERE CustomerID IS NOT NULL)  

  
--Return customer for whom you cannot find any employee from the USA
--For whom you cannot find any order placed for the subject customer and by the select employee                          

SELECT * FROM Customers AS C
WHERE NOT EXISTS
    (SELECT * FROM Employees AS E
     WHERE Country = 'USA'
    AND NOT EXISTS
        (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID
        AND O.EmployeeID = E.EmployeeID))    

I found this interesting. If any of the following field are NULL, then the concatenated value for DirName is also NULL.   You would think that if the less commonly populated fields of Prefix of Suffix were null, dirfname and dirlname would still be returned but this isn’t the case. 

select prefix + ' ' + dirfname + ' ' + dirlname + ' ' + suffix As DirName from individual

To solve this issue, use the IsNull function.

select isnull(prefix, '') + ' ' + dirfname + ' ' + dirlname + ' ' + isnull(suffix, '') As DirName from individual

Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

I’m working on a large project for a federal agency and they want to start using some sensitive data on a Web site I’m working on for them.  While SSL is being used, they also require that some of the data being stored in the database be encrypted.  I found a pretty simple to do it on this article: http://www.sql-server-helper.com/functions/string-encryption.aspx

Here is the code:

Encryption

CREATE FUNCTION [dbo].[Encrypt] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
    
    DECLARE @vEncryptedString NVARCHAR(100)
    DECLARE @vIdx INT
    DECLARE @vBaseIncrement INT
    
    SET @vIdx = 1
    SET @vBaseIncrement = 128
    SET @vEncryptedString = ''
    
    WHILE @vIdx <= LEN(@pClearString)
    BEGIN
        SET @vEncryptedString = @vEncryptedString + 
                                NCHAR(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
                                @vBaseIncrement + @vIdx - 1)
        SET @vIdx = @vIdx + 1
    END
    
    RETURN @vEncryptedString

END
GO

Decrypt

CREATE FUNCTION [dbo].[Decrypt] ( @pEncryptedString NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN

DECLARE @vClearString VARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT

SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vClearString = ''

WHILE @vIdx <= LEN(@pEncryptedString)
BEGIN
    SET @vClearString = @vClearString + 
                        CHAR(UNICODE(SUBSTRING(@pEncryptedString, @vIdx, 1)) - 
                        @vBaseIncrement - @vIdx + 1)
    SET @vIdx = @vIdx + 1
END

RETURN @vClearString

END
GO

Here are examples of how to use these functions

INSERT INTO USERS values('TestUser1', dbo.ENCRYPT('TestPW1'))

SELECT UserID, dbo.Decrypt(UserPW) AS Password from Users

Two great articles on this topic

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

http://weblogs.sqlteam.com/jeffs/jeffs/jeffs/jeffs/jeffs/jeffs/archive/2007/01/02/56079.aspx

A query with an ORDER BY clause cannot be used as a table expression.  That is, a view, inline table-valued function,sub0query, derived table
or common table expression.  This produces the error message "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

SELECT UserID, Name 
FROM (SELECT UserID, Name FROM TSubscribers ORDER BY Name) AS D 

The way to get around this is to use the TOP option.

SELECT UserID, Name
FROM (SELECT TOP 100 Percent UserID, Name, Company FROM TSubscribers ORDER BY Name) AS D

SELECT CASE

SELECT CustomerID, City,
    CASE
        WHEN COUNT(orderID) = 0 Then 'No_Orders'
        WHEN COUNT(orderID) <=2 Then 'Up_to_2_orders'
        WHEN COUNT(orderID) > 2 Then 'More_than_2_orders'
    END AS Category
    FROM Customers
    GROUP BY CustomerID, City

RSS Reporter is a very simple tool and that is the beauty of it. In addition of providing one of the best ways to monitor SQL Server jobs it provides something even cooler – it allows you to write any T-SQL query you want and with one click the results of your query are streamed in an elegant, standard RSS feed that you can subscribe to from any device.

http://www.xsqlsoftware.com/Product/Sql_Server_Rss_Reporter.aspx

This is an interesting example useful for many different applications.  Suppose you create a repeater containing multiple rows and each field contains various types of controls (textboxes, dropdownlists, checkboxes etc.) and you want to update every field in every row with a single button click.  The repeater control has no inherent update capabilities but it can be done.

Sub btnUpdateProductDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs)
      
        Dim id_fieldSelected As Integer
        Dim ProductSelected As Integer
        Dim FeatureSelected As Integer
        Dim SectionSelected As Integer
        Dim createdDateSelected As DateTime
        Dim createdBySelected As String = lblEnteredBy.Text
        Dim IsActiveSelected As Boolean
        Dim ProductLabelSelected As Integer
        
        Dim dataItem As RepeaterItem
        For Each dataItem In rptFieldsInProducts.Items
            
            id_fieldSelected = CType(dataItem.FindControl("lblId_field"), Label).Text
            ProductSelected = CType(dataItem.FindControl("ddlProducts"), DropDownList).SelectedValue
            FeatureSelected = CType(dataItem.FindControl("ddlFeatures"), DropDownList).SelectedValue
            SectionSelected = CType(dataItem.FindControl("ddlSections"), DropDownList).SelectedValue
            createdDateSelected = CType(dataItem.FindControl("lblCreatedDate"), Label).Text
            IsActiveSelected = CType(dataItem.FindControl("chkIsActive"), CheckBox).Checked
            ProductLabelSelected = CType(dataItem.FindControl("ddlProductLabel"), DropDownList).SelectedValue   
            
            'Updates the repeater row
            'The stored procedure that updates this needs these parameter
            'id_field, id_product, id_ProductFeature, id_ProductSection, createdDate, createdBy, IsActive, id_ProductLabel
            
            UpdateProductDetails(id_fieldSelected, ProductSelected, FeatureSelected, SectionSelected, createdDateSelected, _
createdBySelected, IsActiveSelected, ProductLabelSelected)
        Next
    End Sub
    
    Sub UpdateProductDetails(ByVal id_fieldSelected As Integer, ByVal ProductSelected As Integer, ByVal FeatureSelected As Integer, _
                             ByVal SectionSelected As Integer, ByVal createdDateSelected As DateTime, ByVal createdBySelected As String, _
                             ByVal IsActiveSelected As Boolean, ByVal ProductLabelSelected As Integer)
        
        Dim strConnection As SqlConnection
        Dim strSql As String
        Dim cmd As SqlCommand
        
        strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString)
        strSql = "proc_InsertFieldsInProducts"
        cmd = New SqlCommand(strSql)
        cmd.CommandType = CommandType.StoredProcedure
        
        cmd.Parameters.AddWithValue("@id_field", SqlDbType.Int).Value = id_fieldSelected
        cmd.Parameters.AddWithValue("@id_Product", SqlDbType.Int).Value = ProductSelected
        cmd.Parameters.AddWithValue("@id_ProductFeature", SqlDbType.Int).Value = FeatureSelected
        cmd.Parameters.AddWithValue("@id_ProductSection", SqlDbType.Int).Value = SectionSelected
        cmd.Parameters.AddWithValue("@createdDate", SqlDbType.DateTime).Value = createdDateSelected
        cmd.Parameters.AddWithValue("@createdBy", SqlDbType.VarChar).Value = createdBySelected
        cmd.Parameters.AddWithValue("@IsActive", SqlDbType.Bit).Value = IsActiveSelected
        cmd.Parameters.AddWithValue("@id_ProductLabel", SqlDbType.Int).Value = ProductLabelSelected
        cmd.Parameters.AddWithValue("@batchCreatedDate", SqlDbType.DateTime).Value = System.DateTime.Now 

        
        Try
            
           strConnection.Open()
            cmd.Connection = strConnection
            cmd.ExecuteNonQuery()
        Finally
            strConnection.Close()
        End Try
    End Sub

Here is the code for the repeater used for the code above

<asp:SqlDataSource ID="dsProductName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductName), id_Product FROM Products 
WHERE ProductName IS NOT NULL ORDER BY ProductName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsFeatureName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(FeatureName), id_ProductFeature FROM ProductFeatures 
WHERE FeatureName IS NOT NULL ORDER BY FeatureName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsSectionName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(SectionName), id_ProductSection FROM ProductSections 
WHERE SectionName IS NOT NULL ORDER BY SectionName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsProductLabel" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductLabel), id_ProductLabel FROM ProductLabels 
WHERE ProductLabel IS NOT NULL ORDER BY ProductLabel">
</asp:SqlDataSource>

<asp:Repeater id="rptFieldsInProducts" runat="server" OnItemDataBound="ddlProducts_ItemDataBound">
      <HeaderTemplate>
         <fieldset style="width: 900px">
         <legend><h4 class="pageHeader">Product Details</h4></legend>
         <table width="900px" Cellpadding="3" CellSpacing="1" border="0">
             <tr>
                <td class="fieldDetailLabel" width="15%">Product</td>
                <td class="fieldDetailLabel" width="15%">Feature</td>
                <td class="fieldDetailLabel" width="15%">Section</td>                
                <td class="fieldDetailLabel" width="15%">Label</td>
                <td class="fieldDetailLabel" width="15%">Is Active?</td>
            </tr>                  
      </HeaderTemplate>
      <ItemTemplate>                   
             <tr>
               
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlProducts" Runat="Server"
                    DataSourceID="dsProductName"
                    DataTextField="ProductName" 
                    DataValueField="id_Product"
                    CssClass="fieldControl" />
               </td>
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlFeatures" Runat="Server"
                    DataSourceID="dsFeatureName"
                    DataTextField="FeatureName" 
                    DataValueField="id_ProductFeature"
                    CssClass="fieldControl" />
               </td>      
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlSections" Runat="Server"
                    DataSourceID="dsSectionName"
                    DataTextField="SectionName" 
                    DataValueField="id_ProductSection"
                    CssClass="fieldControl" />
               </td>                                            
               <td>
               <asp:DropDownList id="ddlProductLabel" Runat="Server"
                    DataSourceID="dsProductLabel"
                    DataTextField="ProductLabel" 
                    DataValueField="id_ProductLabel"
                    CssClass="fieldControl" />
               </td>
               <td><asp:Checkbox ID="chkIsActive" runat="server" Checked='<%# Eval("IsActive") %>' /></td>
               <td><asp:Label ID="lblId_Field" runat="server" Text='<%# Eval("id_field") %>' class="displayNone" />
               <asp:Label ID="lblCreatedDate" runat="server" Text='<%# Eval("createdDate") %>' class="displayNone" />
               <asp:Label ID="lblCreatedBy" runat="server" Text='<%# Eval("createdBy") %>' class="displayNone" /></td>           
            </tr>
      </ItemTemplate>
      <FooterTemplate>
         <tr>
             <td colspan="5" align="center"><asp:Button ID="btnUpdateProductDetails" Text="Update Product Details" 
CssClass="button" runat="server" OnClick="btnUpdateProductDetails_Click"/></td>
         </tr>           
          </table>         
      </FooterTemplate>
   </asp:Repeater>

This is from the article: http://www.sqlservercentral.com/articles/Administration/68271/

• You have forgotten (or do not have) the "sa" password
• The builtin\administrators account has been removed for security reasons
• You cannot connect to the SQL Server instance

First off you need to shut down all the SQL Server related services. This can be done manually or you can use the batch file below. Either way you need to ensure that all SQL Server services are stopped. If you do the manual process see Image 1, which is a screen shot of the services you need to stop.

If you use this batch file, copy it to your editor like Notepad and save is as a batch file with the .bat extension. If you are stopping an instance you will have to modify the file.

@echo off
cls
echo.*****************************************
echo.**** Shutting Down SQL2005 Services ****
echo.*****************************************
echo.
echo. Shutting Down Integration Services...
net stop "SQL Server Integration Services"
echo.
echo. Shutting Down Full Text Search...
net stop "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Agent...
net stop "SQL Server Agent (MSSQLSERVER)"
echo. 
echo. Shutting Down Analysis Services..
net stop "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Shutting Down Reporting Services...
net stop "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Browser...
net stop "SQL Server Browser"
echo.
echo. Shutting Down SQL Server...
net stop "SQL Server (MSSQLSERVER)"
echo.
echo. 
echo. To stop the SQL Server Brower use this command: net stop "SQL Server Browser"
echo.
echo.
echo.**** Shut Down Completed ****
echo.

The service window should look like this once completed. See Image 2. Now you can open a command line window by clicking on START then RUN finally type: cmd, which will open a new window. From here you execute the .bat file you saved.

Once executed you will see that all the SQL Server services have stopped. If not, stop anything that might still be running. This step is important because if any other SQL Server services are still running you will not be able to connect when SQL Server is started in single user mode.

Now you need to start just the SQL Server in single user mode. In the command line window change to the directory that has SQL Server installed. In my configuration it's here on the C: drive:

cd\program files\microsoft sql server\mssql.1\mssql\binn

This location may be different if you are accessing an instance.

Type the following in the command window for the default instance

sqlservr.exe -m

For a named instance type

sqlservr.exe -m -s <instance name>

Once this is executing you will see screen scroll and then stop. This process is not hung just waiting as SQL Server is now running in single user mode.

Next open another command line window (cmd) and now you can use the sqlcmd tool included with SQL 2005 installation. In this new window type the following and hit enter.

sqlcmd -E

A new prompt will appear. The buildin\administrators account needs to be created and added to the proper role. The following is what you would type in the new command line window:

create login [builtin\administrators] from windows
go
exec sp_addsrvrolemember [builtin\administrators], [sysadmin]
go
shutdown
go

The first line will create a login called builtin\administrators as per the one created within the OS. GO command executes the previous command. Now you add this login to the sysadmin role and finally you shut down the SQL Server that is running in single user mode. Note the first command line window exits the execution of SQL Server and returns the prompt. You may now close this window.

PS. Below is a batch file you can use to start up all SQL Server related servers. I use both to stop and start SQL Server services as needed. If you have stopping an instance you will have to modify the file.

@echo off
cls
echo.---------------------------------------
echo.---- Starting Up SQL2005 Services ----
echo.---------------------------------------
echo.
echo. Starting Up SQL Server...
net start "SQL Server (MSSQLSERVER)"
echo.
echo. Starting Up SQL Agent...
net start "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Starting Up Integration Services...
net start "SQL Server Integration Services"
echo.
echo. Starting Up Full Text Search...
net start "SQL Server FullText Search (MSSQLSERVER)"
echo. 
echo. Starting Up Analysis Services..
net start "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Starting Up Reporting Services...
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Starting Up SQL Browser...
echo. 
echo. To start the SQL Server Brower use this command: net start "SQL Server Browser"
echo.
echo.---- SQL 2005 Servers Start-Up Completed ----
echo.

http://www.sommarskog.se/dyn-search-2005.html#sp_executesql

I create a lot of search forms and I’ve always just used in-line SQL by examining each form element value and then building a SQL string to run against a view or table.  This article discusses how to build a stored procedure in SQL Server 2005 or 2008 using the system stored procedure, sp_executesql.

Here is my example

CREATE PROCEDURE [dbo].[p_Search]

    @id_field int = NULL,
    @FieldName varchar(300) = NULL,
    @TableName varchar(250) = NULL,
    @FieldType varchar(3000) = NULL,
    @DataEntry varchar(250) = NULL                  
                   
AS                                                                    
DECLARE @sql  nvarchar(max),
    @paramlist  nvarchar(4000)                                 
                                     
SELECT @sql =                                                  
    'SELECT * FROM v_FieldDetail          
     WHERE  1 = 1'                                                 
                                                                   
IF @id_field IS NOT NULL                                            
   SELECT @sql = @sql + ' AND id_field = @xid_field'           
                                                                   
IF @FieldName IS NOT NULL                                           
   SELECT @sql = @sql + ' AND FieldName LIKE + ''%'' + @xFieldName + ''%'''            

IF @TableName IS NOT NULL                                           
   SELECT @sql = @sql + ' AND TableName LIKE + ''%'' + @xTableName + ''%''' 

--This one utilizes a split function (below) to accept a comma-delimited string for use with an IN clause

IF @FieldType IS NOT NULL                                           
    SELECT @sql = @sql + ' AND FieldType IN ( SELECT * from dbo.Split( @xFieldType, '',''))'

IF @DataEntry IS NOT NULL                                           
   SELECT @sql = @sql + ' AND DataEntry LIKE + ''%'' + @xDataEntry + ''%'''
                                                                  
   SELECT @sql = @sql + ' ORDER BY FieldName'
    

--PRINT @sql

SELECT @paramlist =                      
      '@xid_field int,
      @xFieldName varchar(300), 
     @xTableName varchar(250),
    @xFieldType varchar(3000),
    @xDataEntry varchar(250)                       
                                                                   
EXEC sp_executesql @sql, @paramlist,@id_field,
    @FieldName,
    @TableName,
    @FieldType,
    @DataEntry
            
                                      
To call this stored procedure and bind the results to a Gridview, do something like this:

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)       

        Dim ds As DataSet           
        Dim strSql As String = "p_Search"
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString)
        
        Dim cmd As New SqlCommand(strSql, con)
        cmd.CommandType = CommandType.StoredProcedure
        
           'p_Search uses dynamic SQL so we pass a value to it only when there is one
           If lblid_field2Text <> "" Then
               cmd.Parameters.Add("@id_field", SqlDbType.Int).Value = Convert.ToInt32(lblid_field.Text)        
        End If
        
        If txtFieldName.Text <> "" Then
            cmd.Parameters.Add("@FieldName", SqlDbType.VarChar).Value = txtFieldName.Text
        End If
        
        If txtTableName.Text <> "" Then
            cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTableName.Text
        End If
   
        ‘This one passes a comma-delimited string for @FieldType which is used in the split function

        If ddlFieldType.SelectedValue <> "" Then
            Dim strSearchValue As String = ""
            Dim li As ListItem
            For Each li In ddlFieldType.Items
                If li.Selected = True Then
                    strSearchValue = strSearchValue & li.Value & "#"
                End If
            Next
            strSearchValue = Left(strSearchValue, (Len(strSearchValue) - 1))
            strSearchValue = Replace(strSearchValue, "#", ",")
            cmd.Parameters.AddWithValue("@FieldType", SqlDbType.VarChar).Value = strSearchValue
        End If

        
        If txtDataEntry.Text <> "" Then
            cmd.Parameters.Add("@DataEntry", SqlDbType.VarChar).Value = txtDataEntry.Text
        End If         
        
        Try
            con.Open()
            Dim MyAdapter As New SqlDataAdapter(cmd) 
            
            ds = New DataSet()
            MyAdapter.Fill(ds, "Requests")
              
            Dim intRecordCount As Integer = ds.Tables(0).Rows.Count() 
            lblRowCount.Text = "Your search returned " & intRecordCount & " records"
            
            GridView1.DataSource = ds.Tables("Requests").DefaultView
            GridView1.DataBind()
        Finally
            con.Close()
        End Try        
    End Sub

--Split function

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     
    
    select @idx = 1     
        if len(@String)<1 or @String is null  return     
    
    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     
        
        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end  

This is an interesting example of how to check for a duplicate record in a table.  If this record does not exist, then proceed with the INSERT.  If a duplicate already exists, then do an UPDATE instead.

-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary
      FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I
   WHERE E.SSN = I.SSN
END

A second way of doing this using a parameter is to count the number of records matching the given criteria.

IF (SELECT COUNT(UserID) 
FROM TLoginStatus
WHERE UserID = @UserID) = 0
        
BEGIN 
INSERT INTO TLoginStatus(UserID, SessionID) Values (@UserID, @SessionID)
     
END 
ELSE
         UPDATE TLoginStatus
         SET SessionID = @SessionID
         WHERE UserID = @UserID

Beautify your SQL code with this handy little utility

http://www.dpriver.com/pp/sqlformat.htm

You can move SQL Server databases a few ways: backup the database and restore it in a new location; detach it and reattach it in the new location or use the ALTER command.  This example uses the latter.

First create 3 databases named db1, db2, db3 using SQL Server Management Studio

Create database db1
  
Create database db2
  
Create database db3

Next, create a stored procedure named p_MoveDatabase compliments of James DiMauro (http://www.sqlservercentral.com/articles/ALTER+DATABASE+MODIFY+FILE/67527/)

*/
  
IF ( OBJECT_ID('dbo.p_MoveDatabase') IS NOT NULL )
  
begin
  
   DROP PROCEDURE dbo.p_MoveDatabase
  
end
  
GO
  
  
create procedure p_MoveDatabase 
         
  
  @NewDataFolder nvarchar(1000),
  
    @NewLogFolder nvarchar(1000),
  
     @DbList nvarchar(4000) 
as
        Begin
        declare @DbTable table (lkey int identity (1,1) primary key, dbname nvarchar(100))
  
        declare @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int )
  
    declare @sql nvarchar(4000)
  
       declare @count int,
  
                       @RowNum int
  
       declare @DbName nvarchar(100)
  
     declare @OldPath nvarchar(1000)
  
   declare @Type int
  
 declare @LogicalName nvarchar(100)
  
        declare @ParmDefinition nvarchar(1000)
  
    declare @FileName nvarchar(100)
  
   declare @NewPath nvarchar(1000)
  
   declare @ShowAdvOpt int
  
   declare @XPCMD int
        set nocount on; 
  
  
        --insert into @DbTable (dbname) values ('testdb1')
  
        --insert into @DbTable (dbname) values ('testdb2') 
  
if right(@DbList,1) = ','
  
Begin
  
      print 'DbList must NOT end with "''"'
  
     return
  
End
declare @MyString NVARCHAR(100)
  
declare @Pos INT
  
declare @NextPos INT
  
declare @String NVARCHAR(4000)
  
declare @Delimiter NVARCHAR(1)
set @String = @DbList
  
set @Delimiter = ','
SET @String = @String + @Delimiter
  
SET @Pos = charindex(@Delimiter,@String)
WHILE (@pos <> 0)
  
BEGIN
  
      SET @MyString = substring(@String,1,@Pos - 1)
  
       insert into @DbTable (dbname) values (LTRIM(RTRIM(@MyString)))
        SET @String = substring(@String,@pos+1,len(@String))
  
      SET @pos = charindex(@Delimiter,@String)
  
END 
  
        set @ShowAdvOpt = cast(( select [value] from sys.configurations where [name] = 'show advanced options') as int)
  
   set @XPCMD = cast(( select [value] from sys.configurations where [name] = 'xp_cmdshell') as int)
  
        if right(@NewDataFolder,1)<> '\' or  right(@NewLogFolder,1)<>'\'
  
 Begin
  
             print 'new path''s must end with \'
  
               return
  
    end
        EXEC sp_configure 'show advanced option', '1'
  
     RECONFIGURE
  
  
        exec sp_configure 'xp_cmdshell' , '1'
  
     RECONFIGURE
  
        print 'NewMdfFolder is ' + @NewDataFolder
  
        print 'NewLdfFolder is ' + @NewLogFolder 
  
        SET @RowNum = 1
  
   SET @count = (select count(*) from @DbTable)
  
        while @RowNum <= @count
  
   Begin
                select @DbName = DBName from @DbTable
  
             where lKey = @RowNum
  
                set @sql = 'select name, physical_name, type from  ' + @DbName + '.sys.database_files'
                insert into @FileTable
  
            exec sp_executesql @sql
  
             
  
  
                -- kill all user connections by setting to single user with immediate
  
             set @sql=  'ALTER DATABASE [' + @DbName  + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
  
            print ''
  
          print 'Executing line  -' + @sql
  
          exec sp_executesql @sql
                -- set db off line
  
                set @sql = 'ALTER DATABASE [' + @DbName + '] SET OFFLINE;'
  
               print ''
  
          print 'Executing line - ' + @sql
  
          exec sp_executesql @sql
                select * from @FileTable
  
          while @@rowcount > 0    
  
          begin
  
                     select top 1 @OldPath = physical_name, @Type = [type], @LogicalName = [name] from @FileTable
                        --move physical files
  
                     set @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX('\', REVERSE(@OldPath), 1))))
  
                     if @type = 0
  
                              begin
  
                                     set @NewPath = @NewDataFolder + @FileName
  
                         end
  
                       else
  
                              begin
  
                                     set @NewPath = @NewLogFolder + @FileName
  
                          end
                        set @Sql = 'EXEC master..xp_cmdshell ''MOVE "' + @OldPath + '" "' + @NewPath +'"'''    
  
                  print ''
  
                  print 'Executing line  -' + @sql
  
                  exec sp_executesql @sql
  
                        --alter file paths
  
                        set @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = ' + @LogicalName + ', FILENAME = "' + @NewPath   + '")'
  
                    exec sp_executesql @sql 
  
                        delete from @FileTable where [name] = @LogicalName
  
                  select * from @FileTable
                end --while 
  
  
                set @sql = 'ALTER DATABASE [' + @DbName + '] SET ONLINE;'
  
         print ''
  
          print 'Executing line  -' + @sql
  
          exec sp_executesql @sql
                SET @RowNum = @RowNum + 1 
                -- allow multi user again.
  
                set @sql=  'ALTER DATABASE [' + @DbName  + '] SET MULTI_USER'
  
             print ''
  
          print 'Executing line  -' + @sql
  
          exec sp_executesql @sql
  
        end
  
        exec sp_configure 'xp_cmdshell' , @XPCMD
  
  reconfigure
        EXEC sp_configure 'show advanced option', @ShowAdvOpt
  
     RECONFIGURE
End --procedure

To call this script, you must pass the names of the new database and log file folders as well as the names of the database(s) that you want to move.

exec p_MoveDatabase @NewDataFolder = 'c:\sqldata\', @NewLogFolder= 'c:\sqllog\',@DbList = 'db1, db2, db3'

Some time ago, I posted something about using user-defined function to split a comma-delimited string into a usable format for searches. This one is simpler.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     
    
    select @idx = 1     
        if len(@String)<1 or @String is null  return     
    
    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     
        
        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end

To use this function do something like this:

CREATE PROCEDURE [dbo].[ba_Company_Profile_Div4_Customize]
(
@id_company int,
@UpdateDescription varchar(8000)
)
AS 
BEGIN
SELECT 
id_event,
EventDate,
UpdateDescription,
EventDescription

FROM events

WHERE id_company=@id_company
AND
UpdateDescription IN (SELECT * FROM dbo.Split(@UpdateDescription,','))
ORDER BY EventDate DESC
END
GO

Great article with examples:

http://www.aspsnippets.com/post/Get-ID-of-the-newly-inserted-record-in-SQL-Server-using-ADONet.aspx

SQL Server's 'smalldatetime' data type includes, as you may have guessed, both the date and time in this format mm/dd/yyyy 00:00:00.  Often times you'll want to display just the date part of this without the time.

Here are a couple of ways of doing this.

The first is explicitly convert the smalldatetime field to CHAR field like this

> CONVERT(CHAR(10), dbo.Events.EventDate, 101) AS EventDate

This produces the format that we want but it's no longer a date field and if it's used in a gridview then you won't be able to sort the data using this field.

Another approach is to get the number of days in the date since 'date 1' and add it to 'date 1'. You get a clean new date where the time component is 0.

> DATEADD(dd, - DATEDIFF(dd, dbo.Events.EventDate, 1), 1) AS EventDate

CREATE PROCEDURE [dbo].[GetLastAccountLogin] 
    -- OUTPUT parameter to hold the count.
    @AccountNo Varchar(50), 
    @ReturnVal smalldatetime = ' ' OUTPUT 
AS 
    -- This will return the last date returned by the SELECT query. 
    Set @ReturnVal = SELECT MAX(Login) FROM TUserLogs WHERE AccountNo=@AccountNo



CREATE PROCEDURE [dbo].[GetTotalUserCompanyProfileViews] 
    -- OUTPUT parameter to hold the count.
    @UserID int,
    @ReturnVal int OUTPUT 
AS 
    -- This will return the totals returned by the SELECT query.     
    SET @ReturnVal = SELECT COUNT(ID) FROM TTrackSearchResults WHERE UserID=@UserID

In the past, I would usually just create a client side or front-end function to change the way data is displayed to the user but this example shows how to do this in SQL Server using a User Defined Function or UDF.

CREATE FUNCTION [dbo].[GradeConversion](@Grade varchar(50))
RETURNS varchar(50)
AS
BEGIN
    DECLARE @MyOutput varchar(50)
    If @Grade='A'
        SET @MyOutput = 'Low Concern'
    Else if @Grade='B'
        SET @MyOutput =  'Low Concern'
    Else if @Grade='C'
        SET @MyOutput =  'Moderate Concern'
    Else if @Grade='D'
        SET @MyOutput =  'High Concern'
    Else if @Grade='F'
        SET @MyOutput =  'Very High Concern'
    Else
        SET @MyOutput =  'Not Rated'
RETURN @MyOutput
END

To call this function you would simply do this dbo.GradeConversion(dbo.companyRatings.ScoreCEOComp) AS ScoreCEOComp

This example shows how you can use a DbTransaction object to add transaction context when using the SqlDataSource control to update data.

<%@Page  Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Diagnostics" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

 Sub On_Click(ByVal source As Object, ByVal e As EventArgs)
        SqlDataSource1.Update()
 End Sub 'On_Click

 Sub On_Sql_Updating(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs)
     Dim command as DbCommand
     Dim connection as DbConnection
     Dim transaction as DbTransaction

     command = e.Command
     connection = command.Connection     
     connection.Open()     
     transaction = connection.BeginTransaction()
     command.Transaction = transaction

 End Sub 'On_Sql_Updating

 Sub On_Sql_Updated(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)

    Dim command As DbCommand
    Dim transaction As DbTransaction

    command = e.Command
    transaction = command.Transaction

    ' We must succeed for the data change to be committed. For 
    ' simplicity, we set this value to true. 
    Dim OtherProcessSucceeded as Boolean = True

    If (OtherProcessSucceeded) Then
        transaction.Commit()
        Label2.Text="The record was updated successfully!"
    Else    
        transaction.Rollback()
        Label2.Text="The record was not updated."
    End If
    End Sub 'On_Sql_Updated
</script>

<html  >
  <head id="Head1" runat="server">
    <title>Command Transaction</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyDatabase%>"
          SelectCommand="SELECT ID, Title, Director FROM Movies"
          UpdateCommand="UPDATE Movies SET Title=@Title WHERE ID=@ID"
          OnUpdating="On_Sql_Updating"
          OnUpdated ="On_Sql_Updated">
          <UpdateParameters>
              <asp:ControlParameter Name="Title" ControlId="TextBox1" PropertyName="Text" />
              <asp:ControlParameter Name="ID" ControlId="DropDownList1" PropertyName="SelectedValue" />
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="Title"
          DataValueField="ID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br /><br />
      <asp:Label id="Label1" runat="server" Text="Enter a new movie title." AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

    </form>
  </body>
</html>

One of the problems with the DBDataReader object is that it keeps an open server-side connection while you are looping through the results of a query.  If you try to execute another command while the first command is still executing, you receive an 'InvalidoperationException' stating that "There is already an open DataReader associated with this connection which must be closed first."  You can avoid this exception by setting the MultipleActiveResultSets connection string option to True when connecting to SQL Server 2005 and above.

<configuration> 
<connectionStrings> 
<clear /> 
<add name="PublishersConnectionString" connectionString="Data Source=localhost;Initial Catalog=Publishers;User ID=xxx;Password=xxx" 
providerName="System.Data.SqlClient; MultipleActiveResultSets=True" />  
</connectionStrings> 

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("PublishersConnectionString").ConnectionString)

        'Create the command object, passing in the SQL string
        Dim strSQL As String = "SELECT au_id, LastName FROM Publishers"
        Dim cmd As New SqlCommand(strSQL)

        Try
            con.Open()
            cmd.Connection = con
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Dim cmdSales As SqlCommand
                cmdSales.CommandType = CommandType.Text
                cmdSales.CommandText = "SELECT  SUM(royaltyper) FROM TitleAuthor WHERE (au_id = @auID)"
                cmdSales.Parameters.AddWithValue("@au_id", reader("au_id"))
                Dim qtySales As Object = cmdSales.ExecuteScalar()
                lbl.Text += reader("LastName").ToString() + ": " + String.Format("{0:C}", qtySales) + "<br />"
            End While
        Finally
            con.Close()
            con.Dispose()
            End
        End Try
    End Sub

Another example and working demo using a TreeView:

http://stonecoastwebdesign.com/code/ShowMARS.aspx

I ran into an issue where I needed to change a list of columns from text and ntext in SQL Server 2000 to varchar(max) and nvarchar(max) fields in SQL Server 2005.  Not wanting to do this one field at a time, I poked around and found a solution using the built in Information_Schema.

To simply produce a list of table names, column names or data types you can do something like this:

SELECT table_name,column_name, data_type
FROM INFORMATION_SCHEMA.Columns
WHERE column_name like '%FirstName%'
ORDER BY table_name

To produce a list of ALTER TABLE statements that will change a column type from Text to Varchar(MAX), run this in Query Analyzer or Management Studio.

SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' VARCHAR(MAX);'
FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'TEXT'
ORDER BY TABLE_NAME

This will produce a list of ALTER TABLE statements that look like this:

ALTER TABLE AuditorFees ALTER COLUMN AuditorChangeNotes VARCHAR(MAX);
ALTER TABLE CEOCompFiguresNotes ALTER COLUMN NotesCEOContract VARCHAR(MAX);
/* etc.

Copy and paste these invidual ALTER TABLE statements in Query Analyzer or Management Studio and run them. Problem solved!

Error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation

Resolution:

SELECT 
        TAnalystAlerts.ID,
        TAnalystAlerts.Category,        
        TAnalystAlerts.Ticker,
        TAnalystAlerts.Title,
        TAnalystAlerts.DatePosted,
        TAnalystAlerts.[Description],
        TAnalystAlerts.URL,
        Companies.id_company,
        Companies.BAStatus
FROM 
        TAnalystAlerts LEFT JOIN Companies ON TAnalystAlerts.Ticker COLLATE Latin1_General_CI_AI  = Companies.Ticker COLLATE Latin1_General_CI_AI

SQL 2000:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=367&lngWId=5

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31893

http://www.sqlteam.com/article/search-and-replace-in-a-text-column

SQL 2005:

Update CompanyProfiles
SET 
CompanyProfiles.CompanyProfile = REPLACE(CAST(companyprofile as varchar(max)),'A' ,'?')
WHERE id_company = 12931

.NET Version:

http://equatorlive.com/blogs/dotnetdave/tech/sql-search-and-replace-on-text-type-columns/

The built-in functions @@Identity() and Scope_Identity() are designed to retrieve the most recently added record's autoincrement identity value from Access and Sql Server respectively. Here are some usage examples.

Access and @@Identity()

The Jet 4.0 provider supports @@Identity(), which means that developers no longer need to use Select Max(ID) or some other method. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("", Category.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.CommandText = query2
    ID = cmd.ExecuteScalar()
  End Using
End Using

SQL Server and Scope_Identity()

While Sql Server also supports @@Identity(), the recommended method for obtaining identity values on this platform is Scope_Identity(),

Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _
    "Select Scope_Identity()"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _
    "Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.Parameters.AddWithValue("@CategoryName", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")
    cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output
    conn.Open()
    cmd.ExecuteNonQuery()
    ID = cmd.Parameters("@CategoryID").Value
  End Using
End Using

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15),
  @CategoryID int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Set @CategoryID = Scope_Identity()
END

Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

The stored procedure:

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15)
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Select Scope_Identity()
END

This site from Microsoft has some great T-SQL examples: http://code.msdn.microsoft.com/SQLExamples

SELECT    
ORDINAL_POSITION  ,
COLUMN_NAME  ,
DATA_TYPE  ,
CHARACTER_MAXIMUM_LENGTH  ,
IS_NULLABLE  ,
COLUMN_DEFAULT
FROM     INFORMATION_SCHEMA.COLUMNS 
WHERE     TABLE_NAME = 'MyAwesomeTable' 
ORDER BY   ORDINAL_POSITION ASC; 

Another method is

sp_help 'tablename' 

The SqlConnection object has a new RetrieveStatistics method in .NET 2.0, which provides some interesting statistics that could come in handy while debugging and performance tuning your .NET applications.  Normally statistics are turned off by default, so you have to set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.  Here is a working demo.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
    
    Sub Page_Load()
        Dim connectionString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("WAITFOR DELAY '0:0:03';SELECT Title,Director FROM Movies", con)
        con.StatisticsEnabled = True
        Using con
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
        End Using
        grdStats.DataSource = con.RetrieveStatistics()
        grdStats.DataBind()
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        td,th
        {
            padding:4px 20px;
        }
    </style>
    <title>Show All Statistics</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <h1>Connection Statistics</h1>
    
    <asp:GridView
        id="grdStats"
        AutoGenerateColumns="false"
        Runat="server">
        <Columns>
        <asp:BoundField DataField="Key" HeaderText="Key" />
        <asp:BoundField DataField="Value" HeaderText="Value" />
        </Columns>    
    </asp:GridView>
    
    </div>
    </form>
</body>
</html>

Erland Sommarskog, a SQL Server MVP, has a nice entry on how to share data between stored procedures.  The example I'll use the most often is using OUTPUT parameters

This method can only be used when the result set is one single row. Nevertheless, this is a method that is sometimes overlooked. Say you have this simple stored procedure:

CREATE PROCEDURE insert_customer @name    nvarchar(50),
                                 @address nvarchar(50),
                                 @city    nvarchar(50) AS
DECLARE @cust_id int
BEGIN TRANSACTION
SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id, name, address, city)
   VALUES (@cust_id, @name, @address, @city)
COMMIT TRANSACTION
SELECT @cust_id

Rewrite this procedure as:

CREATE PROCEDURE insert_customer @name    nvarchar(50),
                                 @address nvarchar(50),
                                 @city    nvarchar(50),
                                 @cust_id int OUTPUT AS
BEGIN TRANSACTION
SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK)
INSERT customers (cust_id, name, address, city)
   VALUES (@cust_id, @name, @address, @city)
COMMIT TRANSACTION

You can now easily call insert_customer from another stored procedure. Just recall that in T-SQL you need to specify the OUTPUT keyword also in the call:

EXEC insert_customer @name, @address, @city, @cust_id OUTPUT

I Was recently asked to move a standalone application inside of WSS 3.0. No matter what I tried I was getting an error message "Connection String has not been properly iniitialized" even though it worked fine in a separate app. The problem turned out to be that the first version which worked in version 1.1 does not work ASP.NET 2.0. In the first example, the connection string is pulling it's connection info from the <AppSettings> section. Version 2.0 doesn't use <appsettings> and instead uses <ConnectionStrings> so the SqlConnection and SqlCommand objects have to be structured a little differently.

********************

My typical method which worked fine in a separate app but did not work in WSS:

Dim strSQLText As String = "SELECT UserID, Name FROM tblUsers ORDER BY Name"
Dim con As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("QCentralConnectionString"))
Dim cmd As New SqlCommand(strSQLText, con)
con.Open()
ddluserID.DataSource = cmd.ExecuteReader()
ddluserID.DataBind()
con.Close()

web.config:

<appSettings>       
    <add key="QCentralConnectionString" value="server=localhost; database=QCentral; uid=xxx; pwd=xxx" />   
</appSettings>

********************

This version worked in WSS 3.0 (ASP.NET 2.0):

Dim strSQLText As String = "SELECT UserID, Name FROM tblUsers ORDER BY Name"
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("QCentralConnectionString").ConnectionString)
Dim cmd As New SqlCommand(strSQLText)
con.Open()
cmd.Connection = con
ddluserID.DataSource = cmd.ExecuteReader()
ddluserID.DataBind()
con.Close()

web.config:

<configuration>
<connectionStrings>
<clear />
<add name="QCentralConnectionString" connectionString="Data Source=localhost;Initial Catalog=QCentral;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" /> 
</connectionStrings>

********************

Use this to find out where a given field name or column reside and is being used in SQL Server.

select so.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name = 'YourColumnNameGoesHere'

or

select so.name, sc.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name like '%YourPartialColumnNameGoesHere%'

or

I feel as though I've written a thousand search forms where a user is presented with a form and then can pick from a list of fields in a form to perform a search against. In the past I've always just build a dynamic WHERE clause looking for a value in each field of the form and then appending that value(s) to the SQL statement i.e.:

Dim strReturnTrend As String = ddlReturnTrend.SelectedValue
If strReturnTrend <> "" Then
   If blnNotFirst = True Then SqlText += " AND "
   SqlText += " ReturnTrend= '" & strReturnTrend & "'"
   blnNotFirst = True
End If

You can avoid this lengthy process of building a dynamic WHERE clause by using default values and optional parameters in your stored procedures.

This single query can search for directors by any combination of the parameters.  The conditions in the where clause are such that when the default value is used, the condition will always be true. So to search for directors by a title starting with 'Big Money' the call would look like the following:

sp_search_directors null,'','','','Big Money'

The conditional logic contains a couple of different ways to do this and they are both equivalent. 

 First, if the default value of a parameter is null then the IsNull construct of the first condition is a simple way to implement this. 

In the second example, the case statement is used to test for the default value and simply subsitute the field value in place of the parameter value, if the parameter value is something other than the default,  then the parameter value is used instead.

For the directors name and address, the condition is a "starts with" match.  For the title the condition is a "contains" match.

One more thing to remember, in SQL, a null = null is not a match.  So if the data columns are allowed to have null values, we need to use IsNull to supply a dummy value for the match and in that case the syntax for the condition will be as follows:

and IsNull(Director_FirstName,'') like
case @DirectorFirstName when '' then IsNull(Director_FirstName,'')
else @DirectorFirstName +'%' end

 Not everyone has a first name so we allow that field to be null.  If the value of the director field is null, it will be converted to an empty string so instead of null = null which does not match, we now have ''='' ( 'empty string' = 'empty string'  ) which does match.

I found this article on one of my favorite sites on how to insert new records using an embedded SELECT statement.  Here is an example:

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

http://www.sqlteam.com/article/using-select-to-insert-records

Inserting a value into an identity column (primary key) is something I need to do often but usually forget how to do.  It's actually pretty easy so I made myself some notes on this topic.

If you try and run this in QA in SQL Server, you'll receive the error below because ID is the PK for tableA 

INSERT tableA(ID, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'tableA' when IDENTITY_INSERT is set to OFF.

The trick is to enable IDENTITY_INSERT for the table like this

SET IDENTITY_INSERT tableA ON

INSERT tableA(ID, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT tableA OFF

I picked this up from SQLTeam.com and it's come in handy a few times.

CREATE PROCEDURE CompareTables(

@table1 varchar(100), 
@table2 Varchar(100),
@T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS

-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.

declare @SQL varchar(8000);

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'

exec ( @SQL)

UPDATE table1
SET col1 = t2.col1
FROM table1 AS t1
JOIN table2 AS t2
ON t1.col2 = t2.col2

Came across this article and code which provides a script for obtaining the size of every database on your SQL Server 2000 box. Very nice...

http://www.sqlservercentral.com/columnists/jtshyman/usingxp_execresultsettoobtaindatabasesizeinformati.asp

I recently had to put into place a method that prevented more than one user from logging into our company web site under the same login ID. With the help of a friend, this is what we came up with. 

Basically, each time a user logs in, the user's UserID and sessionID are stored in a database table at login.  Each time the user visits a page in the site, the user's session ID is matched against what is stored in the database.  If the sessionIDs do not match, then the user is logged out and redirected to a page that says something like "duplicate login detected.  you're a cheating bum. etc."

A typical scenario would work like this:

User A logs into the site and the sessionID is recorded into the dbase. She browses from page and page within the site and on each page her sessionID is verified against what's stored in the database.

User B then logs into the site using User A's ID and password. The sessionID information stored in the database for User A is overwritten by User B. When user A tries to browse to another page, she receives a message stating that someone has logged into the site using her ID.  User A can log back in, but then User B will receive the duplicate login message and round and round they go.

Here's the code for it.

This stored procedure is called on the login page. If the user's userID is not already in the datbase, then it is inserted as a new record. Otherwise, the existing record is updated with the new session ID.

CREATE Procedure sp_LoginStatusCheck
@UserID as Int,
@SessionID as nvarchar(100)


AS
    IF (SELECT COUNT(UserID)
FROM TLoginStatus
WHERE UserID = @UserID) = 0
        
BEGIN
INSERT INTO TLoginStatus(UserID, SessionID) Values (@UserID, @SessionID)
    
END
ELSE
         UPDATE TLoginStatus
         SET SessionID = @SessionID
         WHERE UserID = @UserID
GO

Call sp_LoginStatusCheck in the login page.

Sub AddSessionCheck(ByVal UserID As Integer)
'Due to some bizarre requirement that there must be some value assigned to a Session variable not to lose
'the value for SessionID, I created a random session variable and value            
Session("BA") = "BA"
            
            
Dim myConnection As SqlConnection
Dim cmd As SqlCommand
            
myConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
cmd = New SqlCommand("sp_LoginStatusCheck", myConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = Session.SessionID
                        
Try
myConnection.Open()
cmd.ExecuteNonQuery()
Finally
myConnection.Close()
End Try

End Sub

On each page that I Want to check for a duplicate login, I would first get the userID value which is stored in a cookie value in the Page_Load function. 

<%@ Page Language="VB" Inherits="MyBaseClass" src="~/classes/MyBaseClass.vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
Sub Page_Load()
If Not IsPostBack Then
Dim strUserID As String
If Not IsNothing(Request.Cookies("BA")) Then
strUserID = Request.Cookies("BA")("UserID").ToString()
lblUserID.Value = strUserID
End If


'Call CheckLoginStatus
    CheckLogin(strUserID)
    
End If
End Sub
</script>

I created a new class called MyBaseClass with a sub called CheckLogin that accepts UserID as a parameter. This gets called on every page that I check.

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Web.HttpContext

Public Class MyBaseClass
Inherits System.Web.UI.Page

Public Shared Sub CheckLogin(ByVal UserID As Integer)

'This entire section is the duplicate login prevention section
System.Web.HttpContext.Current.Session("Spice") = "Ginger"
Dim strSessionID = System.Web.HttpContext.Current.Session.SessionID
Dim strLoginStatus As String

Dim MyConnection As SqlConnection
Dim Cmd As SqlCommand

MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Cmd = New SqlCommand("sp_LoginStatusCheck2", MyConnection)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
Cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = strSessionID
Cmd.Parameters.Add("@LoginStatus", SqlDbType.Bit).Direction = ParameterDirection.Output

Try
MyConnection.Open()
Cmd.ExecuteNonQuery()
strLoginStatus = Cmd.Parameters(2).Value
'System.Web.HttpContext.Current.Response.Write(strLoginStatus)
Finally
MyConnection.Close()
End Try

If strLoginStatus = "False" Then
System.Web.HttpContext.Current.Response.Redirect("/duplicate.login.aspx")
End If

End Sub

End Class

Here's the code for the stored procedure sp_LoginStatusCheck2

CREATE Procedure sp_LoginStatusCheck2
@UserID as Int,
@SessionID as nvarchar(100),
@LoginStatus as Bit OUTPUT

--We don't want to check the LoginStatus of enterprise level accounts UserID 884, UserID 7996
AS
IF @UserID=884
    SET @LoginStatus = 1
ELSE IF @UserID=7996 
    SET @LoginStatus = 1
ELSE    

    IF (SELECT COUNT(*) FROM TLoginStatus WHERE UserID = @UserID AND SessionID = @SessionID) > 0
        SET @LoginStatus = 1
    ELSE
        SET @LoginStatus = 0
GO

SELECT <KEY_COLUMN>, COUNT(*)
FROM <TABLE>
GROUP BY <KEY_COLUMN>
HAVING COUNT(*) > 1

I use this on signup forms to prevent a user from registering a second account with the same email address.

CREATE Procedure sp_AddSubscriber
@Name as nvarchar(50),
@Email as nvarchar(50),
@AddSubscriber varchar(255) OUTPUT

AS
 IF (SELECT COUNT(Email)
                  FROM TSubscribers
                  WHERE Email = @Email) = 0
  
  BEGIN INSERT TSubscribers
  (Name, Email)  Values  (@Name, @Email)
SET @AddSubscriber = "True"                           
END              
ELSE
     SET @AddSubscriber = "The email address you have entered has already been registered in our database."
GO

I was recently working on a project where I had to return the larger of two values in my T-SQL code.  COALESCE would not work because often one of the two values was not NULL which COALESCE requires.  The solution ended up being relatively simple by using a CASE-WHEN statement like below.

CASE
  WHEN CEOAnnualBonus > CEOBonus THEN CEOAnnualBonus
  WHEN CEOBonus IS NULL THEN CEOAnnualBonus
  ELSE CEOBonus
END 
AS CEOBonusCombined,
CASE
  WHEN CEOOtherAnnualComp > CEOAllOtherCompensation THEN CEOOtherAnnualComp
  WHEN CEOAllOtherCompensation IS NULL THEN CEOOtherAnnualComp
  ELSE
  CEOAllOtherCompensation
  END 
AS CEOAnnualCompCombined 

Also, I learned about using ISNULL to return a different value when you encounter a NULL value in your data.  This helps clean it up at run time.

ISNULL(dbo.TBenchmarkTemp.ChairIndependent, '-') AS ChairIndependent