Yes, we can pass sql query as parameter in stored procedure.
Example:
STEPS:
1. Create a table called employee.
Script-
CREATE TABLE [dbo].[employee]
(
[Id] [int] IDENTITY(1,1) NOT NULL primary key,
[first_name] [dbo].[first_name] NOT NULL,
[last_name] [varchar](50) NULL
)
2. Insert some data in employee table
INSERT INTO Employee VALUES ('FirstName_A','LastName_AA');
INSERT INTO Employee VALUES ('FirstName_B','LastName_BB');
INSERT INTO Employee VALUES ('FirstName_C','LastName_CC');
INSERT INTO Employee VALUES ('FirstName_D','LastName_DD');
INSERT INTO Employee VALUES ('FirstName_E','LastName_EE');
3. Create one stored procedure name sp_test.
CREATE PROCEDURE [dbo].[sp_test]
@txt nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT @txt
END
4. Now Run following scripts.
DECLARE @txt nvarchar(100)
SET @txt='select first_name from employee'
EXEC sp_test @txt
Example:
STEPS:
1. Create a table called employee.
Script-
CREATE TABLE [dbo].[employee]
(
[Id] [int] IDENTITY(1,1) NOT NULL primary key,
[first_name] [dbo].[first_name] NOT NULL,
[last_name] [varchar](50) NULL
)
2. Insert some data in employee table
INSERT INTO Employee VALUES ('FirstName_A','LastName_AA');
INSERT INTO Employee VALUES ('FirstName_B','LastName_BB');
INSERT INTO Employee VALUES ('FirstName_C','LastName_CC');
INSERT INTO Employee VALUES ('FirstName_D','LastName_DD');
INSERT INTO Employee VALUES ('FirstName_E','LastName_EE');
3. Create one stored procedure name sp_test.
CREATE PROCEDURE [dbo].[sp_test]
@txt nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT @txt
END
4. Now Run following scripts.
DECLARE @txt nvarchar(100)
SET @txt='select first_name from employee'
EXEC sp_test @txt
Can we pass sql query as parameter in stored procedure?
Reviewed by kamal kumar das
on
January 20, 2012
Rating:
No comments: