Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-21 : 07:26:18

Hi,

Is it possible to to use a dynamic sql statement into an another statement.

For ex;

declare @param1 varchar(10)
declare @Line1E varchar(4000)
declare @SQF2 varchar(4000)
declare @est varchar(4000)
declare @EAC varchar(4000)
set @param1='0211%'
If @param1 = '0211%'
begin
set @Line1E = 'Select left(ProjCategoryGroup.CategoryGroupID,2) as Grp, ProjCategoryGroup.CategoryGroupID+'+'''-'''+'+ProjCategoryGroup.Name as CAT, '' as EName, '

set @SQF2 = 'ProjForecastEmpl, ProjCategoryGroup where
Left(ProjForecastEmpl.CategoryId,6) = ProjCategoryGroup.CategoryGroupID and
ProjForecastEmpl.DataAreaId = ProjCategoryGroup.DataAreaId and'

end

set @est = @Line1E +'ProjForecastEmpl.Qty as Est, 0 as EAC, 0 as PYr, 0 as M01,
0 as M02, 0 as M03, 0 as M04, 0 as M05, 0 as M06, ", 0 as YTD From
'''+@SQF2+'''
ProjForecastEmpl.DataAreaId = '+'''cae'''+' and
ProjForecastEmpl.ProjId = '+'''100304'''+' and ModelId = '+'''1-Order'''
print @est
select @EAC = @Line1E +
'0 as Est, ProjForecastEmpl.Qty as EAC, 0 as PYr, 0 as M01,
0 as M02, 0 as M03, 0 as M04, 0 as M05, 0 as M06, "
0 as M07, 0 as M08, 0 as M09, 0 as M10, 0 as M11, 0 as M12, 0 as YTD From
'''+@SQF2+'''
ProjForecastEmpl.DataAreaId = '+'''cae'''+' and
ProjForecastEmpl.ProjId = '+'''100304'''+' and ModelId = '+'''EAC'''

select* from(@est union all @EAC)

on the above statement,the variable @Line1E and @SQF2 needs to be used for many other statement and make union all between all the above statement.i dont want repeat the line for each statement..so i want it to declare to a variable and use it as many times in any other query.

Thanks in advance for your kind reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 08:48:58

looks like overcomplicating the issue. Anyways, since we dont have any more visibility on problem you can try this

exec('select * from('+ @est + ' union all ' + @EAC + ')t')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-21 : 09:01:22

Thanks a lot visakh.I hope this works.The query seems to eb very complicated but what i wanted is to use the variable of the sql statement to another sql statement.rather than repeating all the times..but i think the solution which u gave should work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 09:09:45

if you can explain the bigger picture, somebody will be able to suggest you an easy alternate solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-24 : 09:14:49

Executing DDL and SCL Statements in PL/SQL

Only dynamic SQL can execute the following types of statements within PL/SQL program units:

Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

Session control language (SCL) statements such as ALTER SESSION and SET ROLE

The TABLE clause in the SELECT statement

The following native dynamic SQL example uses a SELECT statement with the TABLE clause.

Example 8-1 Using SELECT . . . TABLE in Dynamic SQL

-- Create an object t_emp and a datatype t_emplist as a table of type t_emp
CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/
-- Create a table with a nested table of type t_emplist
CREATE TABLE dept_new (id NUMBER, emps t_emplist)
NESTED TABLE emps STORE AS emp_table;
-- Populate the dept_new table with data
INSERT INTO dept_new VALUES
(
10,
t_emplist
(
t_emp(1, 'SCOTT'),
t_emp(2, 'BRUCE')
)
);
-- Write a PL/SQL block that queries table dept_new and nested table emps
-- SELECT ... FROM ... TABLE is not allowed in static SQL in PL/SQL
DECLARE
v_deptid NUMBER;
v_ename VARCHAR2(20);
BEGIN
EXECUTE IMMEDIATE 'SELECT d.id, e.name
FROM dept_new d, TABLE(d.emps) e
WHERE e.id = 1'
INTO v_deptid, v_ename;
END;
/
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#BJEHDACE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-25 : 04:41:37

Note that this forum is for SQL Server. Your suggestion will not work in SQL Server

Madhivanan

Failing to plan is Planning to fail

sindhu sarah
Starting Member

34 Posts

Posted - 2011-11-28 : 05:37:09

Thanks Visakh your solution worked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 07:17:32

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources