Trying to do a search routine where the resultset is derived from user input based on optional or mandatory fields. In this case the query fields span three main tables plus a couple of reference (lookup) tables. Once the results are returned and bound to a datagrid the user selects a single record using the first column.

Here's what the sql looks like.

SELECT 0 as Selected, Booking.Booking_ID, LeisureClient.LeisureClient_ID AS Client_ID, (SELECT UserName FROM AppUser WHERE AppUser.AppUser_ID = Booking.AppUser_ID) AS CreatedBy, (SELECT (SELECT RTRIM(PassengerTitle) FROM PassengerTitle WHERE PassengerTitle.PassengerTitle_ID = LeisureClient.ClientTitle_ID) + ' ' + LeisureClient.Initials_Forename + ' ' + LeisureClient.Surname FROM LeisureClient WHERE LeisureClient.LeisureClient_ID = Booking.Client_ID) AS Client, Booking.CreationDate, Booking.WebSiteBookingReference, (SELECT BookingStatus FROM BookingStatus WHERE BookingStatus.BookingStatus_ID = Booking.BookingStatus_ID) AS Status, (SELECT ClientContactDetail FROM ClientContact WHERE ClientContact.Client_ID = LeisureClient.LeisureClient_ID AND ClientContact.ClientContacTtype_ID = (SELECT ClientContactType.ClientContactType_ID FROM ClientContactType WHERE ClientContactType.ClientContactType = 'Internet Email Address')) AS Email FROM Booking, LeisureClient WHERE LeisureClient.LeisureClient_ID = Booking.Client_ID

Is this possible ?

Any suggestions welcomed.

Yes, it's absolutely possible. If the resultset you want is a read-only list, look into using a dynamic typed list.

I don't think subqueries in selects are supported. However, your query should be rewritten to use joins in any case (LLBLGen or not). This will make your query easier to read (in my opinion) and will make converting this to a dynamic list easier.

SELECT 
    b.booking_id,
    l.leisureclient_id Client_ID,
    a.UserName,
    bs.bookingstatus status
FROM 
    booking b
    INNER JOIN leisureclient l ON l.leisureclient_id = b.client_id
    INNER JOIN AppUser a ON a.appuser_id = b.appuser_id
    INNER JOIN BookingStatus bs ON bs.bookingstatus_id = b.bookingstatus_id
    etc.

(this code is not syntax checked, obviously)

I'm also not sure that you can use literals as part of the select list in LLBL (e.g. "0 as Selected"), which to me is not a good practice anyway, and hints at some sort of business logic (which you don't want in your query, IMHO).

The docs on dynamic types lists are pretty good, and I think you can find a number of examples if you search these forums.

EDIT: I don't see where filtering comes into play in the query you posted. But filtering should be straightforward using a dynamic typed list.

Also, this part of your query:

(SELECT (SELECT RTRIM(PassengerTitle) FROM PassengerTitle WHERE PassengerTitle.PassengerTitle_ID = LeisureClient.ClientTitle_ID) + ' ' + LeisureClient.Initials_Forename + ' ' + LeisureClient.Surname FROM LeisureClient WHERE LeisureClient.LeisureClient_ID = Booking.Client_ID) AS Client,

Will require an expression.

HTH,

Phil

I looked at this originally as sql as posted and developed something similar using predicates. I can to some extent match my sql with the LLBLGen output. However, I end up a resultset over which I have to do several passes to get the data I want.

So thought I'd try some dynamic SQL and use LLBLGen to call the sproc. I have 5 parameters and I can successfully run this code in sql query analyser. However, calling it from LLBLGen code causes the sproc to fail. Running it with the first parameter always works and returns a single row. However, any other parameters returns a 'System.Data.SqlClient.SqlException'. I also found that I have to convert all my parameter types from into to char as e.g., the first parameter is optional but being a value type can't be null.

I can trace it as far as the LLBLGen call to CallRetrievalStoredProcedure and the adapter.Fill(tableToFill); line, which is where it bombs.

Anyone got any ideas why this fails? There is nothing wrong with the sproc as I said, works fine in query anayser with all and any parameter. Call it from code and it bombs.

CREATE PROCEDURE [BookingSearch] @booking_ref char(10), @websitereference char(12) = null, @firstname varchar(30) = null, @lastname varchar(40) = null, @websiteemail varchar(40) = null AS

SET QUOTED_IDENTIFIER OFF

DECLARE @booking_id AS int IF @booking_ref IS NOT NULL SET @booking_id = CAST(@booking_ref AS int )

DECLARE @SQL nvarchar(4000)

SELECT @SQL = "SELECT DISTINCT 0 AS 'Select', Booking.Booking_ID, LeisureClient.LeisureClient_ID AS 'Client_ID', (SELECT UserName FROM AppUser WHERE AppUser.AppUser_ID = Booking.AppUser_ID) AS 'User', Booking.CreationDate AS 'Creation Date', (SELECT (SELECT RTRIM(PassengerTitle) FROM PassengerTitle WHERE PassengerTitle.PassengerTitle_ID = LeisureClient.ClientTitle_ID)" SET @SQL = @SQL + " + " SET @SQL = @SQL + "' ' " SET @SQL = @SQL + " + LeisureClient.Initials_Forename + " SET @SQL = @SQL + "' '" SET @SQL = @SQL + " + LeisureClient.Surname FROM LeisureClient WHERE LeisureClient.LeisureClient_ID = Booking.Client_ID) AS 'ClientName', Booking.WebSiteBookingReference AS 'WebSite Ref', (SELECT BookingStatus FROM BookingStatus WHERE BookingStatus.BookingStatus_ID = Booking.BookingStatus_ID) AS 'Status', (SELECT ClientContactDetail FROM ClientContact WHERE ClientContact.Client_ID = LeisureClient.LeisureClient_ID AND ClientContact.ClientContactType_ID = (SELECT ClientContactType.ClientContactType_ID FROM ClientContactType WHERE ClientContactType.ClientContactType = " SET @SQL = @SQL + "'" SET @SQL = @SQL + "Internet Email Address" SET @SQL = @SQL + "'" SET @SQL = @SQL + ")) AS 'Website Email'" SET @SQL = @SQL + " FROM booking, leisureclient, clientcontact WHERE LeisureClient.LeisureClient_ID = Booking.Client_ID AND ClientContact.Client_ID = LeisureClient.LeisureClient_ID"

IF @booking_id IS NOT NULL SET @SQL = @SQL + " AND Booking.Booking_ID = " + CAST(@booking_id AS CHAR(10)) IF @websitereference IS NOT NULL SET @SQL = @SQL + " AND Booking.WebSiteBookingReference = '" + @websitereference + "'" IF @firstname IS NOT NULL SET @SQL = @SQL + " AND LeisureClient.Initials_Forename = '" + @firstname + "'" IF @lastname IS NOT NULL SET @SQL = @SQL + " AND LeisureClient.Surname = '" + @lastname + "'" IF @websiteemail IS NOT NULL SET @SQL = @SQL + " AND ClientContact.ClientContactDetail = '" + @websiteemail + "'"

SET @SQL = @SQL + " ORDER BY booking.booking_id"

PRINT @SQL

EXEC sp_executesql @SQL, N'@booking_ref char(10), @websitereference char(12), @firstname varchar(30), @lastname varchar(40), @websiteemail varchar(40)', @booking_ref, @websitereference, @firstname, @lastname, @websiteemail GO

Sorry, that's a little much to parse just by looking through it.

I think you are taking the wrong approach, though--if you have the power of LLBL on your side, why muck around with dynamic sql in a stored proc? Of all the options for doing a query with optional filters, this method would be my last choice. Not trying to be mean--I just think you are making this much harder on yourself than you have to! Dynamic SQL using strings is both hard to test and hard to maintain.

If you are going to use a stored proc, get rid of the dynamic SQL and do something with your where clause like:

AND (b.booking = @booking_Id OR @booking_id IS NULL)

This basically reads, in English: "if a value was supplied for @booking_id, use it as a filter, otherwise do not use it as a filter."

This may not perform well on a large system, as the execution plan may not take advantage of the index on booking_Id (assuming there is one). But if you are going to use a stored proc, consider the above method in lieu of dynamic, string-based SQL.

But if you can learn how to do this kind of operation using a dynamic typed list (see my first reply), it will allow you much more flexibility. For example, if you want to add dynamic sorting and paging to your query, it would be very easy to do. Add these features to a stored proc (dynamic or otherwise) is a major pain.

Not sure if that's helpful or not.

Phil

Many thanks for this. I'll work my way towards doing typed lists eventually, but as always with 'newbies' they have some work to do first. I remember a few months ago starting out with LLBLGen whether I would ever get anywhere with it. Well 3 months on I made the right choice no question.

Ttaking up you're suggestion to abandon dynamic SQL I re-wrote the sproc to remove the dynamic where clause with the suggestion you made using

... AND (Booking.Booking_ID = @booking_Id OR @booking_id IS NULL)

Again, the sproc works perfectly well in query analyser, but calling it from code bombs with the same error when adapter.Fill(tableToFill); is called in CallRetrievalStoredProcedure.

I'll search this site for solutions, but if you have any idea why this is I'd be grateful if you could advise.

Again, the sproc works perfectly well in query analyser, but calling it from code bombs with the same error when adapter.Fill(tableToFill); is called in CallRetrievalStoredProcedure

Would you please elaborate more about the error? Posting a stack trace will be very helpful. Thanks.

Walla, I opened another post entitled 'CallRetrievalStoredProcedure Problem and there's a stack trace in there.

Basically, the horrible dynamic sql you see in this thread I have re-worked as a normal non-dynamic sproc and no sp_execsql call at the end

As I said in the other post, the code runs perfectly fine inside of Query Analyser, but called from LLBLGen code generates an error the source of which is the passed-in parameters. This is behaviour that I don't understand: five parameters, all optional, all set to null if no value is supplied before the sproc is called. I

f I pass in the third parameter the SQL data provider says the second param is missing, if I pass in the first parameter the error related to the absense of the second parameter.

In short, there is something I'm not understanding that I was hoping one of you guys could give me an answer to. Appreciate as well it is not an LLBLGen issue because it is standard .NET data access code being called.

As I'm a bit lost what the state of the problem is (and what problem we're all chasing ), could you recap for me a bit what the real problem is etc.? As other threads are also involved as it seems, I'm not sure if this thread is basicly the same problem as the other thread about the proc or not.

I found the answer in various messages

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=2929

for instance, passing nulls to stored procs was the issue. My work-around was to pass String.Empty for null and have the sproc conver to null.