SQL Server - Convert SQL to Stored Procedure

Question

Suppose I have the following structure to a set of tables in my SQL Server (2012) DB:

StartDate:    Col1:     Col2:   ....   Coln:

And, the way the DBA set up the database (have no control over that - I only have query access), all the tables with this structure that I'd want to query have, say, names beginning with MyTbl....

So, I would like to create a query that queries ALL these tables at once to get data for a specific StartDate and I've done it using the following SQL:

declare @t table(tablename varchar(50))
declare @sql varchar(max)
set @sql = ''

insert into @t
SELECT t.name AS table_name FROM sys.tables AS t
WHERE t.name LIKE 'MyTbl%'

select @sql = @sql + 'Select ''' + tablename + ''' as Table_Name, t.* From ' + tablename + 
' t where StartDate = ''2015-01-01'' +
' union ' from @t

Select @sql = substring(@sql, 1, len(@sql) - 6)

exec(@sql)

In other words:

1. Find all tables in my DB with names beginning with MyTbl
2. Query each table for any data with StartDate = '2015-01-01`
3. Union all those queries together to get one big dataset result

The SQL works perfectly, but I'm getting quite stuck in creating a stored procedure from this query that can take in a parameter for StartDate and I don't know enough about stored procedures to do this correctly.

How could I convert this into a stored procedure that takes a date in for StartDate (to replace the ''2015-01-01'' in the query)?

Any help / guidance would be GREATLY appreciated!!!

THANKS!!!

Answer 1

While it can be a little dense if you're not used to the styling Microsoft uses on these pages, the best place to start would be the Create Procedure documentation on MSDN

https://msdn.microsoft.com/en-us/library/ms187926.aspx

That said, creating a stored procedure is pretty straight forward. Here's a really simple procedure that takes a @startDate parameter and then just returns it back. This is just to illustrate how and where you define your parameters

create procedure dbo.MyProcedure
    -- put your input parameters here
    @StartDate date
as
    --put the body of your procedure (i.e. everything you've written in your OP) here
    select @StartDate
go

YOu'll notice however that if you run this twice in a row, you get an error, because it tries to build the same procedure again. Another thing which can come in handy is adding some code before your procedure which will basically check to see if it already exists, and if it does, alter the procedure rather than just blindly re-create it.

This is a snippet from a template I use quite often which handles all of that logic for you. The simplest way to use this is press CTRL-SHIFT-M, which brings up a dialogue to replace all those tags with values you provide.

use [<Database Name, sysname,>]
go

if not exists (select 1
               from sys.procedures with(nolock)
               where name = '<Procedure Name, sysname,>'
                   and [schema_id] = schema_id('<Schema, sysname,dbo>')
                   and type = 'P'
           )
exec ('create procedure [<Schema, sysname,dbo>].[<Procedure Name, sysname,>]
       as
           select ''Procedure not defined.'' as ErrorDescription
       return')  
       --Executed as dynamic SQL since SQL Server Management Studio considures the straight SQL code a syntax error for some reason on the create procedure statement
GO

alter procedure [<Schema, sysname,dbo>].[<Procedure Name, sysname,>]     
    <Parm 1 Name, sysname,include [@]> <Parm 1 Datatype, sysname,><Parm 1 Default, sql_variant,include [=] if used>,
    <Parm 2 Name, sysname,include [@]> <Parm 2 Datatype, sysname,><Parm 2 Default, sql_variant,include [=] if used>
as
/*******************************************************************************************************

********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
set nocount on
---------------------------------------------
-- body of stored procedure
---------------------------------------------


return

Answer 2

I noticed you were not looping through each table .. here is something I had put together

CREATE PROCEDURE get_tabledata (@date DATE)
    AS
    BEGIN
        DECLARE @t TABLE (
            id INT IDENTITY(1, 1)
            ,tablename VARCHAR(50)
            )
        DECLARE @id INT
    DECLARE @tablename VARCHAR(max)
    DECLARE @sql VARCHAR(max)

    SET @sql = ''

    INSERT INTO @t
    SELECT t.NAME AS table_name
    FROM sys.tables AS t
    WHERE t.NAME LIKE 'MyTbl%'

    SET @id = @@ROWCOUNT

    IF (@id > 0)
    BEGIN
        WHILE (@id > 0)
        BEGIN
            SET @tablename = (
                    SELECT tablename
                    FROM @t
                    WHERE id = @id
                    )

            SELECT @sql = @sql + 'Select ' + @tablename + ''' as Table_Name, t.* From ' + @tablename + ' t where StartDate = ' + '' + convert(VARCHAR, @date) + ''

            SET @sql = @sql + ' union'

           Set @id = @id -1; 

        END
SELECT @sql = substring(@sql, 1, len(@sql) - 6)
    END

    EXEC (@sql)
END