European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2012 Hosting - HostForLIFE.eu :: Passing Table to a Function Parameter in SQL Server 2012

clock June 25, 2019 12:01 by author Peter

In 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) 



SQL Server 2016 Hosting - HostForLIFE.eu :: Sp_MSforeachtable Procedure in SQL Server

clock June 20, 2019 11:29 by author Peter
All 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
Procedure
This 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.



About HostForLIFE.eu

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.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in