November 2, 2009 at 1:43 pm
Hi all,
I'm trying to understand a block of sql from an existing stored procedure.. Here is the code
create proc..
declare @ins nvarchar(10)
declare @prdinfo decimal(10,2)
declare @prdreps decimal(6,0)
...
begin try /* get data from prdsale*/
select * into #tmp from
openquery(DBSER1, 'SELECT * FROM USER1.prdsale WHERE PRDSC=''-1''')
INSERT INTO #tmp
exec ('SELECT * FROM USER1.prdsale WHERE PRDSC=?',@PRDOLD) at DBSER1
select
@ins=PR.PRDLC,
@PRDINFO=PR.PRDINFO,
@PRDREPS=case when @PRDREPS=0 then PR.PRDREPS else @PRDREPS end
from #tmp PR
drop table #tmp
END TRY
...
In the open query statement, is it like a dynamic sql? Also, to me it looks like all it is doing is just retreiving data from prdsale table. But could there be a reason to write into temp table first? Also, big doubt is- if it is writing all the fields from prdsale into tmp or just these 3 fields- INS,PRDINFO and PRDREPS...
Thanks for your help in advance
November 2, 2009 at 2:29 pm
The OPENQUERY statement allows you to execute SQL against a remote data source. In your case, the SELECT statement will be executed against the DBSER1 data source.
It looks like first OPENQUERY statement is used only to build out the temp table with the correct metadata from the source query. I'm guessing that you don't have a row in your table with PRDSC='-1'. So that query will build out an empty temp table.
The EXEC statement grabs the row with a PRDSC value equal to your variable value and inserts it into the temp table. Yes, it grabs all of the columns from the table.
The SELECT statement with the variable definitions grabs the row that the EXEC put into the temp table and populates the 3 variables with the corresponding column values.
By the looks of this, the proc is always looking for 1 row from the source table. It was written to allow for execution on a remote server. If the USER1.prdsale table was in the local SQL instance/database, it would have been easier (and more understandable) to not use the OPENQUERY syntax or to not use a temp table. It would have been just as easy to write it like this:
select
@ins=PR.PRDLC,
@PRDINFO=PR.PRDINFO,
@PRDREPS=case when @PRDREPS=0 then PR.PRDREPS else @PRDREPS end
from USER1.prdsale PR
WHERE PRDSC=@PRDOLD
November 2, 2009 at 5:54 pm
Thank you so much. It was more than helpful.
I've another sql query that I need help within the procedure..
...
declare @ACI varchar(5)
create table #tmp (Res nvarchar(5))
insert into #tmp
exec ('SELECT ACI FROM USER1.MAYR WHERE MA_CODE=''' + @mayrto + '''') at SER1
select @ACI =Res from #tmp
...
In the above select query, is the where clause simple as where ma_code=@mayrto? Not sure what the ''' and '''' are for?
Thank you
November 5, 2009 at 7:30 am
Please can someone help me...
declare @ACI varchar(5)
create table #tmp (Res nvarchar(5))
insert into #tmp
exec ('SELECT ACI FROM USER1.MAYR WHERE MA_CODE=''' + @mayrto + '''') at SER1
select @ACI =Res from #tmp
...
I'm converting this into Oracle procedure..Trying to understand what this block does.
Is the above select query the same as 'select ACI from user1.mayr where ma_code=@mayrto' ?
Is there a loop involved in the above block?
Thank you
November 5, 2009 at 8:16 am
In Oracle it's something like: select something from dual;
To see what the code is doing, you could print the string or "select" the string in SQL like this:
declare @mayrto varchar(5)
set @mayrto='hello'
select ('SELECT ACI FROM USER1.MAYR WHERE MA_CODE=''' + @mayrto + '''')
The output looks like:
SELECT ACI FROM USER1.MAYR WHERE MA_CODE='hello'
The additional '' '''' 's are to put the single ticks around the hello. SQL is funny about how many ticks it takes to make a single tick inside of subquerys. 😉
_______________________________________________________________________
Work smarter not harder.
November 5, 2009 at 8:26 am
Thanks Matt!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply