CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Tim Wilcox
Tim Wilcox

Reputation: 1331

Created a stored procedure in SQL Server Management Studio

I am still quite new to this but am ready to take the next step. I am trying to execute multiple queries at once, if at all possible. From reading some other questions, it seems as if creating a stored procedure is the best route.

The task at hand is creating 36 reports. In this case, hoping to create all 36 reports at one time and not have the results be one continuous entity. Each uses a query very similar to the code shown below. The query shown below is simply the first report to be created out of 36. The only difference in the 36 is the values in the WHERE statement. I am hoping for an operation or method that keeps me from copying and pasting 36 chunks of code into SQL and then copying and pasting those results into an excel spreadsheet.

So my question is 1. Can this be done? 2. Is a stored procedure the best method for this task?

SELECT
    '000000' AS area,
    [SizeClass],
    COUNT(*) AS [Number of Worksites],
    SUM(Employment) AS [Employment In Size Class]
FROM
    dbo.sizeclassreport
WHERE 
    code LIKE '11%' OR code LIKE '21%'
GROUP BY
    [SizeClass]
ORDER BY
    [SizeClass]

Upvotes: 0

Views: 311

Answers (1)

Alex
Alex

Reputation: 2337

It gets tricky if the 36 reports you are talking about pass a different number of arguments and also if they have wildcards AND also if the WHERE clause involves different columns. I would probably write a sproc with the max. number of parameters a report could pass like this for a max of three:

CREATE PROCEDURE dbo.sp_Test
    @code1 varchar(80),
    @code2 varchar(80),
    @code3 varchar(80)
AS
BEGIN
    IF(@code1 is null or @code1 = '')
    BEGIN
        RAISERROR('bad arguments!',16,1);
        RETURN;
    END
    select
        [cols]
    from 
        dbo.tbl
    where
        ([Code] like @code1) OR
        (@code2 is NOT null AND [Code] like @code2) OR
        (@code3 is NOT null AND [Code] like @code3)
END

Where at least code1 is expected at all times and the rest are optional. if the where clause involves different columns depending on the report, then I suggest you do multiple sprocs with a similar approach as above to accomodate the different searches.

Upvotes: 2

Related Questions