simple example of using dynamic SQL:

USE [RRS]
GO
/****** Object:  StoredProcedure [dbo].[GetSerologicalAssaysResultsEmpty]    Script Date: 09/17/2009 13:44:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        pete celliers
-- Create date: 17/9/09
-- Description:    
-- =============================================
alter PROCEDURE [dbo].[GetSerologicalAssayResultDetails]
@LabID int = null,
@ReagentID int = null,
@assaytypeid int = null,
@assayid int = null,
@assaylotid int = null,
@Date datetime = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

--determins if we need to add the and logical operator
DECLARE @andbit    bit
select @andbit = 0

DECLARE @sql    nvarchar(4000)
SELECT @sql =   N' SELECT SerologicalAssayResultDetails.*
FROM  LaboratoryReagents INNER JOIN
               Laboratories ON LaboratoryReagents.LaboratoryID = Laboratories.LaboratoryID INNER JOIN
               Reagents ON LaboratoryReagents.ReagentID = Reagents.ReagentID INNER JOIN
               SerologicalAssayResults ON Laboratories.LaboratoryID = SerologicalAssayResults.LaboratoryID INNER JOIN
               SerologicalAssayResultDetails ON SerologicalAssayResults.SerologicalAssayResultID = SerologicalAssayResultDetails.SerologicalAssayResultID INNER JOIN
               SerologicalAssayLots ON SerologicalAssayResults.SerologicalAssayLotID = SerologicalAssayLots.SerologicalAssayLotID INNER JOIN
               SerologicalAssays ON SerologicalAssayLots.SerologicalAssayID = SerologicalAssays.SerologicalAssayID INNER JOIN
               AssayTypes ON SerologicalAssays.AssayTypeID = AssayTypes.AssayTypeID '

if    @LabID is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' Laboratories.LaboratoryID = ' + convert(varchar, @LabID)
    set @andbit = 1
end

if    @ReagentID is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' Reagents.ReagentID = ' + convert(varchar, @ReagentID)
    set @andbit = 1
end

if    @assaytypeid is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' AssayTypes.AssayTypeID = ' + convert(varchar, @assaytypeid)
    set @andbit = 1
end

if    @assayid is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' SerologicalAssays.SerologicalAssayID = ' + convert(varchar, @assayid)
    set @andbit = 1
end

if    @assaylotid is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' SerologicalAssayLots.SerologicalAssayLotID = ' + convert(varchar, @assaylotid)
    set @andbit = 1
end

if    @Date is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' SerologicalAssayResults.DateEntered = ' + convert(varchar, @Date)
    set @andbit = 1
end





   EXEC sp_executesql @sql OUTPUT

END

--------------

run as follows:

exec [GetSerologicalAssayResultDetails] @LabID=5

be aware of SQL Injection (security risk) that would normally be exposed when dealing with dynamic SQL

Database diagram support objects cannot be installed because this database does not have a valid owner.

This sometimes happen when you attach a database from a different machine or after restoring a database from scratch

Solution:

EXEC sp_dbcmptlevel '<<DBNAME>>', '90';
go
ALTER AUTHORIZATION ON DATABASE::<<DBNAME>> TO "<<LOGINNAME>>"
go
use <<DBNAME>>
go
EXECUTE AS USER = N'dbo' REVERT
go

Cannot open database "DBName" requested by the login. The login failed.
Login failed for user 'DBUser'.

This sometimes happen when you attach a database from a different machine
(because the security doesn't reside inside the database itself)

Solution:

use <<DBNAME>>
go

---create LOGIN <<USERNAME>> WITH PASSWORD = '<<PASSWORD>>' , check_policy = off;
---OR (uncomment which one is needed)
alter LOGIN <<USERNAME>> WITH PASSWORD = '<<PASSWORD>>' , check_policy = off;
go

exec sp_change_users_login 'Auto_Fix', '<<USERNAME>>', null, '<<PASSWORD>>'
go

Make sure your username and password match the web.config file's: <configuration>
     <connectionStrings>
          <clear/>
          <add name="MyConnectionString" connectionString="Data Source=MYPC\SQL2005;Initial Catalog=DBNAME;Persist Security Info=True;User ID=<<USERNAME>>;Password=<<PASSWORD>>" providerName="System.Data.SqlClient"/>