0

How to parameterize a call to a stored procedure in an NL()

Jet Reports Historic Posts

March 12, 2013 17:03

None

Follow

Hi all,

I managed to execute a stored procedure from within an =NL(..) function. Now I'd like to know how I may parametereize the invocation of the sproc.

This is what you need in order to use a stored procedure:

Jfx enforces you to only use SELECT statements. Use TSQL OPENQUERY for this
Example:
SELECT Kontaktnummer, Firmenname1, PLZ, Ort, StrasseHausnummer
FROM OPENQUERY([loopback], 'navaid.dbo.getContactDataFromSegment ''SEG000004''')

This will execute the sproc getContactDataFromSegment

The first parameter of OPENQUERY is a linked server. I wanted to use my databes server as a linked server to itself, so I created the linked server Iand named it "loopback" but any other name will do.

Here is how I created the linked server to itself (I forgot where I found this code, but thanks to the author)
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
Go

Try it out in SQL Management studio:
SELECT Kontaktnummer, Firmenname1, PLZ, Ort, StrasseHausnummer
FROM OPENQUERY([loopback], 'navaid.dbo.getContactDataFromSegment ''SEG000004''')


Now I can use the execute sproc statement in my NL()

=NL("table";"SQL=SELECT Kontaktnummer, Firmenname1, PLZ, Ort, StrasseHausnummer FROM OPENQUERY([loopback], 'navaid.dbo.getContactDataFromSegment ''SEG000004''')";{"Kontaktnummer"."Firmenname1"."PLZ"."Ort"."StrasseHausnummer"})



My problem now is, how can I parameterize the segment number ( SEG000004 ) to be read from a cell i.e. A4



Any help is welcome
Thanks
Sascha

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts March 12, 2013 19:13

  Hi Sascha,
  
  So I should say first that you are working around the intention of SQL= which is to not allow the execution of stored procedures. The purpose of not allowing stored procedure execution is to limit your ability to potentially make changes to your SQL records through Jet functions. Because Jet functions may get executed multiple times during a report run in a way that is not expected by the user, any stored procedure that makes data changes would be very dangerous to call in a Jet Report. So you have been warned.
  
  All that being said, I assume you can get the segment value from cell A4 very easily like this:
  
  =NL("table";"SQL=SELECT Kontaktnummer, Firmenname1, PLZ, Ort, StrasseHausnummer FROM OPENQUERY([loopback], 'navaid.dbo.getContactDataFromSegment ''"&A4&"''')";{"Kontaktnummer"."Firmenname1"."PLZ"."Ort"."StrasseHausnummer"})
  Now if what you're looking for is the ability to do Jet filtering in cell A4 such as "Segment1|Segment2|Segment3" then this is not going to work for you. You would have to specify parameters in the format SQL expects them, not in the standard Jet filter format. Assuming you are doing that, I believe this approach should work.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 13, 2013 20:55

  Hello Hughes,
  
  thanks for your answer. I'm going to give it a try back in the office tomorrow morning.
  
  Yes, I am well aware of the fact that a strored procedure can do a ot of harm. But it is my responsibility as sproc developer to take care of this.
  Yes, I know that I'd have to take care if someone would enter seg0001|seg00123
  or an *
  or between date1 and date2
  or <= somevalue
  and so on.
  
  (allthough I could also handle those parameters but with some effort. There are sufficient techniques in TSQL.
  For everyone who is interested, have a look at sp_executesql)
  
  
  The use of stored procedures is not guaranteed to be the best option in any case but it is an alternative whenever the pure Jfx functions are to slow.
  Or there are already queries in SQL for retrieving data from NAV, or you are just so fast in writing the stored procedue)
  
  One solution to the "my sproc might damage the database" problem
  - I have created a separate database on an SQL server.
  - To access this database I have created a user NAVREADER.
  This database user is allowed to read only.
  - I have added the NAV SQL database as a linked server,
  so the reader can access the NAV tables.
  - In JetReports I use the data connection of the NAVREADER user.
  - The invocation of my stored procedurre is under the NAVREADER account.
  
  This way I can protect the NAV database from being written to by the stored procedures which I call through Jfx.
  
  Have a nice day
  Regards
  Sascha