While working on autocomplete dropdown, i created a simple scalar function that replaces multiple spaces with one.
Very often we need to bulk export database objects and move them to another server. Manually we do this by right click on the database –> Tasks –> Generate Scripts… In this example I show how we can export Stored Procedures with T-SQL and SQLCMD. This model gives us the […]
We usually mask the credit card number in reports or end user data with asterisk (*). Here I show a scalar-valued function that masks the Credit Card Number.
In some scenarios like ETL or load data into a DB, we need to insert the data into a staging table, where all the columns are String data type and on the next step to cleanse and manipulate the data. I heard a discussion about the data type of the […]
When we create dynamic SQL code, we can add the parameters in two ways: Concatenate them into the dynamic code with + Use sp_executesql
In some situations a scheduled shrink of the log files is needed to release disk space. The script bellow can be scheduled in SQL Server Agent Job.
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 […]
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 […]