June 25, 2019 12:01 by
PeterIn this article, I described how to pass a table to a function parameter in SQL Server. In this article, you create a table, student, then create a user-defined table type and pass the table type as a parameter to a function. So let's have a look at a practical example of how to pass a table as a function parameter in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Here is how to implement passing a user-defined table type to a function.
1. Create a Student Table in SQL Server
Create a table named Student.
CREATE TABLE [dbo].[Student]
(
[StudentID] [int] NULL,
[StudentName] [varchar](30) NULL,
[StudentFees] [int] NULL
)
2. Create a User-Defined Table Type in SQL Server
Now create a user-defined table type to be used as a table-valued parameter in the function.
CREATE TYPE dbo.StudentType AS TABLE
(
[StudentID] [int] ,
[StudentName] [varchar](30) ,
[StudentFees] [int]
)
Now Press F8 to see the created type in the Object Explorer.
Database->Programmability->Types->User Define Table Types
3. Creating a Function in SQL Server
Now create the StudentDetailFunctionFunction. This function will accept a table-valued parameter.
READONLY keyword - This keyword is required to declare a table-valued parameter.
ALTER FUNCTION StudentDetailFunction( @StudentDetail dbo.StudentType READONLY )
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Studentname VARCHAR(50)
SELECT @Studentname= StudentName FROM @StudentDetail
RETURN @Studentname
END
4. Execute the SQL Server Function
Now you can declare a variable @StudentVariable which contains the value of the table columns.
DECLARE @StudentVariable AS StudentType
INSERT INTO @StudentVariable(StudentName) VALUES('Peter')
SELECT dbo.StudentDetailFunction(@StudentVariable)
June 20, 2019 11:29 by
PeterAll versions of SQL Server have undocumented Stored Procedures or functions. This may be because those Stored Procedures or functions are used by Microsoft internally. This type of Stored Procedure or function (undocumented) can be any without any notification. The "sp_MSforeachtable" Stored Procedure comes with SQL Server, but it is not documented in MSDN. This Stored Procedure could be found in the Master database. The Stored Procedure "sp_MSforeachtable" allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or number of various T-SQL commands against every table in the database.
sp_MSforeachtable Syntax
sp_MSforeachtable [ @command1 = ] 'command1' [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]
Parameter
| Parameter |
Description |
| @command1 |
It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000). |
| @replacechar |
It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a "?". |
| @command2 |
@command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute. |
| @command3 |
| @whereand |
This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000). |
| @precommand |
This command is to be run before processing any table. Its data type is nvarchar(2000). |
| @postcommand |
This command is to be run after the processing of all the tables. Its data type is nvarchar(2000). |
Definition of sp_MSforeachtable procedure in SQL Server
CREATE PROCEDURE sys.sp_MSforeachtable
@command1 NVARCHAR(2000),
@replacechar NCHAR(1) = N'?',
@command2 NVARCHAR(2000) = null,
@command3 NVARCHAR(2000) = null,
@whereand NVARCHAR(2000) = null,
@precommand NVARCHAR(2000) = null,
@postcommand NVARCHAR(2000) = null
AS
-- This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set
-- @precommand and @postcommand may be used to force a single result set via a temp table.
-- Preprocessor won't replace within quotes so have to use STR().
DECLARE @mscat NVARCHAR(12)
SELECT @mscat = LTRIM(STR(CONVERT(INT, 0x0002)))
IF (@precommand is not null)
EXEC(@precommand)
-- Create the SELECT
EXEC(N'DECLARE hCForEachTable cursor global for SELECT ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
syso.object_id '
+ N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
+ @whereand)
DECLARE @retval INT
SELECT @retval = @@error
IF (@retval = 0)
EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
IF (@retval = 0 and @postcommand is not null)
EXEC(@postcommand)
RETURN @retval
The following script helps us to list all the tables of the "TestDb" database.
Example script
Use Testdb
exec sp_MSforeachtable 'print "?"'
Another example. The following script helps us to determine the space used and allocated for every table in the database.
Example script
Use Testdb
exec sp_MSforeachtable 'EXECUTE sp_spaceused [?];'
Common uses of sp_MSforeachtable Stored
ProcedureThis stored produce may be used for the following purposes.
- To get the size of all the tables in the database
- To rebuild all indexes of all the tables in the database
- Disable all constraints and triggers of all the tables in the database
- Delete all the data from all the tables in the database
- To RESEED all tables to 0
- To get the Number of Rows in all tables in a database
- Update the statistics of all the tables in a database
- Reclaim space from dropped variable-length columns in tables or indexed views of the database
These undocumented Stored Procedures can be used if we want to do the same operation on each table of any database. Please note that Microsoft may change the functionality and definition of this Stored Procedure at any time.
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.