SQL 2014 How to loop without a cursor

I want to try to optimize some code by placing combining a call to a stored procedure with a SQL select statement.
The stored procedure is called taSopLineIvcInsert.  I have a temporary dataset stored in SOPLINES and SOPHEADR. I am passing down the order number to another stored procedure and I want to select all the lines that belong to this order. As each line is found I then want to call the stored procedure taSopLineIvcInsert that will insert each record into the final production database. I want to do this without using a cursor. How can I restructure the attached stored procedure to accomplish this?
SQL_Loop.sql
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You need to post the source code for taSopLineIvcInsert too,
0
rwheeler23Author Commented:
That is not possible. It is encrypted by Microsoft.
0
Aneesh RetnakaranDatabase AdministratorCommented:
in that case you need to loop using a cursor
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rwheeler23Author Commented:
Something simple like select @rc= count(*) from mytable where whatever.
Then
@I=0
while ( @I < rc )
begin
tasopIvcLineinsert
@I=@I + 1
end
0
Mark WillsTopic AdvisorCommented:
Cannot see any cursor definition....

Assume that the proc taSopLineIvcInsert does the actual insert/updates but does it do anything else ?

What are you wanting to achieve ? Bypassing 'exec taSopLineIvcInsert' with you own version ? or just a different approach to executing that encrypted) proc ?

If you have to use 'exec taSopLineIvcInsert' then it seems to be row by row anyway and a cursor is a very good way of executing row-by-row

A 'WHILE' loop is not really any different...
0
rwheeler23Author Commented:
taSopLineIvcInsert inserts records into the production database. I am reading records from one database after they have been processed. The source database is contains a very basic sales order database with header and detail records. The production database receives detailed sales orders inserted by the taSopIvcInert sp. Much goes on inside that sp( sales tax, data validation, inventory checks, etc.).  I was thinking something like this:

DECLARE @i int
DECLARE @RC INT = 0

SELECT @I=COUNT(*) FROM SOPLINES WHERE SOPNUMBER=@SOPNUMBER
while @RC < @I
begin
    set @RC = @RC + 1

    tsSopIvcInsert(all the parameters)

end
0
Nitin SontakkeDeveloperCommented:
I may not be able to post the entire code, however, will just give you a hint.

You can do something as follows:

declare @dynamicSQL nvarchar(max) = ''
select @dynamicSQL += 'execute taSopLineIvcInsert ''' + H.DOCTYPE_SOPH + ''', ' + ltrim(str(H.SOPNUMBE_SOPH)) + ...
FROM [POWMATQP]..[SOPLINES] L
       INNER JOIN [POWMATQP]..[SOPHEADR] H ON L.SOPNUMBE_SOPL=H.SOPNUMBE_SOPH AND L.DOCTYPE_SOPL=H.DOCTYPE_SOPH
       WHERE H.SOPNUMBE_SOPH=@I_SOPNUMBE AND H.DOCTYPE_SOPH=@I_SOPTYPE

execute sp_executeSQL @dynamicSQL

Open in new window


Enclose this in your try...catch...block as usual. Make sure you take care of NULLs correctly as "isnull([columnname], 'NULL')".

Remember that the dynamic SQLs execute in separate connection context than the one in which they are executed.
0
rwheeler23Author Commented:
Interesting, I see what you mean by using dynamics sql. Otherwise what I just noticed is that if the order has N lines on it what actually gets inserted is the last line N times.  I have some  typing to do to get this set up. I will let you know how it goes.
0
Nitin SontakkeDeveloperCommented:
Yet another thing. You can have a try catch block inside the dynamic sql itself. In your case you will also need to declare two output variables as required by the stored procedure. You can develop the script in such a way that the execution aborts if any of the call returns errors.

I have been using this technique for long and haven't failed me so far. Give it a go. No cursor required and yet the essentially the same outcome.
0
rwheeler23Author Commented:
I am going blind trying to set up where the apostrophes go and don't go. Some of these fields are numbers as well as dates and strings. I will keep plodding along.
0
Nitin SontakkeDeveloperCommented:
At first, don't put it in a variable. Just select the whole string and also make sure that your WHERE clause returns just one record. Keep pressing F5 and parse it visually. Once done, then fix other minor issues and NULLs. Enclose dates in single quote and put them in universally neutral format such as '2017-12-22' or '20171222', either is fine in quotes.
0
rwheeler23Author Commented:
Got it. I have to include all the variable names as these sp's are very touchy and it took me a long time to get them to work so I want to send down exactly what I was sending down before. I am about half-way done. Thanks for your tips. Nulls will not be an issue because the source table does not allow nulls.
0
rwheeler23Author Commented:
I have it building this statement but when I try to execute this I am greeted with a message about parameter 'handle is expected of type int.
I do not have any parameters with that name.   Could it have something to do with the last two output parameters?

SELECT @dynamicSQL += 'EXECUTE taSopLineIvcInsert @I_vSOPTYPE=' + LTRIM(RTRIM(CONVERT(CHAR(10),H.DOCTYPE_SOPH))) + ',@I_vSOPNUMBE=''' + LTRIM(RTRIM(H.SOPNUMBE_SOPH)) +  ''',@I_vCUSTNMBR=''' +
            LTRIM(RTRIM(CUSTNUMB_SOPH)) + ''',@I_vDOCDATE=''' + CONVERT(CHAR(10),DOCDATE_SOPH,111) + ''',@I_vLOCNCODE=''' + LTRIM(RTRIM(LOCNCODE_SOPH)) + ''',@I_vITEMNMBR=''' + LTRIM(RTRIM(ITEMNUMB_SOPL)) +
            ''',@I_vUNITPRCE=' + LTRIM(RTRIM(CONVERT(CHAR(10),CAST(UNITPRCE_SOPL AS NUMERIC(10,2))))) + ',@I_vQUANTITY=' + '0' + ',@I_vPRCLEVEL=''' + LTRIM(RTRIM(@I_PRCLEVEL)) +
            ''',@I_vQTYTBAOR=' + LTRIM(RTRIM(CONVERT(CHAR(10),CAST(CUST_QTY_SOPL AS NUMERIC(10,2))))) + ',@I_vPRSTADCD=''' + LTRIM(RTRIM(SHIP_TO_CODE_SOPH)) +
            ''',@I_vSHIPMTHD=''' + LTRIM(RTRIM(SHIPMTHD_SOPH)) + ''',@I_vUpdateIfExists=' + LTRIM(RTRIM(CONVERT(CHAR(10),@I_UpdateIfExists))) + ',@I_vDEFPRICING=' + LTRIM(RTRIM(CONVERT(CHAR(10),@I_DEFPRICING))) +
            ',@I_vDEFEXTPRICE=' + LTRIM(RTRIM(CONVERT(CHAR(10),@I_DEFEXTPRICE))) + ',@I_vCURNCYID=''' + LTRIM(RTRIM(@I_CURNCYID)) + ''',@I_vUOFM=''' + LTRIM(RTRIM(UOFM_SOPL)) +
            ''',@O_iErrorState=' + LTRIM(RTRIM(CONVERT(CHAR(10),@O_ErrorState))) + ' OUTPUT, @oErrString=' + LTRIM(RTRIM(@O_ErrorString)) + ' OUTPUT ' +
            ' FROM SOPLINES L INNER JOIN SOPHEADR H ON L.SOPNUMBE_SOPL=H.SOPNUMBE_SOPH AND L.DOCTYPE_SOPL=H.DOCTYPE_SOPH WHERE SOPNUMBE_SOPL='''+LTRIM(RTRIM(H.SOPNUMBE_SOPH)) +
            ''' AND DOCTYPE_SOPL=' + LTRIM(RTRIM(CONVERT(CHAR(10),H.DOCTYPE_SOPH))) + ' AND MARK_TO_ORDER_SOPL=1 and SOP_XFER_TO_GP_SOPL=0'
            FROM [POWMATQP].[dbo].[SOPLINES] L
            INNER JOIN [POWMATQP].[dbo].[SOPHEADR] H ON L.SOPNUMBE_SOPL=H.SOPNUMBE_SOPH AND L.DOCTYPE_SOPL=H.DOCTYPE_SOPH
            WHERE SOPNUMBE_SOPL=@I_SOPNUMBE AND DOCTYPE_SOPL=@I_SOPTYPE AND MARK_TO_ORDER_SOPL=1 and SOP_XFER_TO_GP_SOPL=0
0
rwheeler23Author Commented:
When I copy the results out of SQL Profiler I get the exact same message. Something is not being built correctly.  I have not been successful in getting to view the final SQL statement sent down.
0
Nitin SontakkeDeveloperCommented:
Few things to note:

1/ If you pass the parameters exactly in the order as they are declared in the stored procedure being called, there is no need to follow the syntax @parameter_name = 'value'. This syntax is provided exclusively for the purpose of passing parameters in any arbitrary order and bypassing few parameters altogether. Removing parameter names from query string will have two effects. First, your query building query will be lot simple to write, look and maintain. Second, the resulting query string will be much smaller in length.

2/ You cannot pass value to a output parameter. You will need to pass the placeholder variables. Which is what i stated few posts back. Essentially, you will need to declare these variables in the script itself. As in:

declare @dynamicSQL nvarchar(max) = '
declare @O_ErrorState [integer]
declare @ErrorString [varchar](100)
'

Open in new window


And while building string use them as is. As in:

... + '@O_ErrorState OUTPUT, @ErrorString OUTPUT'

Hope you get the point.

3/ If you convert to VarChar instead of Char, LTrim and RTrim is probably not required.

4/ You cannot have LTrim and RTrim on a numeric column, it will give error. In case you are doing that mistake. I have no way of knowing your data types.

5/ All you have to do is just print the variable with the help of select @dynamicSQL. No need to go to profiler. Select will show only first few characters of the string. In SSMS, go to Tools -> Options -> Query Results -> SQL Server -> Results to Text and change 'Maximum number of characters displayed in each column' number to 8192 (8k) which is max allowed. If you wish to view further you could...

select substring(@dynamicSQL, 8000, 8000)  -- and so on. Keep on increasing second parameter by 8K each time.

Open in new window


Hope it helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
My apologies, I have lived with this database for so long my mind is poisoned by how Microsoft set it up 20 years ago. There are no varchars only chars. The stored procedures I have to used are black boxes.  Since they are encrypted I have no way of telling the order of the variables.   Can you please tell me why query returns blank? If I run it with values it does return the proper 3 records.
TestSQL.sql
0
Nitin SontakkeDeveloperCommented:
You seem to have removed output parameters altogether which does look good.

Make sure that none of the values are NULL from db with identical WHERE clause.

I showed you substring function ONLY IF you wish to see a part of @dynamicSQL which is longer than 8000 chars. Won't work if it is less than 8000 chars. PRINT statement will give output in separate tab (messages) not the results tab.

Have you already tried sp_help with the stored procedure? Have you already tried select * from sys.parameters or sys.colums?
0
rwheeler23Author Commented:
I got it now and can see the string. Here is my original version calling the sp. As you can see the output parameters have to appear on each call otherwise it errors out. I also see this with sp_help. I am thinking modifying your initial declaration of dynamicSQL is the way to go.

      exec taSopLineIvcInsert
      @I_vSOPTYPE=@I_SOPTYPE,
      @I_vSOPNUMBE=@I_SOPNUMBE,
      @I_vCUSTNMBR=@I_CUSTNMBR,
      @I_vDOCDATE=@I_DOCDATE,
      @I_vLOCNCODE=@I_LOCNCODE,
      @I_vITEMNMBR=@I_ITEMNMBR,
      @I_vUNITPRCE=@I_UNITPRCE,
      @I_vQUANTITY=@I_QUANTITY,
      @I_vPRCLEVEL=@I_PRCLEVEL,
      @I_vQTYTBAOR=@I_QTYTBAOR,
      @I_vPRSTADCD=@I_PRSTADCD,
      @I_vSHIPMTHD=@I_SHIPMTHD,
      @I_vUpdateIfExists=@I_UpdateIfExists,
      @I_vDEFPRICING = @I_DEFPRICING,
      @I_vDEFEXTPRICE=@I_DEFEXTPRICE,
      @I_vCURNCYID=@I_CURNCYID,
      @I_vUOFM = @I_UOFM,
      @O_iErrorState = @O_ErrorState OUTPUT,
      @oErrString = @O_ErrorString OUTPUT
0
Nitin SontakkeDeveloperCommented:
Of course. I never said my original code is perfect. I said that I will give you a hint.

In the dynamicSQL there will NOT be any right hand side variables, except two OUTPUT parameters. Instead, there will be literal values as in...again just giving you hint.

exec taSopLineIvcInsert @I_vSOPTYPE = 'H',  @I_vSOPNUMBE = 5, @I_vCUSTNMBR = 1069, ...@O_iErrorState = @O_ErrorState OUTPUT,       @oErrString = @O_ErrorString OUTPUT

Open in new window


I still think that you should try omitting parameter names and try once and see data gets populated properly. In my opinion, it's an excess baggage being carried over.
0
rwheeler23Author Commented:
Here is the final product. I have not tested it with a 500 line order but that will happen real soon. Thanks for all of your help. It definitely got me going in the right direction. Dynamics SQL statements were the key to this project.
FINAL_PRODUCT.SQL
0
Nitin SontakkeDeveloperCommented:
Yes. That looks lot better.

Once again, the SQL parameter of sp_executeSQL executes within it's own connection context which is different than the one in which sp_executeSQL is being executed.

It means that the execution of the dynamic SQL is NOT happening within the begin tran...rollback tran...commit tran you stated in your code.

Please get this cleared very well.

In simplest (possible) term I can explain, the sp_executeSQL starts it own connection context, executes the supplied sql and terminates that connection. That connection is oblivious of the connection in which your code is executing.
0
rwheeler23Author Commented:
So in other words, it is in a world of its own?  It would not run otherwise so that is why it is the way it is. I would keep getting that handle expecting type int error message. If there is a way to get it to run please let me know. I would prefer it use my transaction block.

sp_execute expects parameter '@handle' of type 'int'
0
Nitin SontakkeDeveloperCommented:
Yes, that right. Here is documentation

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

You are using sp_execute, while I was suggesting to use sp_executeSQL, hence the error of @handle.
0
rwheeler23Author Commented:
I will study this article to get a better understanding. All I know is if I use sp_execute I get that error message. sp_executesql does not give me the error message but if it is not doing a begin/end transaction then that is a problem.
0
Nitin SontakkeDeveloperCommented:
You can have begin / end transaction inside that query string itself if you wish.
0
rwheeler23Author Commented:
Thanks for all of your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.