?
Solved

How to get a local variable filled from this statement

Posted on 2005-04-28
8
Medium Priority
?
724 Views
Last Modified: 2010-05-18
This kind of workign was for me the only one i think i could use to call a stored procedure, in a package on a oracle server from my sqlserver.

    SET @QUERY = 'SELECT * FROM OPENQUERY(KINEWEB, ''{CALL KINE_REP_ADMIN.SDW.X_Sales_ShowInfo('
                 + '''''' + @COMP_ID + ''''', '
                 + CAST(@FEAT_ID AS VARCHAR)+ ', '
                 + ' TO_DATE(''''' + CONVERT(VARCHAR, @SS_DATE, 103) + ''''', ''''DD/MM/YYYY'''') , '
                 + ' TO_DATE(''''' + CONVERT(VARCHAR, @SS_PROG_DATE, 103) + ''''', ''''DD/MM/YYYY'''') , '
                 + @PERF_ID + ', '
                 + CAST(@HALL_ID AS VARCHAR)+ ', '
                 + '''''' + RIGHT('00' + CAST(DATEPART(hh, @SS_DATE) AS VARCHAR), 2)
                          + RIGHT('00' + CAST(DATEPART(mi, @SS_DATE) AS VARCHAR), 2) + ''''', '
                 + '{resultset 20, Dum1, Dum2})}'')';
    EXEC (@QUERY);

But, ... i would like to het the value of this Dum1 and Dum2 so that i can use it in my sql-server sp.

using SET @QUERY = 'SELECT @X1 = DUM1, @X2 = DUM2 FROM OPENQUERY.... doesn't work.

Any suggestions ????
0
Comment
Question by:HLEBOEUF
  • 4
  • 2
  • 2
8 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 13884139
even with sp_executeSQL ?
please try

DECLARE @QUERY NVARCHAR(4000)  -- must be Nvarchar, not Varchar

SET @QUERY = N'SELECT @X1 = DUM1, @X2 = DUM2 FROM OPENQUERY.... '

exec sp_executeSQL @query, N'@X1 varchar(100) output, @X2 varchar(100) output', @X1 out, @X2 out

select @X1 as XA? @X2 as X2

the second parameter of the sp_executesql sp is the definition fo the variables shared between the calling environment and the dynamic sql "child" environment.
the datatypes must be consistent in both context (same datatype and size) since the parameters are passed by reference (pointers)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13884143
oops typo, last line should be

select @X1 as X1, @X2 as X2
0
 
LVL 2

Expert Comment

by:PErdu
ID: 13884170
declare @x1 as YOURSQL TYPE, @X2 as YOURSQLTYPE


SELECT @X1 = DUM1, @X2 = DUM2 FROM OPENQUERY will work

Now if you have something like

sp_execute_sql 'SELECT @X1 = DUM1, @X2 = DUM2 FROM OPENQUERY '+ @sql
where @sql is a dynamic where; order by... clause it won't...

Then you may maybe have to pass thru a temporary table and query this newly created table like ...
  SET @QUERY = 'INSERT INTO #tmp_openquery SELECT * FROM OPENQUERY(KINEWEB, ''{CALL KINE_REP_ADMIN.SDW.X_Sales_ShowInfo('
                 + '''''' + @COMP_ID + ''''', '
                 + CAST(@FEAT_ID AS VARCHAR)+ ', '
                 + ' TO_DATE(''''' + CONVERT(VARCHAR, @SS_DATE, 103) + ''''', ''''DD/MM/YYYY'''') , '
                 + ' TO_DATE(''''' + CONVERT(VARCHAR, @SS_PROG_DATE, 103) + ''''', ''''DD/MM/YYYY'''') , '
                 + @PERF_ID + ', '
                 + CAST(@HALL_ID AS VARCHAR)+ ', '
                 + '''''' + RIGHT('00' + CAST(DATEPART(hh, @SS_DATE) AS VARCHAR), 2)
                          + RIGHT('00' + CAST(DATEPART(mi, @SS_DATE) AS VARCHAR), 2) + ''''', '
                 + '{resultset 20, Dum1, Dum2})}'')';
    sp_execute_sql @QUERY

SELECT @X1 = DUM1, @X2 = DUM2 FROM #TMP_OPENQUERY
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 2

Expert Comment

by:PErdu
ID: 13884176
Well Hilaire seems you learned me something !!!
didn't ever find the way to get output parameters from sp_execute_sql

0
 

Author Comment

by:HLEBOEUF
ID: 13884212
TO Hilaire

DECLARE @QUERY NVARCHAR(4000);
DECLARE @X1 INT;
DECLARE @X2 VARCHAR(200);
SET @QUERY = N'SELECT * FROM OPENQUERY(KINEWEB, ''{CALL KINE_REP_ADMIN.SDW.X_SALES_SHOWINFO(''''KLOM'''', 45100,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') ,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') , 2, 13, ''''1430'''', {RESULTSET 20, DUM1, DUM2})}'')';
PRINT @QUERY;
EXEC SP_EXECUTESQL @QUERY, N'@X1 INT OUTPUT, @X2 VARCHAR(100) OUTPUT', @X1 OUT, @X2 OUT
--EXEC SP_EXECUTESQL @QUERY
SELECT @X1 AS X1, @X2 AS X2

Works but results in a first result of Dum1 and Dum2 with values, the second select results in both Null Values !!

TO PErdu (1)


The SELECT @X1 = DUM1 ..

Doesn't work, variable unknown in the Select statement (Separate Work-envrironment ???)


TO PErdu (2)


DECLARE @QUERY NVARCHAR(4000);
DECLARE @X1 INT;
DECLARE @X2 VARCHAR(200);
SET @QUERY = N'INSERT INTO #Oeps SELECT * FROM OPENQUERY(KINEWEB, ''{CALL KINE_REP_ADMIN.SDW.X_SALES_SHOWINFO(''''KLOM'''', 45100,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') ,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') , 2, 13, ''''1430'''', {RESULTSET 20, DUM1, DUM2})}'')';
PRINT @QUERY;
EXEC SP_EXECUTESQL @QUERY
SELECT * FROM #Oeps

Doesn't work, the select * from #Oeps results into a Invalid Object Name (i think the temp storage is in a seperate work-environment).
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 13884723
Please try

DECLARE @QUERY NVARCHAR(4000);
DECLARE @X1 INT;
DECLARE @X2 VARCHAR(200);
SET @QUERY = N'SELECT @X1=Dum1,  @X2=Dum2 FROM OPENQUERY(KINEWEB, ''{CALL KINE_REP_ADMIN.SDW.X_SALES_SHOWINFO(''''KLOM'''', 45100,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') ,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') , 2, 13, ''''1430'''', {RESULTSET 20, DUM1, DUM2})}'')';
PRINT @QUERY;
EXEC SP_EXECUTESQL @QUERY, N'@X1 INT OUTPUT, @X2 VARCHAR(100) OUTPUT', @X1 OUT, @X2 OUT
--EXEC SP_EXECUTESQL @QUERY
SELECT @X1 AS X1, @X2 AS X2
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 13884732
Oops, you need consistent datatypes (changed varchar(100) to varchar(200) to fit the declare statement)

DECLARE @QUERY NVARCHAR(4000);
DECLARE @X1 INT;
DECLARE @X2 VARCHAR(200);
SET @QUERY = N'SELECT @X1=Dum1,  @X2=Dum2 FROM OPENQUERY(KINEWEB, ''{CALL KINE_REP_ADMIN.SDW.X_SALES_SHOWINFO(''''KLOM'''', 45100,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') ,  TO_DATE(''''28/04/2005'''', ''''DD/MM/YYYY'''') , 2, 13, ''''1430'''', {RESULTSET 20, DUM1, DUM2})}'')';
PRINT @QUERY;
EXEC SP_EXECUTESQL @QUERY, N'@X1 INT OUTPUT, @X2 VARCHAR(200) OUTPUT', @X1 OUT, @X2 OUT
--EXEC SP_EXECUTESQL @QUERY
SELECT @X1 AS X1, @X2 AS X2
0
 

Author Comment

by:HLEBOEUF
ID: 13885201
Great, Thx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question