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

Posted by DevAnand under Sql Server on 5/20/2010 | Views : 5880 | Status : [Member] | Replies : 4
Hi dude

I have been asked a question by my senior like

How will you create parameterized dynamic SQL Queries using Stored Procedure ?

I am expecting detailed explanation.

Thanks in advance

Devanand




Responses

Posted by: PandianS on: 5/20/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
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

DevAnand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 5/20/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down

e.g

Create Proc sp_Example

(
@Parameter1 int , --Parameters declared
@Parameter2 int
)
as

select * from my table
where name = @Parameter1 and lastname = @Parameter2


That is how you create it

Thank you for posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

DevAnand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: DevAnand on: 5/20/2010 [Member] Starter

Up
0
Down
Thanks for the great explanation and sample.

thank you
Devanand


DevAnand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Samanthajyesta on: 5/21/2010 [Member] Starter

Up
0
Down
Well , A dynamic stotred procedure is a Transact-sql statement or set of statements stored in a variable and executed using an SQL command. We use dynamic sql commands because static sql commands some times fails to meet our requirements .
We do this when we need to retrieve set of records based on different search parameters means to execute a different SELECT statements based on different WHERE clauses.
it can be done using two methods.

1.EXECUTE command.
When we use EXECUTE command the parameters should be converted to characters.
syntax:: EXECUTE(@SQLStatement)

2.sp _executesql statement statement.(supports parameter substitution).
when we use sp_executesql statement supports parametr substitution..and this is more preferrable than EXECUTE command.
syntax:: sp_executesql [@SQLStatement],[@ParameterDefinitionList],[@ParameterValueList].



http://www.dotnetfunda.com

DevAnand, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response