It looks like you've included a lot of SQL code snippets in your script. Here's a summary of what each section seems to do
Stored Procedure Definitions: Defines several stored procedures
such as GetStudentData, GetStudentsByDepartment, GetSensitiveData, and
GetStudentCount.These procedures seem to involve retrieving data from tables
based on certain conditions.
Execution of Stored Procedures: Executes the stored procedures defined above,
both with and without parameters.
String Manipulation Functions:
Demonstrates various string manipulation functions like LTRIM, RTRIM, LOWER,
UPPER, REVERSE, LEN, ASCII, LEFT, RIGHT, CHARINDEX, SUBSTRING, REPLICATE,
PATINDEX, REPLACE, and STUFF.These functions perform tasks like trimming
whitespace, changing case, reversing strings, finding substrings, etc.
Loops: Includes examples
of using loops (WHILE statement) to
iterate over numbers and characters in a string.
Table
Operations: Contains examples of creating
tables, inserting data into tables, and performing operations on table data
like counting occurrences of email domains.
.png)
sp_helptext 'GetStudentCount';
sp_depends 'GetStudentCount';
-- SQL
Server Example
CREATE PROCEDURE GetStudentData
AS
BEGIN
SELECT StudentID, StudentName, Salary
FROM Student;
END;
--
Execute the stored procedure
EXEC GetStudentData;
-- SQL
Server Example
CREATE PROCEDURE GetStudentsByDepartment
@DepartmentName NVARCHAR(50)
AS
BEGIN
SELECT StudentID, StudentName, Salary
FROM Students
WHERE Department =
@DepartmentName;
END;
--
Execute the stored procedure with a parameter
EXEC GetStudentsByDepartment @DepartmentName = 'IT';
CREATE PROCEDURE GetSensitiveData
WITH ENCRYPTION
AS
BEGIN
SELECT * FROM SensitiveTable;
END;
-- Drop a
stored procedure named GetStudentData
DROP PROCEDURE GetStudentData;
CREATE PROCEDURE GetStudentCount
@DepartmentName NVARCHAR(50),
@StudentCount INT OUTPUT
AS
BEGIN
SELECT @StudentCount = COUNT(*)
FROM Students
WHERE Department =
@DepartmentName;
END;
DECLARE @Count INT;
EXEC GetStudentCount @DepartmentName = 'IT', @StudentCount = @Count OUTPUT;
-- Now
@Count contains the output value
PRINT @Count;
-- SQL
Server Example
CREATE PROCEDURE GetStudentCount
@DepartmentName NVARCHAR(50),
@StudentCount INT OUTPUT
AS
BEGIN
SELECT @StudentCount = COUNT(*)
FROM Students
WHERE Department =
@DepartmentName;
END;
DECLARE @Count INT;
--
Execute the stored procedure and retrieve the output parameter
EXEC GetStudentCount @DepartmentName = 'IT', @StudentCount = @Count OUTPUT;
-- Print
the result
PRINT 'Student count in IT department: ' + CAST(@Count AS NVARCHAR(10));
SET @Count = 0;
-- Print
the result
SELECT CONCAT('Student count in IT department: ', @Count) AS Result;
-- SQL
Server Example
DECLARE @Count INT;
--
Declare and set initial value for @DepartmentName
DECLARE @DepartmentName NVARCHAR(50);
SET @DepartmentName = 'IT';
--
Execute the stored procedure and retrieve the output parameter
EXEC GetStudentCount @DepartmentName, @Count OUTPUT;
-- Use IF
statement to check the Student count
IF @Count > 0
PRINT 'There are Students in the IT
department.';
ELSE
PRINT 'No Students found in the IT
department.';
--
Example stored procedure with parameters
CREATE PROCEDURE YourStoredProcedureName
@Parameter1 datatype1,
@Parameter2 datatype2,
-- add more
parameters as needed
AS
BEGIN
-- Procedure
logic using parameters
-- For example:
SELECT *
FROM YourTable
WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
-- Add more
logic as needed
END;
CREATE PROCEDURE GetStudentsByDepartment
@DepartmentName NVARCHAR(50)
AS
BEGIN
SELECT StudentID, StudentName, Salary
FROM Students
WHERE Department =
@DepartmentName;
END;
DECLARE @Count INT;
--
Execute the stored procedure and retrieve the output parameter
EXEC GetStudentsByDepartment @DepartmentName = 'IT';
--
Example stored procedure with a result set
CREATE PROCEDURE GetStudentsByDepartment
@DepartmentName NVARCHAR(50)
AS
BEGIN
-- Select data
from the Students table based on the provided department
SELECT StudentID, StudentName, Salary
FROM Students
WHERE Department =
@DepartmentName;
-- Optionally,
you can use the RETURN statement for an integer return value
-- RETURN 0; --
Success
-- RETURN 1; --
Error
END;
--
Execute the stored procedure
EXEC GetStudentsByDepartment @DepartmentName = 'IT';
SELECT LTRIM('00012345') AS TrimmedString;
'12345'
SELECT LTRIM('
Hello, World!') AS TrimmedString;
'Hello,
World!'
SELECT RTRIM('Hello, World! ') AS TrimmedString;
'Hello,
World!'
SELECT RTRIM('00012345000') AS TrimmedString;
'00012345'
SELECT LOWER('Hello, World!') AS LowercaseString;
'hello,
world!'
SELECT UPPER('Hello, World!') AS UppercaseString;
'HELLO,
WORLD!'
SELECT REVERSE('Hello, World!') AS ReversedString;
'!dlroW
,olleH'
SELECT LEN('Hello, World!') AS StringLength;
12
SELECT ASCII('A') AS AsciiValue;
65
DECLARE @Number INT;
SET @Number = 1;
WHILE @Number <= 10
BEGIN
-- Your logic
here
PRINT 'Current Number: ' + CAST(@Number AS VARCHAR(10));
-- Increment
the number
SET @Number = @Number + 1;
END;
---
DECLARE @Number INT;
DECLARE @InputString NVARCHAR(100) = N'Hello';
SET @Number = 1;
WHILE @Number <= LEN(@InputString)
BEGIN
-- Your logic
here
DECLARE @CurrentChar NCHAR(1);
SET @CurrentChar = SUBSTRING(@InputString, @Number, 1);
PRINT 'Current Character: ' + @CurrentChar;
-- Increment
the number
SET @Number = @Number + 1;
END;
DECLARE @InputString NVARCHAR(100) = N'HelloWorld';
SELECT LEFT(@InputString, 5) AS Result;
Result
-----
Hello
DECLARE @InputString NVARCHAR(100) = N'HelloWorld';
SELECT RIGHT(@InputString, 5) AS Result;
Result
-----
World
DECLARE @FullString NVARCHAR(100) = N'Hello, World';
-- Find
the position of the substring 'World' in the full string
SELECT CHARINDEX('World', @FullString) AS Position;
DECLARE @FullString NVARCHAR(100) = N'Hello, World';
--
Extract a substring starting from position 8 with a length of 5
SELECT SUBSTRING(@FullString, 8, 5) AS ExtractedSubstring;
ExtractedSubstring
-------------------
World
DECLARE @FullString NVARCHAR(100) = 'email.com';
--
Extract a substring starting from position 1 with a length of 5
SELECT SUBSTRING(@FullString, 1, 5) AS ExtractedSubstring;
ExtractedSubstring
-------------------
email
DECLARE @FullString NVARCHAR(100) = 'A@example.com';
-- Find
the position of the '@' symbol in the email address
DECLARE @AtSymbolPosition INT = CHARINDEX('@', @FullString);
--
Extract the email ID (substring before the '@' symbol)
SELECT SUBSTRING(@FullString, 1, @AtSymbolPosition - 1) AS EmailID;
--
Assuming you have a table named 'Users' with an 'EmailAddress' column
SELECT SUBSTRING(EmailAddress, 1, CHARINDEX('@', EmailAddress) - 1) AS EmailID
FROM Users;
DECLARE @FullString NVARCHAR(100) = 'A@example.com';
--
Extract the email ID
SELECT SUBSTRING(@FullString, 1, CHARINDEX('@', @FullString) - 1) AS EmailID;
-- Sample
table creation
CREATE TABLE Emails (
EmailAddress NVARCHAR(255)
);
-- Sample
data insertion
INSERT INTO Emails (EmailAddress)
VALUES
('A@godday.com'),
('B@example.com'),
('C@gmail.com'),
('D@yahoo.com');
--
Extract email domain and count occurrences for each domain
SELECT
SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, LEN(EmailAddress)) AS EmailDomain,
COUNT(*) AS DomainCount
FROM
Emails
GROUP BY
SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, LEN(EmailAddress));
-- Repeat
the character 'X' 5 times
SELECT REPLICATE('X', 5) AS RepeatedString;
-- Repeat
the character 'X' 5 times
SELECT REPLICATE('X', 5) AS RepeatedString;
-- Pad
the string '123' with leading zeros to make it 8 characters long
SELECT REPLICATE('0', 8 - LEN('123')) + '123' AS PaddedString;
PaddedString
------------
00000123
PATINDEX('%AAA%', expression)
DECLARE @FullString NVARCHAR(100) = 'FGGGFGYU88 AAA YUYUYYTUTUTUTY';
-- Find
the position of the word 'AAA' in the full string
SELECT PATINDEX('%AAA%', @FullString) AS Position;
Position
--------
17
SELECT REPLACE(Email, '.in', '.com') AS UpdatedEmail
FROM Users;
UPDATE YourTableName
SET Email = REPLACE(Email, '.in', '.com');
DECLARE @FullString NVARCHAR(100) = 'Hello, World!';
--
Replace 'World' with 'Hello'
SELECT STUFF(@FullString, CHARINDEX('World', @FullString), LEN('World'), 'Hello') AS UpdatedString;
UpdatedString
-------------
Hello, Hello!