Let’s say that we have a SP with predefined parameters, i.e. @Parameter1 can hold values 1, 2 or 3; @Parameter2 can hold values of A, B, C or D and so on. This script creates all the possible combinations for the parameters of the SP and executes the SP as many times […]
T-SQL
31 posts
Very often we make changes to the design of an underlying objects, used by views. After we do this, we need to refresh the views to apply the changes. In this example we will: 1. Create View 2. Change the design of an underlying object 3. Refresh all the views […]
Working with multi-value parameters in SSRS is always fun. We need to pick a good delimiter, add additional values like “ALL” or “BLANKS” and many others. In the following few lines I explain a logics to add a multi-value parameter to SSRS with additional values (all) to not apply the […]
Data Warehouse is “a system used for reporting and data analysis”. In general it is built by dim (dimension) and fact tables (structure and data tables). Here I create a stored procedure to build Date Dimension. Now it builds only calendar data and will be expanded to include “fiscal” data […]
I built a function for reporting purposes that shows the duration in hh:mm:ss between two datetime columns. It can be used in a reporting tool to show the data in a log. The max difference in DATEDIFF() is 68 years. Quick and extended calculation (every year is a leap year): 68 years […]
Using a multi-value parameters in SSRS reports is giving the users the flexibility to filter on one or more values. In the following example they can filter on one or more products and on one or more customers. SSRS is passing the parameters to the stored procedure as joined strings. […]
An user-defined function to convert comma separated values (CSV) to an Array (table) is always needed. I wrote one that uses a multi-character delimiter and returns not/trimmed values. The string searches for the delimiter, inserts the left slice into the resulting table and cuts the left slice from the string. […]
A T-SQL script to export the definition of a table may be used in an automation. For example when I automate an import of a data into the database, I don’t create a staging table with NVARCHAR(MAX) columns, but export the structure of the destination table as a staging table.
In the development process and on my playground I need a cloned database to play safely and delete in the end.
While we create the database architecture, we decide to use the special value NULL or replace it with empty string or zero. Later on the development stage we manipulate the data and when we need to aggregate, we have to keep in mind the NULL or “blank” columns. The funny […]