Please explain How to create parameterized Dynamic Query in Stored procedure ?

Hi Devanand

Dynamic Query:
Building Query structure On the fly(The query structure will be binded at runtime). see the following scenario...

You have One table called : Tb_Sample1

IF OBJECT_ID('Tb_Sample1','U') IS NULL

CREATE TABLE Tb_Sample1

(

ID	INT IDENTITY,

Col1	VARCHAR(50),

Col2	VARCHAR(50)

)

GO

Depends on the scenario, You will know the scenario @ runtime only that which column you need to validate ?. In that case, you can not build query as Static as given below...

If you need to validate the column : Col1
SELECT * FROM Tb_Sample1 WHERE Col1 = 'XXXXXXX'

If you need to validate the column : Col2
SELECT * FROM Tb_Sample1 WHERE Col2 = 'XXXXXXX'

In our case , We have only Three columns.

Incase if you have 500 columns then what will you do ? How do u know which column you going to validate ?

So, you need a dynamic query based on the Parameter what you giving ? See the below sample...

Building Parameterized Dynamic Query:

INSERT Tb_Sample1(Col1,Col2) 

VALUES

('Aequea','SQL Server'),

('Salino','MySQL'),

('Calcalino','Oracle'),

('Setaceo','MSAccess'),

('Socaprio','SQL Server'),

('Alumino','MySQL'),

('Vitriolic','Teradata')

GO

IF OBJECT_ID('USP_DynamicResult','P') IS NOT NULL

DROP PROC USP_DynamicResult

GO

CREATE PROC USP_DynamicResult

(

@ColumnName	NVARCHAR(50),

@ColumnValue	NVARCHAR(50),

@Records	INT OUTPUT

)AS

BEGIN

SET NOCOUNT ON

	

DECLARE @SQL	    NVARCHAR(100)	,

              @Parameters   NVARCHAR(100)	= N'@lColumnValue NVARCHAR(50), @lRecords  INT OUTPUT'

						

SELECT   @SQL = N'SELECT * FROM Tb_Sample1 WHERE ' + @ColumnName + ' = @lColumnValue; SELECT @lRecords = @@ROWCOUNT',

@ColumnValue = '' + @ColumnValue + ''

	

EXEC SP_EXECUTESQL  @SQL,

		  @Parameters,

		  @lColumnValue = @ColumnValue, 

                                  @lRecords = @Records OUTPUT

	

END

GO

How to execute the Stored Procedure ?

If you need result based on Col1:

DECLARE @Records1 INT

EXEC USP_DynamicResult @ColumnName ='Col1', @ColumnValue='Setaceo', @Records= @Records1 OUTPUT

SELECT @Records1 [Record(s)]

If you need result based on Col2:

DECLARE @Records1 INT

EXEC USP_DynamicResult @ColumnName ='Col2', @ColumnValue='SQL Server', @Records= @Records1 OUTPUT

SELECT @Records1 [Record(s)]

So, Based one the Parameters, you can build query dynamically.

Here, I have used SP_EXECUTESQL system stored procedure for Dynamic query. For Dynamic query The System Stored Procedure is the Optimistic way...

I think Now you clear with Parameterized Dynamic query.

Note: The INSERT statement used as a "Table Value Constructor". If you using SQL Server 2008,2008 R2 , You can use this script 'AS IS', Otherwise, Please change only the INSERT script.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions