Fetch T-SQL With Passed Parameter Value Using Stored Procedure
This tutorial explains how to fetch T-SQL with passed parameter value using stored procedure.
Join the DZone community and get the full member experience.Join For Free
Using "sys.objects," we can get a list of tables and stored Procedures created in the Database. SQL Server now supports table-valued parameters, which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API. Using SYSOBJECTS and SYSDEPENDS, we can get all the tables, stored procedures, and other database object-related information.
You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects. It contains a row for each user-defined, schema-scoped object that is created within a database. sys.objects does not show DDL triggers because they are not schema-scoped. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. It was not possible to pass a table variable as a parameter to a stored procedure in old versions of SQL Server.
Create one table named Employee.
The table script is given below.
CREATE TABLE [dbo].[Employee]( [Name] [nvarchar](50) NULL, [Address] [nvarchar](50) NULL, [Age] [int] NULL, [ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
I entered some dummy data.
select * from Employee
Create one stored procedure named Sp_EmployeeFullQuery.
Create procedure Sp_EmployeeFullQuery ( @ID VARCHAR(200) ) as begin DECLARE @FullQuery NVARCHAR(MAX) set nocount on; SET @FullQuery='select * from Employee where Employee.ID=' + CAST(@ID AS VARCHAR) + '' end PRINT @FullQuery EXEC (@FullQuery)
In this procedure, I defined one parameter called @ID.
Declare one variable @FullQuery.
DECLARE @FullQuery NVARCHAR(MAX)
Now, put all SQL query inside @FullQuery variable.
SET @FullQuery='select * from Employee where Employee.ID=' + CAST(@ID AS VARCHAR) + ''
At last, whatever parameter value is passed, this will show through @FullQuery variable by using the last two parts.
PRINT @FullQuery EXEC (@FullQuery)
exec Sp_EmployeeFullQuery 1
Here, 1 means AutoGenerate ID column value, which will show the related column data by filtering this ID value.
You will get all the details of SQL query with the already passed parameter value.
Using primary key, autogenerate ID column value showing the records.
Also, generate SQL query, which is defined in a stored procedure by passing parameter value.
Opinions expressed by DZone contributors are their own.