Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Fetch T-SQL With Passed Parameter Value Using Stored Procedure

DZone's Guide to

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.

Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

Introduction

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.

Description

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.

Step 1

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  

Step 2

I entered some dummy data.

select * from Employee

Output

Step 3
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) 

Output

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.

Summary

Create procedure.
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.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
sql server ,database ,stored procedures ,t-sql

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}