Dyamic SQL Queries

Please Give some examples for Dyamic SQL Queries

[B]Question asked by visitor LAKSHMI[/b]

Hi,

Following is one example of dynamic query

Declare @TableName varchar(128), @ColumnList varchar(1000)
Declare @SQL varchar(1000)

set @TableName = 'authors'

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE Ordinal_position <= 10
and table_name = @TableName

Set @SQL = 'Select ' + @ColumnList + ' from ' + @TableName

IN oracle
& is used for dynamic sub
eg SELECT * FROM table
WHERE last_name=&name

u give some problem corresponding dynamic sql to solve.

[FONT="Verdana"][SIZE="1"]DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;
[/SIZE][/FONT]

Check with sp_executesql help in SQL Server Help

sorry i don't know sql server but if u want it in oracle i can do that.

Sorry Bhaski. I don't have idea in ORACLE