Can we pass sql query as parameter in stored procedure?

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
Can we pass sql query as parameter in stored procedure? Can we pass sql query as parameter in stored procedure? Reviewed by kamal kumar das on January 20, 2012 Rating: 5

No comments:

ads 728x90 B
Powered by Blogger.