Dynamic Group by without Dynamic SQL

Good After All,

I learned some cool SQL magic the other day! I wanted to dynaamiclly drop columns and group the statement in a store procedure without using dynamic sql.  Dropping the unwanted columns was easy, but the group by statement was not that simple to figure out right away.  In tihs example I will count the number of players and change the columns being outputted.

As always let's create the temp tables with data.  Well use the same values we did a few blogs ago to make things easier.


--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Players') ) DROP TABLE #Players

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#TempCount') ) DROP TABLE #TempCount


--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Players(PlayerID INT,FirstName VARCHAR(20),LastName VARCHAR(20))
CREATE TABLE #Roster(TeamID INT, PlayerID INT)


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'

INSERT INTO #Players( PlayerID, FirstName, LastName )
   SELECT 100,'Hank','Aaron'
   UNION ALL
   SELECT 101, 'Robin','Yount'
   UNION ALL
   SELECT 102,'Ernie','Banks'
   UNION ALL
   SELECT 103,'Billy','Williams'
   UNION ALL
   SELECT 104, 'Kirby','Puckett'
   UNION ALL
   SELECT 105, 'Harmon','Killebrew'
   UNION ALL
   SELECT 106, 'Ty','Cobb'
   UNION ALL
   SELECT 107, 'Al','Kaline'
   
INSERT INTO #Roster( TeamID, PlayerID )
   SELECT 100,100
   UNION ALL
   SELECT 100,101
   UNION ALL
   SELECT 200,102
   UNION ALL
   SELECT 200,103
   UNION ALL
   SELECT 300,104
   UNION ALL
   SELECT 300, 105
   UNION ALL
   SELECT 400, 106
   UNION ALL
   SELECT 400, 107
  




Before we can start diving into the group by we need to set up some variables.  Below are the variables I have as well as defaul the value to 0 if its set to null.

--Declaring the variables
DECLARE @TeamOut BIT
DECLARE @CityOut BITD
ECLARE @StateOut BIT

--assinging values to variables
SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=1

--If values are passed as null then convert to 0
IF ISNULL(@TeamOut,'')          = '' SET @TeamOut=0
IF ISNULL(@CityOut,'')          = '' SET @CityOut=0
IF ISNULL(@StateOut,'')         = '' SET @StateOut=0

Now let's look at the SQL select statement.

SELECT COUNT(p.PlayerID) AS total,
       CASE WHEN @TeamOut = 1 THEN Team ELSE NULL END as Team,
       CASE WHEN @CityOut = 1 THEN City ELSE NULL END as City,
       CASE WHEN @StateOut = 1 THEN State ELSE NULL END as STATE
INTO #TempCount      
FROM #Teams t
INNER JOIN #Roster r ON t.TeamID = r.TeamID
INNER JOIN #Players p ON r.PlayerID = p.PlayerID  
GROUP BY    
   (CASE WHEN @TeamOut=1 THEN Team ELSE NULL END),
   (CASE WHEN @CityOut=1 THEN City ELSE NULL END),
   (CASE WHEN @StateOut=1 THEN State ELSE NULL END)

As you can see I use a case statement with the use of the use of the variables.  This seems to work out perfect.  Now what happens when the bit flag is set to 0 and we dont want to show it?  I thought you would ask that question.  It's pretty simple.  We just modified the temp table (#TempCount) that holds all the data.

--Removing columns we dont want to output
IF @TeamOut         = 0 ALTER TABLE #TempCount DROP COLUMN Team
IF @CityOut         = 0 ALTER TABLE #TempCount DROP COLUMN City
IF @StateOut        = 0 ALTER TABLE #TempCount DROP COLUMN STATE

Now we just do a simple Select statement from the temp table to get the results.  Here are a few different examples with just changing the bit flag.


Example 1.

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=NULL

Example 2:

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=1







Example 3:

SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=NULL

This is just a simple example for you, but you can see the value is does.  Hope this was valueable to you as it was to me.

Have a great day!

Robert

Comparing Stuff and Replace Command

Good Morning All,

I found a cool neat function in SQL Server that is pretty helpful for replacing string within text.  This function is called the Stuff (Transact-SQL).

This function is simpilar to the Replace (Transact-SQL) function but they are different.  In the replace function you replace one string with another in any place in the whole text whereas the stuff function only replaces the string if the text is a certain value based on position within the text.  Well look at an example where some of the data got insert wrong with '#' in the teams name and we want to remove the first occurrence within the text.

What will I cover in this blog?

• Setting up a table
• Show how the Replace function works
• Show how the Stuff function works

First let's set up a simple table.

--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

--Create new table
CREATE TABLE #Teams(TeamID INT IDENTITY(10000000,1),Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))

--Insert Team 
INSERT INTO  #Teams(Team,Founded,City,State) 
    SELECT '#Milwaukee Brewers#','1970','Milwaukee','WI'
    UNION ALL
    SELECT '#Minnesota Twins#','1894','Minneapolis','MN'
    UNION ALL
    SELECT 'Chicago Cubs','1870','Chicago','IL'
    UNION ALL
    SELECT 'Detroit Tigers','1894','Detroit','MI'


Seconly, let's see how the replace command works.

SELECT Team, REPLACE(Team,'#','')AS RepaceFunction FROM #Teams

As you can see the replace command both '#' in the Milwaukee Brewers and Minneasota Twins entry.

Thirdly, let's look at the stuff command.  With the use of the case statement we are able to update the correct rows.

SELECT team,
       CASE
           WHEN  SUBSTRING(Team,1,1)='#' THEN STUFF(Team,1,1,' ')
           ELSE team
       END AS StuffFunction 
from #Teams

As you can see in this screen shot that it only removed the first '#' within the Milwaukee Brewers and Minneasota Twins string.

Bonus time!  Let's review both commands in one statement.

SELECT team,
       REPLACE(Team,'#','')AS RepaceFunction,
       CASE
           WHEN  SUBSTRING(Team,1,1)='#' THEN STUFF(Team,1,1,' ')
           ELSE team
       END AS StuffFunction 
from #Teams


Now you can see how powerful this might be in updating data in your database.  This is only one of many ways to use either the replace or stuff functions.

Have a great day everyone,

Robert

Merge Statement and Cross Apply

Good Morning All,

The other day I got to dive into learning about the Merge Statement within Sql Server 2008.  All I can say is wow!  This is one of my favorite new commands in SQL Server.  I think I will be using it a lot.  It makes inserts, updates, and deletes eaiser and more efficient.  In tihs blog I dont have the best explain to make use of this, but it will cover alot of different topics and hopefully help you out.

Before we can write the Merge command there are few other tricks I want I need to explain that I do in this merge statement.

I will explain the following:

1. Turn a comma list variable into a table result
2. Find the cartesian  product of two tables
3. Finally the merge statement

First I will take a comma list variable into a table result.  Here is the current variable I have.

DECLARE @Players VARCHAR(500)

SET @Players = 'Hank Aaron,Robin Yount,Ryan Braun,Corey Hart'

Now I want to output it like the following:

Player Name
Hank Aaron
Robin Yount
Ryan Braun
Corey Hart

To populate this table from the variable I use a function in my database called fn_split by using this select statement.


SELECT value AS PlayerName FROM dbo.fn_Split(@Players,',')

Here is the function defintion for you.  I beleive in giving credit where credit is due, but sadly can't give the person that wrote this function any credit.  I dont remember where I found this function, but I believe it was a forum I was searching on years ago. It has worked great for me.

CREATE FUNCTION dbo.fn_Split(@text VARCHAR(8000), @delimiter VARCHAR(20) = ', ')RETURNS @Strings TABLE
    (value VARCHAR(8000) )
            AS
    BEGIN
    DECLARE @index INT
        SET @index = -1
    WHILE (LEN(@text) > 0)
        BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0)
                    AND (LEN(@text) > 0)
            BEGIN
            INSERT INTO @Strings
                    VALUES (@text)
                        BREAK
            END
        IF (@index > 1)
            BEGIN
            INSERT INTO @Strings
                    VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
            END
        ELSE
            SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
    RETURN
    END
            

Let's set up our temp tables to work with..

--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Teams') )DROP TABLE #Teams
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Roaster') )DROP TABLE #Roaster

--Create new table
CREATE TABLE #Teams(TeamID INT IDENTITY(10000000,1),Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Roaster(TeamID INT,PlayerName VARCHAR(200),CreatedOn DATE, ModifiedOn DATETIME)



Let's create a team for our team table.

--Insert Team 
INSERT INTO  #Teams(Team,Founded,City,State) SELECT  'Milwaukee Brewers','1970','Milwaukee','WI'
  
Secondly, let's work on creatting our cartesian production between the variable and team table.  This is pretty simple with some SQL magic.  The statement makes use of the outer apply as well as the function.

SELECT * FROM #Teams
OUTER APPLY dbo.fn_Split(@Players,',')p

This is the output of the above select statement.

Finally we can create the merge statement.  It's pretty simple now that we have everything set up and ready to go.  We want to be able to insert and update values in the roaster table.

Here is the entire command at one time:

MERGE #Roaster r
USING (SELECT t.TeamID,p.value AS PlayerName 
            FROM #Teams t
            OUTER APPLY dbo.fn_Split(@Players,',')p
      ) tp ON r.TeamID=tp.TeamID 
         AND r.PlayerName=tp.PlayerName
WHEN NOT MATCHED THEN
       INSERT (teamid, PlayerName, CreatedOn)
       VALUES (tp.TeamID,tp.PlayerName,GETDATE())
WHEN MATCHED THEN  
       UPDATE SET ModifiedOn=GETDATE()
       ;

Here is the output for inserting values:

Here is the output for when values are updated:

Well that should do it for my blog entry.  Hopefully this helped you out and give you some insight on some new functions and features to use in SQL Sever.

Cheers,

Robert

How to insert PDFs into a SQL Server DB from T-Sql and command shell

Hello Readers,

I thought I would write a few blogs this week for once.  I learned something new the other day at work and I thought I would share it with everyone.  The other day I had to somehow import a bunch of pdfs from a web server into our new sql server database.  After doing some googling and getting my knowledge up on command shell within T-Sql I found it was pretty simple.  Below are the steps I did to make it happen!

I ran into a few issues with the pdf being on the web server so to make it easier I copied the file from the server to the sql server on a share for a temp location until the file was imported into the database.

Step 1.  Let's set up our temp tables just let the rest of my blog entries.

--remove temp table
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#myfiles'))DROP TABLE #myfiles

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#BinaryFile') ) DROP TABLE #BinaryFile

--Create temp table
CREATE TABLE #myfiles (MyID INT IDENTITY(1,1) PRIMARY KEY, FileName VARCHAR(2000))
CREATE TABLE #BinaryFile(FileID UNIQUEIDENTIFIER,filename VARCHAR(50),Team VARCHAR(50),BinaryFile VARBINARY(MAX))

Step 2.  Let's get a directory Listing of all the files in the share.  With the use of command shell we can easily do this.

INSERT INTO #myfiles (FileName )
EXECUTE MASTER.dbo.xp_cmdshell 'DIR "\\yourserver\share" /A-D /B'

Step 3. Let's create some variables.  These will help in the cursor I wrote to grab the data.

DECLARE @FileName VARCHAR(50) --the pdf name variable
DECLARE @Team VARCHAR(20)    --the team name
DECLARE @FilePath VARCHAR(200)   --file path location for pdf on web server
DECLARE @SqlPath VARCHAR(200)    --file path location for pdf on sql server
DECLARE @FileShare VARCHAR(200)  --share path for pdf on webserver
DECLARE @SqlShare VARCHAR(200)   --share path for pdf sql server
DECLARE @SqlCopyOutput INT       --to see if the file success copied to sql server
DECLARE @string VARCHAR(1000) --String for dynamic sql command

SET @FileShare = '\\server\share\'
SET @SqlShare ='\\server\share\'

Step 4.  The Cursor.  I wrote a cursor that will loop through all the files.  It will first move them from the web server to the sql server share.  After that it will insert them into the database.  If it was a successful insert it will then remove them from the sql server share. I had to use a few different commands such as command shell, t-sql, dynamic sql,  and open row set.  Here is the entire cursor.  It is pretty straight forward.  As you can see I keep with my baseball theme.  I am going to insert the Milwaukee Brewers and Minnesota Twins baseball schedule for 2013.

DECLARE Pdf_Cursor CURSOR FOR
SELECT Pdf.Team,Pdf.FileName
FROM( SELECT FileName ,
      SUBSTRING(FileName,6,((( CHARINDEX('2013.', filename) ) +(LEN(FileName)-18)) )) AS Team
     FROM #myfiles
     WHERE  FileName LIKE '2013%'
    ) Pdf
OPEN pdf_Cursor 
FETCH NEXT FROM pdf_cursor INTO @Team, @FileName
WHILE @@FETCH_STATUS = 0
   BEGIN
       SET @FilePath = @FileShare + @FileName
       SET @SqlPath = @SqlShare + @FileName

       --Move file from webserver to sql server so it will insert into di_media.
       SET @TransferFileString = 'copy ' +@FilePath+ ' ' + @SqlPath
       EXEC MASTER..xp_cmdshell @TransferFileString

       --making sure the file copied over to the sql serve before  inserting into di_media
       EXEC MASTER..xp_Fileexist @SqlPath ,@SqlCopyOutput OUTPUT

       IF @SqlCopyOutput != 1
           SELECT 'Fail to move to share', @FileName
       ELSE
           BEGIN
            SET @string = ' INSERT  INTO #BinaryFile( FileID,FILENAME,Team,BinaryFile)'
            SET @string = @string + 'SELECT  NEWID(),'  + CHAR(39) +@FileName  + CHAR(39) +',' + CHAR(39) + @Team + CHAR(39) +',BulkColumn FROM Openrowset(Bulk ' + CHAR(39) + @SqlPath + CHAR(39) +', SINGLE_BLOB) AS blob'
              
               EXEC(@string)

               --checking to see if pdf made it in the db before deleting from sql server.
               IF EXISTS(SELECT * FROM #BinaryFile WHERE filename = @FileName)
                       BEGIN
                           SET @string = 'Del ' + @SqlPath
                           EXEC xp_cmdshell @string
                           SET @string = ''
                       END              
               ELSE
                   SELECT 'Failed to insert into db', @Team
           END
      
       --clearing out path
       SET @FilePath = ''
       SET @SqlPath = ''
       SET @TransferFileString = ''

       FETCH NEXT FROM pdf_Cursor INTO @Team, @FileName
   END

CLOSE pdf_Cursor
DEALLOCATE pdf_cursor


Step 5.  To query the table.  Looks quickly the temp table to insure the data did make it.  As we can see it was a success!

SELECT * FROM #BinaryFile

The last thing I want to talk about the open row set I used to insert the actually pdf into the database.  This is the key part to the entire thing.  Here is the entire command printed out with out using dynamic sql.

INSERT  INTO #BinaryFile( FileID,FILENAME,Team,BinaryFile)
SELECT  NEWID(),'2013.BrewersSchedule.pdf','Brewers',BulkColumn 
FROM OPENROWSET(BULK '\\serever\share\2013.BrewersSchedule.pdf', SINGLE_BLOB) AS blob


I just learned how to turn this scripting logic into an SSIS package.  Later in another blog entry I will show how you can do this using SSIS.  It might be more useful in the SSIS package.

Dynamic SQL with Output parameters

Hey All!

So today in my post I would like to write about dynamic SQL in a store procedure with output parameter.  I have recently learned all about dynamic SQL.  Dynamic SQL is pretty neat, but I am sure we all know the blesses and curses of Dynamic SQL.  It can be pretty powerful.  The other day I had to create a store procedure to output various columns based on a bit flag as well as a show the record count.  In this blog I will show how I did this using a baseball example of data.

First thing is first let's get all the data set up first.  This little script will create all the temp tables with data for you.


--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Players') ) DROP TABLE #Players

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Roster') ) DROP TABLE #Roster

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#sp_SearchRoster') ) DROP PROC #sp_SearchRoster

--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Players(PlayerID INT,FirstName VARCHAR(20),LastName VARCHAR(20))
CREATE TABLE #Roster(TeamID INT, PlayerID INT)


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'

INSERT INTO #Players( PlayerID, FirstName, LastName )
   SELECT 100,'Hank','Aaron'
   UNION ALL
   SELECT 101, 'Robin','Yount'
   UNION ALL
   SELECT 102,'Ernie','Banks'
   UNION ALL
   SELECT 103,'Billy','Williams'
   UNION ALL
   SELECT 104, 'Kirby','Puckett'
   UNION ALL
   SELECT 105, 'Harmon','Killebrew'
   UNION ALL
   SELECT 106, 'Ty','Cobb'
   UNION ALL
   SELECT 107, 'Al','Kaline'
   
INSERT INTO #Roster( TeamID, PlayerID )
   SELECT 100,100
   UNION ALL
   SELECT 100,101
   UNION ALL
   SELECT 200,102
   UNION ALL
   SELECT 200,103
   UNION ALL
   SELECT 300,104
   UNION ALL
   SELECT 300, 105
   UNION ALL
   SELECT 400, 106
   UNION ALL
   SELECT 400, 107
  

I am sure there are several ways to write a dynamic SQL store procedure.  I found this way a pretty good way.  I would normal write some conditions for the part of when joining tables, but I got a lazy in this blog needless to say. Below is the store procedure.  I am not going to explain the store procedure that much since I have commented within it and it seems pretty start forward if you have ever read dynamic SQL before.

--Create store procedureSET NOCOUNT ONGOCREATE PROC #sp_SearchRoster
   (
       @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @Team VARCHAR(500),
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @RecordCount INT OUTPUT
   )AS BEGIN
   /*Variables*/
   DECLARE @SqlStringCount      NVARCHAR(MAX),
           @SqlStringColumns   VARCHAR(8000),
           @ParmDefinition     NVARCHAR(500),
           @Where              VARCHAR(8000),
           @From               VARCHAR(8000)
          
          
   /*Creating the select statement*/
   SET @SqlStringColumns =N'Select distinct r.teamid,'
   IF @FirstNameOut = 1 SET @SqlStringColumns = @SqlStringColumns + ' p.firstname,'
   IF @LastNameOut      = 1  SET @SqlStringColumns = @SqlStringColumns + ' p.lastname,'  
   IF @TeamNameOut      = 1 SET @SqlStringColumns = @SqlStringColumns + ' t.team,'
   IF @TeamFoundedOut   = 1 SET @SqlStringColumns = @SqlStringColumns + ' datepart(year,t.Founded) as Founded,'
   SET @SqlStringColumns = LEFT(@SqlStringColumns,LEN(@SqlStringColumns)-1)--HACK TO REMOVE TRAILING COMMA
  
   /*Creating From/join statement*/
   SET @From = ' from #Roster r '
   SET @From = @From + ' inner join #Teams t on t.teamid = r.teamid '
   SET @From = @From + ' inner join #players p on r.PlayerID = p.PlayerID '

   /*Creating the where clause*/
   SET @Where = ' where 1=1 '
   
    IF @Team IS NOT NULL SET @Where = @Where + ' and t.team in (' + CHAR(39) +REPLACE(@team,',', ''',''')+CHAR(39)  + ') '
   
    IF @TeamFoundedDateBegin IS NOT NULL AND @TeamFoundedDateEnd IS NOT NULL
       SET @Where = @where + ' and t.founded between ' + CHAR(39) + CONVERT(VARCHAR(10),@TeamFoundedDateBegin, 101) + CHAR(39)+ ' and '+ CHAR(39) + CONVERT(VARCHAR(10), @TeamFoundedDateEnd, 101) + CHAR(39)
  

   /********************************/
   /*Creating the Count SQL String*/
   SET @ParmDefinition = N'@RecordCountOUT int OUTPUT'
   SET @SqlStringCount = 'Select @RecordCountOUT = count(*) '
   SET @SqlStringCount = @SqlStringCount + @From --adding from clause
   SET @SqlStringCount = @SqlStringCount + @Where --adding from where
   EXECUTE sp_executesql @SqlStringCount, @ParmDefinition, @RecordCountOUT = @RecordCount OUTPUT --Executing the sql string to output the record
   /*********************************/   /**********************************/
   /*Creating the Columns SQL String*/
   SET @SqlStringColumns = @SqlStringColumns + @From --adding from clause
   SET @SqlStringColumns = @SqlStringColumns + @Where --adding from where
   EXEC(@SqlStringColumns) --Executing the Sql
   /********************************/
   END
GO


Let's first find all the teams that were founded in 1894.

DECLARE    @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @Team VARCHAR(500),
       @return_value INT,
       @RecordCount INT

EXEC   @return_value =  #sp_SearchRoster
       @FirstNameOut = 0,
       @LastNameOut =0,
       @TeamNameOut=1,
       @TeamFoundedOut  = 1,
       @TeamFoundedDateBegin = '1894',
       @TeamFoundedDateEnd ='1894',
       @team    =NULL,
       @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS N'@RecordCount'

Here are the results.

Let's say we want to just see all the players in the database.  It's simple just turn on and off some flags.

GO
DECLARE    @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @Team VARCHAR(500),
       @return_value INT,
       @RecordCount INT

EXEC   @return_value =  #sp_SearchRoster
       @FirstNameOut = 1,
       @LastNameOut =1,
       @TeamNameOut=0,
       @TeamFoundedOut  = 0,
       @TeamFoundedDateBegin = NULL,
       @TeamFoundedDateEnd =NULL,
       @team    =NULL,
       @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS N'@RecordCount'GO

Here is our result set.

Let's say we only want to Milwaukee Brewers and Minnesota Twins players instead of them.


GO
DECLARE    @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @Team VARCHAR(500),
       @return_value INT,
       @RecordCount INT

EXEC   @return_value =  #sp_SearchRoster
       @FirstNameOut = 1,
       @LastNameOut =1,
       @TeamNameOut=1,
       @TeamFoundedOut  = 0,
       @TeamFoundedDateBegin = NULL,
       @TeamFoundedDateEnd =NULL,
       @team    ='Milwaukee Brewers,Minnesota Twins',
       @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS N'@RecordCount'GO

The result set shown

How do you trouble shoot if your query is failing?   The way I like to do this is using the print command before I execute the SQL string.  If you write PRINT @SqlStringColumns it will output the following for you so you know what string is being executed

SELECT DISTINCT r.teamid ,
         p.firstname ,
         p.lastname ,
         t.team
FROM #Roster r
INNER JOIN #Teams t ON t.teamid = r.teamid
INNER JOIN #players p ON r.PlayerID = p.PlayerID
WHERE 1 = 1 AND t.team IN ( 'Milwaukee Brewers', 'Minnesota Twins' )

As you can see its pretty simple and you can really show and hide columns on the fly.  Now that I have given you the basic go ahead and have fun with Dynamic SQL!

Till next time,

Robert