🪄SSRS (& Paginated Reports): Design (Part III: Ranking Rows in Reports)

Introduction

In almost all the reports I built, unless it was explicitly requested no to, I prefer adding a running number (aka ranking) for each record contained into the report, while providing different background colors for consecutive rows. The ranking allows easily identify a record when discussing about it within the report or extracts, while the different background colors allow differentiating between two records while following the values which scrolling horizontally. The logic for the background color can be based on two (or more) colors using the ranking as basis.

Tabular Reports

In a tabular report the RowNumber() function is the straightforward way for providing a ranking. One just needs to add a column into the report before the other columns, giving a meaningful name (e.g. RankingNo) and provide the following formula within its Expression:
= RowNumber(Nothing)

When 'Nothing' is provided as parameter, the ranking is performed across all the report. If is needed to restrict the Ranking only to a grouping (e.g. Category), then group's name needs to be provided as parameter:
= RowNumber("Category")

Matrix Reports

Unfortunately, in a matrix report based on aggregation of raw data the RowNumber() function stops working, the values shown being incorrect. The solution I use to solve this is based on the custom GetRank() VB function:

Dim Rank as Integer = 0
Dim LastValue as String = ""

Function GetRank(group as string) as integer
if group <> LastValue then
       Rank = Rank + 1
       LastValue = group
end if

return Rank
end function

The function compares the values provided in the call against a global scope LastValue text value. If the values are different, then a global scope Rank value is incremented by1, while the LastValue is initialized to the new value, otherwise the values remaining the same. The logic is basic also for a non-programmer.

The above code needs to be added into the Code section of Report's Properties for the function to be available:

Adding the code in Report Properties

Once the function added, a new column should be added similarly as for a tabular report,  providing the following code within its Expression in exchange:
=Code.GetRank(Fields!ProductNumber.Value)

Note:
As it seems, on the version of Reporting Services Extension I use, the function has only a page scope, the value being reset after each page. However when exporting the data with Excel the ranking is applied to the whole dataset.

Providing Alternate Colors

Independently of the report type, one can provide an alternate color for table's rows by selecting the row with the data and adding the following expression into the BackaroundColor property:
=Iif(ReportItems!RankingNo.Value Mod 2, "White", "LightSteelBlue")

Notes:
1) For a tabular report the cost of calling the RowNumber function instead of referring to the RankingNo cell is relatively small. One can write it also like this:
=llf(RowNumber(Nothing) Mod 2 = 0, "White", "LightSteelBlue")

Power BI Paginated Reports

The pieces of code considered above can be used also in Power BI Paginated Reports. Even if there's no functionality for adding custom code in the standard UI, one can make changes to the rdl file in Visual Studio or even in Notepad. For example, one can add the code within the "Code" tag at the end of the file before the closing tag for the report:

<Code>

Dim Rank as Integer = 0
Dim LastValue as String = ""
Dim Concatenation = ""

Function GetRank(group as string) as integer
if group <> LastValue then
       Rank = Rank + 1
       LastValue = group
end if

Concatenation = Concatenation & vbCrLf & Rank & "/" & group &amp; "/" & LastValue
return Rank
end function

</Code>
</Report>

Note:

One can consider using a pipeline "|" instead of a forward slash.

Happy coding!

Previous Post <<||>> Next Post

🪄SSRS (& Paginated Reports): First Steps (Part II: Report Parameters)

Introduction

One of the advantages of SQL Server Reporting Services (SSRS) is that once  a query available, in just a few minutes one can display the data into a report. This post shows how to add parameters to a report. For exemplification is used the same view from the first post of the series.

As prerequisite for this post one needs to have ideally the Reporting Services Extension installed. 

Identifying the Parameters

The first step is deciding which parameters are required or make sense to add to the report. The parameters together with their type can be documented in a list similar to the one below:

Parameter list

SSRS differentiates between five types of parameters:  Text, Boolean, Date/Time, Integer and Float. 

Reviewing the Source Query

Before adding the parameters to the report is recommended to look at the source query to check whether it allows the efficient use of parameters. Based on the above list of parameters the standard view needs to be modified as follow (the new columns are marked with a 'new' comment):

-- Individual Customers view
ALTER VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT p.[BusinessEntityID]
, p.PersonType PersonTypeId--new
, CASE p.PersonType 
	WHEN 'SC' THEN 'Store Contact'
    WHEN 'IN' THEN 'Individual (retail) customer'
    WHEN 'SP' THEN 'Sales person'
    WHEN 'EM' THEN 'Employee (non-sales)'
    WHEN 'VC' THEN 'Vendor contact'
    WHEN 'GC' THEN 'General contact'
 END PersonType --new
, p.[Title]
, p.[FirstName]
, p.[MiddleName]
, p.[LastName]
, p.[Suffix]
, pp.[PhoneNumber]
, pnt.[Name] AS [PhoneNumberType]
, ea.[EmailAddress]
, p.[EmailPromotion]
, at.[AddressTypeID] --new
, at.[Name] AS [AddressType]
, a.[AddressLine1]
, a.[AddressLine2]
, a.[City]
, sp.StateProvinceCode --new
, [StateProvinceName] = sp.[Name]
, a.[PostalCode]
, sp.CountryRegionCode -- new
, [CountryRegionName] = cr.[Name]
, p.[Demographics]
FROM [Person].[Person] p
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = p.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID]
	INNER JOIN [Sales].[Customer] c
	ON c.[PersonID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON ea.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.[BusinessEntityID] = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;

Preparing the Source Query

Based on this, the source query can be defined as follows:

-- Customer Addresses
SELECT SIC.Title   
, SIC.PersonType   
, SIC.FirstName   
, SIC.LastName   
, SIC.AddressType   
, SIC.AddressLine1   
, SIC.City   
, SIC.PostalCode   
, SIC.CountryRegionName   
, SIC.PhoneNumber   
, SIC.EmailAddress   
FROM Sales.vIndividualCustomer SIC 
WHERE CountryRegionName = 'Germany'
  AND PostalCode = '42651'
ORDER BY SIC.FirstName  
, SIC.LastName   
, SIC.AddressLine1   

Note:
When preparing a source query for your report is important to limit the number of the records returned to reduce the waiting time until the data are displayed and minimize the traffic to the database. 

Generating the Report

Creating a report is straightforward in SSRS because the 'Report Wizard' takes care of everything in the background. 

Generated report with the Report Wizard

  After testing the report, right click on the created dataset (Dataset1) in design mode and change its name to be meaningful (e.g. Addresses).

Dataset properties

   In this step I prefer transforming the query into a formula because it allows more flexibility in handling the parameters dynamically. Click on the formula and replace the query with the following text: 

 = " -- Customer Addresses " & vbCrLf
 & " SELECT SIC.Title    " & vbCrLf
 & " , SIC.PersonType    " & vbCrLf
 & " , SIC.FirstName    " & vbCrLf
 & " , SIC.LastName    " & vbCrLf
 & " , SIC.AddressType    " & vbCrLf
 & " , SIC.AddressLine1    " & vbCrLf
 & " , SIC.City    " & vbCrLf
 & " , SIC.PostalCode    " & vbCrLf
 & " , SIC.CountryRegionName    " & vbCrLf
 & " , SIC.PhoneNumber    " & vbCrLf
 & " , SIC.EmailAddress    " & vbCrLf
 & " FROM Sales.vIndividualCustomer SIC  " & vbCrLf
 & " WHERE CountryRegionName = 'Germany' " & vbCrLf
 & "   AND PostalCode = '42651' " & vbCrLf
 & " ORDER BY SIC.FirstName   " & vbCrLf
 & " , SIC.LastName    " & vbCrLf
 & " , SIC.AddressLine1 " & vbCrLf

This can be obtained pretty easy by using a formula in Excel like below:

Preparing the dynamic query statement

Formula: = "& "" " & A1 & " "" & vbCrLf "

Note:
In previous versions of reporting environment there must be an empty space after each vbCrLf except the last line, otherwise an error will be shown and the report can't be used at all. When the empty space misses is tricky to find the line where this happened. To validate this one can put the mouse at the end of each line and see whether it lands after the vbCrLf or after an empty space.

Preparing the Queries for Dropdowns

Before adding the parameters it might be a good idea to prepare the queries for the dropdowns. For this let's consider the simplest parameters: 

-- Address Types
SELECT 0 Id
, '(All)' Name
UNION ALL
SELECT AddressTypeID
, Name
FROM [Person].[AddressType]
ORDER BY Id

The query has two parts, one made from the database query to which is added a row marking the '(All)' scenario in which the parameter is actually ignored, and thus all the records for this parameter will be shown. This is typically the default value. 

SSRS has another solution for showing all the values, though I prefer this approach. 

In all the dropdown queries I prefer to use the same names ('Id', respectively 'Name'), independently on how the attributes are called, as they make the purpose explicit. Because the data type of the parameter is integer and is a reference key, the default value can be 0 or also -1 if 0 values are available in the dataset. 

For Text parameters one can use the empty string as parameter, like in the below example for Country Codes: 

-- Countries
SELECT '' Id
, '(All)' Name
UNION ALL
SELECT CountryRegionCode Id
, Name 
FROM [Person].[CountryRegion]
ORDER BY Id

Adding the Parameters

To add a Parameter right click on 'Parameters' within the 'Report Data' and then klick on 'Add Parameter'. This will open the 'Report Parameter Properties' which by default looks like this:

Report Parameter Properties

Each parameter type requires different settings to be considered. 

Defining Freetext parameters

Customer Name, Postal Code and Phone Number are Text parameters shown in Freetext controls and all three are setup similarly (see Parameter List above): 

General settings for text parameters

Defining Dropdowns with Fix Values

The General setting for the Person Type are similar with the ones considered for Freetext, however it has a set of predefined values which can be added in the Available Values as follows: 

Available Values

The default value can be provided into the 'Default Values' section. Just click on 'Add' and enter an empty space:

Default Values for fix dropdown

Defining Dropdowns with Dynamics Values

Country parameter is defined similarly, however as Available Values is selected the 'Get values from a query' option. The Countries dataset is selected as source while 'Id' and 'Name' are provided as below: 

Available Values for dynamic dropdown

Same can be done also for the AddressType parameter having the AddressTypes datasource as source. 

After adding all the parameters and choosing the order in which they should be displayed, the Report Data section will look like this:

Report's parameters

Note:

When designing parameters one should consider also the best practices for avoiding the poor design of parameters.

Adding the Parameters to the Query

For this the query needs to be added as follows: 

= "-- Customer Addresses " & vbCrLf
 & " DECLARE @Country as nvarchar(3) = '" & Parameters!Country.Value & "'" & vbcrlf 
 & " DECLARE @CustomerName as nvarchar(100) = '" & Parameters!CustomerName.Value & "'" & vbcrlf 
 & " DECLARE @PostalCode as nvarchar(15) = '" & Parameters!PostalCode.Value & "'" & vbcrlf 
 & " DECLARE @PhoneNumber as nvarchar(25) = '" & Parameters!PhoneNumber.Value & "'" & vbcrlf 
 & " DECLARE @AddressType as int = " & Parameters!AddressType.Value & vbcrlf 
 & " DECLARE @PersonType as nvarchar(3) = '" & Parameters!PersonType.Value & "'" & vbcrlf 
 & " SELECT SIC.Title   " & vbCrLf
 & ", SIC.PersonType   " & vbCrLf
 & ", SIC.FirstName   " & vbCrLf
 & ", SIC.LastName   " & vbCrLf
 & ", SIC.AddressType   " & vbCrLf
 & ", SIC.AddressLine1   " & vbCrLf
 & ", SIC.City   " & vbCrLf
 & ", SIC.PostalCode   " & vbCrLf
 & ", SIC.CountryRegionName   " & vbCrLf
 & ", SIC.PhoneNumber   " & vbCrLf
 & ", SIC.EmailAddress   " & vbCrLf
 & " FROM Sales.vIndividualCustomer SIC " & vbCrLf
 & " WHERE 0=0 " & vbCrLf
 & IIf(Parameters!Country.Value<> "", " 		  AND SIC.CountryRegionCode = @Country", "") & vbcrlf 
 & IIf(Parameters!CustomerName.Value<> "", " 		  AND (SIC.FirstName  LIKE '%' + @CustomerName + '%' OR SIC.LastName  LIKE '%' + @CustomerName + '%')", "") & vbcrlf 
 & IIf(Parameters!PostalCode.Value<> "", " 		  AND SIC.PostalCode = @PostalCode", "") & vbcrlf 
 & IIf(Parameters!PhoneNumber.Value<> "", " 		  AND SIC.PhoneNumber LIKE '%' + @PhoneNumber + '%'", "") & vbcrlf
 & IIf(Parameters!PersonType.Value<> "", " 		  AND SIC.PersonTypeId = @PersonType", "") & vbcrlf
 & IIf(Parameters!AddressType.Value<> 0, " 		  AND SIC.AddressTypeId = @AddressType", "") & vbcrlf 
 & " ORDER BY SIC.FirstName  " & vbCrLf
 & ", SIC.LastName   " & vbCrLf
 & ", SIC.AddressLine1   " & vbCrLf

For each report parameter is defined a SQL parameter in the header with the appropriate data type and length, and a constraint within the WHERE clause. The parameter will be considered only if a valid value was provided. One has to take into consideration the type of the parameter. 

Testing the Report

If everything worked correctly the report will run without problems:

Testing the report

For testing the report it makes sense to choose the parameters one by one from the general to the more particular case. For example for a first test I would choose 'Germany' as Country, then in a second run i would choose one of the values from the resulting dataset, e.g. 'Arthur' as Customer Name, while in a thirst run I would pick a value for the Postal Code. This approach allows minimizing the volume of time required for testing. 

Troubleshooting

Errors in formulas are sometimes difficult to troubleshoot especially when the report refuses to run. The SQL errors are easier to troubleshoot into Management Studio (SSMS). Just copy the query in SSMS and replace the added formatting.

Errors into the formula require unfortunately more time, however most of the errors I had were related to the missing empty space, or forgetting to close a quote or incorrect formatting.

Previous Post <<||>> Next Post

💎SQL Reloaded: Porting 32 bit CLR UDFs on 64 bit Platforms

Today I tried to port on a 64 bit platform a few of the CLR UDFs created in the previous posts, this time being constrained to use Visual Basic Studio 2010 Express to create and build the assembly on a x86 platform, and install the assembly on a x64 SQL Server box. From the previous troubleshooting experience between the two platforms, I knew that there will be some challenges, fortunately there was nothing complex. Under SSIS 2008 it’s possible to choose the targeted platform, therefore I was expecting to have something similar also in VB Studio 2010 Express, and after a simple review of Project Properties, especially in what concerns the Compile settings, I found nothing relevant. I tried then the standard approach, so I built the solution, copied the .dll on the target server and tried to register the assembly though I got the following error:

Msg 6218: %s ASSEMBLY for assembly '%.*ls' failed because assembly '%.*ls' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message%.*ls

After several attempts to google for a solution on how to port 32 bit CLR UDFs on 64 bit Platform or on how to configure VB Studio 2010 Express in order to target solutions for 64 bit platforms, I found a similar question (VB Express target x86 Platform) in MSDN,  Johan Stenberg’s answer completed by JohnWein’s hint, led me to the “Issues When Using Microsoft Visual Studio 2005” document, to be more specific to 1.44 section "References to 32-bit COM components may not work in VB and C# Applications running on 64-bit platforms", of importance being the part talking about "Express Editions". In the document is specified how to modify the project and add in the first PropertyGroup section a PlatformTarget tag with the text value x86, therefore what I had to do was to add the respective tag but with the value x64. After doing this change everything worked smoothly. It’s kind of a mystery why Microsoft hasn’t enabled this feature in Express versions, but in the end I can live with it as long there is a workaround for it.

Happy coding!

💎SQL Reloaded: Number of Records II (The CLR Version)

While scanning rapidly the daily posts from MSDN blogs, I stopped on Chris Skorlinski’s “Example code executing TSQL for each Table in a database” post, wondering then on whether he’s using other techniques than I used in the past. I run into this type of need several times, especially in cases in which I wanted to report in an automated manner the number of records for a given set of tables, typically during ETL tasks. The problem could be easily solved with the help of a cursor, though it’s not the most elegant technique, even if it solves the problem fairly easy. 

I remember how I tried to find a way to execute a dynamic statement using a dynamic script inside of a  UDF (user-defined function), though that’s not possible because a stored procedure can’t be executed inside of a function. An alternative would be to use the OPENROWSET function and execute a statement using a linked server, though the OPENROWSET function doesn’t work with dynamically created statements.

Five years ago, soon after SQL Server 2005 RTM appeared on the market, I tried to create a UDF programmatically using an example from Bob Beauchemin & co.’s “A First Look at SQL Server 2005 for Developers” book. The example worked then, but not without some inherent headaches, having to spent some time on troubleshooting the received errors, and if I remember correctly they were caused by a change in the way the connection was created from the SQL Server SQLClient. Anyway, that’s already history, though since then I was wondering on whether is possible to use a programmatically created UDF to return the number of records for a given table sent as parameter. Since then I haven’t had the chance to test that, even if it seemed logically possible, but seeing Chris Skorlinski’s post and as last week I installed Visual Studio 2010 on my home computer, I said to myself that that’s a nice way to see the tool at work. Thought and done, but again not without problems, running in several small but time consuming issues.

So armed with some patience, I did a quick search on Microsoft site on how to use CLR scalar-valued functions under SQL Server 2008, running into this MSDN resource, which actually returns the record count using a static query. So I opened Visual Studio 2010 and created a new Database/SQL Server/Visual Basic SQL CLR Database Project named SQLServerProject1 (I forgot to give the project a meaningful name) and added a database reference to the AdventureWorks database using Windows Authentication.



Then in the new created project I’ve added a UDF to the project (Add/New Item/User Defined Function), naming the new created class as CLRLibrary, I replaced the existing function with the one from the MSDN article, added a new String input parameter called TableName, modified the query and changed UDF’s name to NumberRecords as per below piece of code:
 

Imports System 
Imports System.Data 
Imports System.Data.SqlClient 
Imports System.Data.SqlTypes 
Imports Microsoft.SqlServer.Server 
Imports System.Runtime.InteropServices 'added manually 
 

Partial Public Class UserDefinedFunctions 
SqlFunction(DataAccess:=DataAccessKind.Read)> _ 
Public Shared Function NumberRecords(ByVal TableName As String) As Integer 
Using conn As New SqlConnection("context connection=true") 
conn.Open() 
Dim cmd As New SqlCommand("SELECT COUNT(1) AS [NumberRecords] FROM " & TableName, conn) 
Return CType(cmd.ExecuteScalar(), Integer) 
End Using 
End Function 
End Class 

    In addition, I had to add also a reference to System.Runtime.InteropServices, and now the function was ready for testing so I built the solution; then I copied CLRLibrary’s Full Path in SQL Server Management Studio and attempted to follow the steps described in the MSDN article. Now it’s time for some troubleshooting fun… First I enabled the CLR integration configuration option by running the below script:

--enable CLR integration use master 
go sp_configure 'clr enabled', 1  

go 
reconfigure 
go 

       
    Using the master database I attempted to create a reference to the assembly, using the Full Path and the dll name.
 

CREATE ASSEMBLY SqlServerProject1 

FROM 'D:\\SqlServerProject1.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS 

   The next step was to declare the function:

CREATE FUNCTION dbo.NumberRecords(@table_name nvarchar(100)) 
RETURNS int  
AS  
EXTERNAL NAME SqlServerProject1.[SqlServerProject1.UserDefinedFunctions].NumberRecords 
GO  
 

    Actually is the final form of the DDL script, because initially I used only a combination of the Project, class and function name (e.g. SqlServerProject1.UserDefinedFunctions.NumberRecords), receiving the following error message:

Msg 6505, Level 16, State 2, Procedure NumberRecords, Line 1
Could not find Type 'UserDefinedFunctions' in assembly 'SqlServerProject1'.

    After a quick search on Google I found out from Eggheadcafe forum that the Namespace was missing from the declaration, and once I made this change the above statement worked without problems. The next step was naturally to test on whether the function is working:

SELECT dbo.NumberRecords('AdventureWorks.HumanResources.Department')  

And it worked! So it’s time to test it against a set of tables from AdventureWorks database:
 

SELECT s.name [schema_name]
   , T.name table_Name 
, dbo.NumberRecords('AdventureWorks.' + s.name + '.' + t.name) NumberRecords 
FROM AdventureWorks.sys.tables t 
      JOIN AdventureWorks.sys.schemas s 
        ON t.schema_id = S.schema_id  
ORDER BY s.name  
, T.name 

  Actually I missed some important thing from the above steps, somewhere while attempting to test the function or to register the assembly, I received the following error message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "NumberRecords": System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

   A post on MSDN SQL Server forum remembered me that I forgot to check on whether I’m using the 3.5 or 4.0 .Net Framework. So in the SQLServerProject1 project, from main menu/Project/SQLServerProject1 Properties/Compile/Advice Compile Options/Target Framework, I changed the value to .Net Framework 3.5. This change requires the project to be closed and reopened.

    In between the above steps I had also to check on whether the assembly was registered using the below script:

--assembly file details 
SELECT A.name [Assembly] 
, AF.name File_Path 
FROM sys.assemblies A 
     JOIN sys.assembly_files AF 
        ON A.assembly_id = AF.assembly_id 
WHERE A.is_user_defined=1 
--AND A.name='SqlServerProject1' 

    Also, if you’d like to remove the assembly from the database, you’ll have first to drop the function, and only then drop the assembly:

-- cleaning up
DROP FUNCTION NumberRecords 
DROP ASSEMBLY SqlServerProject1 

    It can be further discussed on whether this approach is acceptable as performance or not, on whether it can be misused, etc. Actually a nice misuse is the following statement which passes a WHERE constraint with the table name.

SELECT dbo.NumberRecords('AdventureWorks.HumanResources.Department WHERE Name LIKE ''P%''') 

    It could be created a second parameter for passing the WHERE constraint or, why not, pass the whole query as parameter, as long only a scalar is returned. I hope I haven’t forgot any step, I’m kind of mixing the facts because I attempted also to create the same function using a new created login, running into other type of issues that maybe deserve a second post.

Previous Post <<||>> Next Post

🔏MS Office: Excel for SQL Developers III (Creating a Stored Procedure from Table’s Metadata)

In a previous post I was showing a simple technique for using stored procedures for inserting/updating data within SQL Server 2005+. From a rough estimation I think that about 80% of the stored procedures built for this purpose could use the same template, without including additional business logic. Given the fact that databases store metadata about their objects, table’s metadata could be used to create such a stored procedure using VBA or more complex programming languages. SQL Server 2008 stores its table’s columns metadata in INFORMATION_SCHEMA.COLUMNS view, the required information for the Products created in the previous post could be get using the following query:

-- SQL Server 2008 Table/Column Metadata SELECT TABLE_SCHEMA SchemaName 
, TABLE_NAME TableName 
, COLUMN_NAME ColumnName 
, DATA_TYPE DataType 
, CHARACTER_MAXIMUM_LENGTH Length 
, IS_NULLABLE IsNullable 
, NUMERIC_PRECISION Precision 
, NUMERIC_SCALE NumericScale 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Products' 
ORDER BY TABLE_NAME 
, ORDINAL_POSITION 

The output of the above query could be easily copy-pasted into Excel or directly exported using the SQL Server Import and Export Wizard, the range with data (without headers) being passed as parameter to GetStoredProcedure macro created for this post together with stored procedure and table’s name.

 

Macro’s construction is based on the following presumptions:
1.   The first attribute in the target table is always table’s primary key.
2.   The stored procedure considers only simple data types though it can be extended to support further data types.
3.   Stored procedure's name and table's name contains also the schemas in which the respective objects are created.
4.   CreationDate, ModifiedDate, CreatedBy and ModifiedBy are reserved words for tracking the timestamp and the User who created/modified the record.
5.   The UserID  will be the last parameter in case the CreatedBy or ModifiedBy are in scope.

And here’s the macro GetStoredProcedure with its afferent Metadata Enumeration that maps the column order from the above query, and the GetDataType function that translates the metadata related to data type to the actual parameters’ data type: 

 

Enum Metadata 
  enmSchemaName = 1 
  enmTableName = 2 
  enmColumnName = 3 
  enmDataType = 4 
  enmLength = 5 
  enmIsNullable = 6 
  enmPrecision = 7 
  enmNumericScale = 8 
End Enum 

Function GetStoredProcedure(ByVal rng As Range, ByVal ProcedureName As String, ByVal TableName As String) As String 
'builds the code for a stored procedure based on table's metadata 
Dim parameters As String 
Dim columnName As String 
Dim parameterName As String 
Dim columnValue As String 
Dim insert As String 
Dim columns As String 
Dim update As String 
Dim indexRow As Integer 
Dim ID As String 
Dim insertUserID As Boolean 

insertUserID = False 

For index = 1 To rng.Rows.Count 
    If Len(Trim(Cells(index, Metadata.enmTableName).Value)) > 0 And Len(Trim(Cells(index, Metadata.enmColumnName))) Then 
       'translating column names 
       Select Case rng.Cells(index, Metadata.enmColumnName) 
       Case "CreationDate": 
            columnName = "" 
            columnValue = "GetDate()" 
            parameterName = "" 
       Case "ModifiedDate": 
            columnName = "ModifiedDate" 
            columnValue = "GetDate()" 
            parameterName = "" 
       Case "CreatedBy": 
            columnName = "" 
            columnValue = "@UserID" 
            parameterName = "" 
            insertUserID = True 
       Case "ModifiedBy": 
            columnName = Trim(rng.Cells(index, Metadata.enmColumnName)) 
            columnValue = "@UserID" 
            parameterName = "" 
            insertUserID = True 
       Case Else: 
            columnName = Trim(rng.Cells(index, Metadata.enmColumnName)) 
            columnValue = "@" & columnName 
            parameterName = "@" & columnName 
       End Select 
       
       If index = 1 Then 
            ID = columnName 
       Else 
            columns = columns & ", " & Trim(rng.Cells(index, Metadata.enmColumnName)) 
            insert = insert & ", " & columnValue 
            
            If Len(columnName) > 0 Then 
                 update = update & Space(9) & ", " & columnName & " = " & columnValue & "" & vbCrLf 
            End If 
       End If 
       
        If Len(parameterName) > 0 Then 
             parameters = parameters & ", " & parameterName & " " & GetDataType(Trim(rng.Cells(index, Metadata.enmDataType).Value), Trim(rng.Cells(index, Metadata.enmLength).Value), Trim(rng.Cells(index, Metadata.enmPrecision).Value), Trim(rng.Cells(index, Metadata.enmNumericScale).Value)) & vbCrLf 
         End If 
    End If 
Next 

If Len(columns) > 0 Then 
    columns = Right(columns, Len(columns) - 1) 
End If 
If Len(insert) > 0 Then 
    insert = Right(insert, Len(insert) - 1) 
End If 
If Len(update) > 0 Then 
    update = Right(update, Len(update) - 10) 
End If 
If Len(parameters) > 0 Then 
    parameters = Right(parameters, Len(parameters) - 1) & IIf(insertUserID, ", @UserID int" & vbCrLf, "") 
End If 

'building the stored procedure string 
GetStoredProcedure = "CREATE PROCEDURE " & ProcedureName & "(" & vbCrLf & parameters & ")" & vbCrLf & _ 
    "AS" & vbCrLf & _ 
    "BEGIN" & vbCrLf & _ 
    "BEGIN TRY" & vbCrLf & _ 
    "    IF ISNULL(@" & ID & ", 0) = 0" & vbCrLf & _ 
    "    BEGIN" & vbCrLf & _ 
    "    -- insert statement" & vbCrLf & _ 
    "       INSERT INTO " & TableName & " (" & columns & ")" & vbCrLf & _ 
    "       VALUES (" & insert & ")" & vbCrLf & _ 
    "       SET @" & ID & " = @@IDENTITY" & vbCrLf & _ 
    "    END" & vbCrLf & _ 
    "    ELSE" & vbCrLf & _ 
    "    BEGIN" & vbCrLf & _ 
    "        -- update statement" & vbCrLf & _ 
    "        UPDATE " & TableName & vbCrLf & _ 
    "        SET " & update & _ 
    "        WHERE " & ID & "= @" & ID & vbCrLf & _ 
    "    END" & vbCrLf & _ 
    "    SELECT @" & ID & vbCrLf & _ 
    "END TRY" & vbCrLf & _ 
    "BEGIN CATCH" & vbCrLf & _ 
    "   SELECT Cast(ERROR_NUMBER() as varchar(10)) + ':' + ERROR_MESSAGE()" & vbCrLf & _   
    "END CATCH" & vbCrLf & _ 
    "END" 

End Function 

Private Function GetDataType(ByVal DataType As String, ByVal Length As String, ByVal NumericPrecision As String, ByVal NumericScale As String) As String 
'translating data types 
Select Case DataType 
    Case "varchar": GetDataType = DataType & "(" & Length & ")" 
    Case "nvarchar": GetDataType = DataType & "(" & Length & ")" 
    Case "nchar": GetDataType = DataType & "(" & Length & ")" 
    Case "decimal": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case "numeric": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case "money": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case "smallmoney": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case Else: GetDataType = DataType 
End Select 

End Function 

Note:
As always, there is enough place for improvement, I just tried to exemplify the utility of such a function that could reduce considerably developers’ time for building such stored procedures.