Using stored proc with table parameters

Sample stored procedure. SQL Server 2008+

CREATE PROCEDURE [dbo].[usp_audit_select_criticalresults]
    @exams dbo.ExamIDType READONLY    
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT DISTINCT x.ExamID, 
           case 
             WHEN sap2.ParameterValue is not null then CONVERT(bit,sap2.ParameterValue)
             else CONVERT(bit,0)
           END            
    FROM @exams x
    LEFT JOIN SessionActivityParameters sap
        ON sap.ParameterName='ExamId' AND x.ExamID = sap.ParameterValue
    LEFT JOIN SessionActivityParameters sap2 
        ON sap2.ParameterName='confirmed' AND sap.SessionActivityId =sap2.SessionActivityId
    LEFT JOIN SessionActivity sa
        ON sa.SessionActivityId = sap.SessionActivityId
    WHERE (sa.AttributeId='55CD62C2-AED5-4216-A3C6-FABD0183E130' or sa.AttributeId is null)

            
END

Executing stored procedure with test data in T-SQL:

DECLARE @exams dbo.ExamIDType;

INSERT INTO @exams (ExamID)
SELECT '00993736'
UNION SELECT '00993737'
UNION SELECT '00993738'
UNION SELECT '00993749'
UNION SELECT '00993750'
UNION SELECT '00993766'
UNION SELECT '00993767'

--SELECT * FROM @exams

exec usp_audit_select_criticalresults @exams

Creating a linked server

Example specifying a specific name for the linked server:

/****** Object:  LinkedServer [MYLINKEDSERVER]    Script Date: 04/30/2012 09:24:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYLINKEDSERVER'

                                 , @srvproduct=N'KINDEL-G-W7'

                                 , @provider=N'SQLNCLI'

                                 , @datasrc=N'10.245.8.103'
 /* For security reasons the linked server 

             remote logins password is changed with ######## 
    @server - The name assigned to the linked server definition.

    @srvproduct - Name or IP Address of server sql instance where the linked server                  is being added to.    

    @datasrc - Name or IP Address of remote server sql instance
Sample query:
    SELECT * FROM MYLINKEDSERVER.<database name>.dbo.<table Name>
    
*/
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYLINKEDSERVER'

                                    ,@useself=N'False'

                                    ,@locallogin=NULL

                                    ,@rmtuser=N'sa'

                                    ,@rmtpassword='########'

GO

SQL Server 2008/2008R2 Intellisense

I've noticed that SQL server 2008 intellisense frequently stops working/refreshing the database tree nodes immediately after I have just added a new new table.  

In MS SQL Server Management Studio:

Edit menu -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)

Using Merge to Update or Insert single row of data

Example of recent procedure I wrote to either update an existing row or insert a new row.
Table has the same structure as MembershipEmailFormat the table variable.

Found it easiest to create a table variable to be the source in the Using clause.  I realize that the main purpose of Merge statements really are merging muliple rows between two tables.  My use case is that I need to insert a new email address for a user or modify and existing email address.

CREATE PROCEDURE [dbo].[usp_user_merge_emailformat]
        @UserID UNIQUEIDENTIFIER,
        @Email varchar(256),
        @UseHTML bit
AS
BEGIN
    --SELECT @UserID='04EFF187-AEAC-408E-9FA8-284B31890FBD',
    --       @Email='gkindel@merge.com',
    --       @UseHTML=0
       
    DECLARE @temp TABLE
    (
            UserID UNIQUEIDENTIFIER,
            Email varchar(256),
            HtmlFormat bit
    )       

    INSERT INTO @temp(UserID,Email, HtmlFormat)
    Values(@UserID,@Email,@UseHTML)
           
    SELECT * FROM @temp    

    MERGE dbo.MembershipEmailFormat as t
    USING @temp AS s
    ON (t.UserID = s.UserID and t.Email = s.Email)
    WHEN MATCHED THEN UPDATE SET t.HtmlFormat = s.HtmlFormat
    WHEN NOT MATCHED THEN INSERT VALUES(s.UserID,s.Email,s.HtmlFormat);
END    

How to drop linked Server from SQL Server 2008

Another task I can't seem to remember.

Two useful system stored procedures showing linked servers in SQL 2005/2008


exec sp_helpserver
exec sp_linkedservers

I had trouble removing a linked server because I forgot to remove all of the logins.
(seems like removing a linked server should cascade remove the associated logins)

exec sp_droplinkedsrvlogin 'FUSIONRIS','sa'
exec sp_droplinkedsrvlogin 'FUSIONRIS',NULL
exec sp_dropserver 'FUSIONRIS'

How to create a linked server from a local SQL server instance

I needed to add my local server as a named linked server on my development workstation.
Here is the magic hand shake...

--local linked server
EXEC sp_addlinkedserver
@server='FUSIONRIS',
@srvproduct='',
@provider='SQLNCLI',
@datasrc=''

View TOP 20 expense queries on SQL server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC