SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 11/21/2011 :  07:26:18  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/21/2011 :  08:48:58  Show Profile  Reply with Quote
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/

Go to Top of Page

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 11/21/2011 :  09:01:22  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/21/2011 :  09:09:45  Show Profile  Reply with Quote
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/

Go to Top of Page

johntech
Yak Posting Veteran

Egypt
51 Posts

Posted - 11/24/2011 :  09:14:49  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 11/25/2011 :  04:41:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Note that this forum is for SQL Server. Your suggestion will not work in SQL Server

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 11/28/2011 :  05:37:09  Show Profile  Reply with Quote
Thanks Visakh your solution worked
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/28/2011 :  07:17:32  Show Profile  Reply with Quote
wc

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000