Solved

Dynamic SQL and EXEC sp_executesql - incorrect calling

Posted on 2008-10-16
10
1,683 Views
Last Modified: 2012-05-05
Hi,

I have a piece of dynamic sql I need to run that returns a COUNT of all records in a resultset.
The count must be retreived from the dyanmic sql and allocated to the OUTPUT parameter of the stored procedure (responsible for executing the dynamic sql)

I am totally confident, my declaration for:

 EXEC sp_executesl

is not correct - it must return the count from the dynamics sql.


Any help would be appreciated.

Thanks for your time.
CREATE PROCEDURE [dbo].[SelectCountTravelOfferSearchBy]
 

	-- search text

	@SearchText varchar(50) ,
 

	-- foreign key, index and bit columns

	@TravelCenterID varchar(20) = NULL,

	@TravelTypeID varchar(10) = NULL,

	@TourTypeID varchar(20) = NULL,

	@AccommodationTypeID varchar(20) = NULL,

	@RatingTypeID varchar(10) = NULL,

	@BookingTypeID varchar(10) = NULL,

	@RegionID varchar(20) = NULL,

	@SupplierID varchar(20) = NULL,

	@IsSpecialOffer bit = NULL,

	@IsActive bit = NULL,

	

	-- item count

	@ItemCount bigint OUTPUT ,

	

	-- sort expressions

	@SortBy varchar(50) = 'OrderIndex' ,

	@SortDirection varchar(4) = 'ASC'
 

AS
 

	-- turn off rows affected

	SET NOCOUNT ON

			

	--isolation level

	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 

	-- system function stores

	DECLARE 

		@ErrStatus int, 	-- stores error status

		@RowsAffected int	-- stores number of rows affected		
 

	/* ------------------------------------------------------------- 

 	 * Resultset containing record count.

	 * ------------------------------------------------------------- */	

	DECLARE @Count int

	SET @Count = 0 -- initialise count
 
 

	-- dynamic sql params

	DECLARE @MyItemCount int

	SET @MyItemCount = 0 -- initialise count
 
 

	DECLARE @MySql nvarchar(4000)

	SET @MySql = '

	SELECT

		@MyItemCount = ISNULL(COUNT(*),0)

	FROM 

		[dbo].[TravelOffers]

		LEFT OUTER JOIN [dbo].[AccommodationTypes] ON [dbo].[TravelOffers].[AccommodationTypeID] = [dbo].[AccommodationTypes].[AccommodationTypeID]

		INNER JOIN [dbo].[TravelTypes] ON [dbo].[TravelOffers].[TravelTypeID] = [dbo].[TravelTypes].[TravelTypeID]

		INNER JOIN [dbo].[TravelCenters] ON [dbo].[TravelOffers].[TravelCenterID] = [dbo].[TravelCenters].[TravelCenterID]

		LEFT OUTER JOIN [dbo].[Regions] ON [dbo].[TravelOffers].[RegionID] = [dbo].[Regions].[RegionID]

		LEFT OUTER JOIN [dbo].[Suppliers] ON [dbo].[TravelOffers].[SupplierID] = [dbo].[Suppliers].[SupplierID]

		INNER JOIN [dbo].[RatingTypes] ON [dbo].[TravelOffers].[RatingTypeID] = [dbo].[RatingTypes].[RatingTypeID]

		INNER JOIN [dbo].[BookingTypes] ON [dbo].[TravelOffers].[BookingTypeID] = [dbo].[BookingTypes].[BookingTypeID]

		LEFT OUTER JOIN [dbo].[TourTypes] ON [dbo].[TravelOffers].[TourTypeID] = [dbo].[TourTypes].[TourTypeID]

		'
 

	--Where clause

	BEGIN

		SET @MySql = @MySql + 'WHERE

		'

	END

	

	--TravelCenterID

	IF (@TravelCenterID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[TravelCenterID] = ''' + @TravelCenterID + '''' 

		SET @Count = @Count + 1

	END

	

	--TravelTypeID

	IF (@TravelTypeID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[TravelTypeID] = ''' + @TravelTypeID + '''' 

		SET @Count = @Count + 1

	END

	

	--TourTypeID

	IF (@TourTypeID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[TourTypeID] = ''' + @TourTypeID + '''' 

		SET @Count = @Count + 1

	END

	

	--AccommodationTypeID

	IF (@AccommodationTypeID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[AccommodationTypeID] = ''' + @AccommodationTypeID + '''' 

		SET @Count = @Count + 1

	END

	

	--RatingTypeID

	IF (@RatingTypeID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[RatingTypeID] = ''' + @RatingTypeID + '''' 

		SET @Count = @Count + 1

	END

	

	--BookingTypeID

	IF (@BookingTypeID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[BookingTypeID] = ''' + @BookingTypeID + '''' 

		SET @Count = @Count + 1

	END

	

	--RegionID

	IF (@RegionID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[RegionID] = ''' + @RegionID + '''' 

		SET @Count = @Count + 1

	END

	

	--SupplierID

	IF (@SupplierID IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[SupplierID] = ''' + @SupplierID + '''' 

		SET @Count = @Count + 1

	END

	

	--IsSpecialOffer

	IF (@IsSpecialOffer IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[IsSpecialOffer] = ' + CAST(@IsSpecialOffer AS varchar) 

		SET @Count = @Count + 1

	END

	

	--IsActive

	IF (@IsActive IS NOT NULL)

	BEGIN

		IF (@Count > 0) SET @MySql = @MySql + ' AND '

		SET @MySql = @MySql + '[dbo].[TravelOffers].[IsActive] = ' + CAST(@IsActive AS varchar) 

		SET @Count = @Count + 1

	END

	SET @MySql = @MySql + '

			AND

			(

				-- search filters!

				[dbo].[TravelOffers].[TravelCenterID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[TravelTypeID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[TourTypeID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[AccommodationTypeID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[RatingTypeID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[BookingTypeID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[RegionID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[SupplierID] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[OfferTitle] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[ShortDescription] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[HtmlDescription] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[AdultNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[ChildNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[InfantNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[SeniorNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[ConcessionNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[StudentNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[FamilyNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[PaymentMethods] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[PaymentInstructions] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[PaymentTerms] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[SpecialNotes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[CancellationPolicy] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[Notes] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[CreatedBy] LIKE ''%''' + @SearchText + '''%'' OR 

				[dbo].[TravelOffers].[ModifiedBy] LIKE ''%''' + @SearchText + '''%''

			)

	'
 

	-- print sql

	--PRINT(@MySql)
 
 
 
 
 
 

	-- BUG HERE!!!!!!!!!!!!!!!!!!!!!!!! Pretty sure the declaration is wrong. I need to retrieve the @MyItemCount value from the dynamic sql and store it in the procedure output parameter @ItemCount.

	EXEC sp_executesql @MySql, N'@MyItemCount bigint OUTPUT', @ItemCount = @MyItemCount OUTPUT
 
 
 
 
 

	-- check for errors

	IF @ErrStatus <> 0

	BEGIN

		RAISERROR('Error occurred in stored procedure ''[dbo].[SelectCountTravelOfferSearchBy]''', 10, 1)

		RETURN @ErrStatus

	END
 

	-- turn on rows affected

	SET NOCOUNT OFF
 

	-- success(0)

	RETURN 0
 
 

GO

Open in new window

0
Comment
Question by:smacca
  • 5
  • 4
10 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 22738215
You don't need the extra variable declaration

Change this line:
    @MyItemCount = ISNULL(COUNT(*),0)
to
    @ItemCount = ISNULL(COUNT(*),0)

and this line
    EXEC sp_executesql @MySql, N'@MyItemCount bigint OUTPUT', @ItemCount = @MyItemCount OUTPUT
To
    EXEC sp_executesql @MySql, N'@ItemCount bigint OUTPUT', @ItemCount OUTPUT
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22739382
I actually wonder whether you need to do this with Dynamic SQL.  There is a method I've been using a lot lately that seems fast and doesn't involve the (rather fragile) risky business of dynamic sql.  

Let's say you have a webpage that has a search form:

Travel Center {drop down with ID's - with the Select One choice set to a value of zero}
Region {drop down with ID's - again, the top choice value is zero}

Select Count(TravelOffersID)
FROM
{your joined together table structure}
WHERE
      (@TravelCenterID = '0' OR [dbo].[TravelOffers].[TravelCenterID] = @TravelCenterID)
      and (@RegionID = '0' OR [dbo].[TravelOffers].[RegionID] = @RegionID)
      and....
      and
      ( [dbo].[TravelOffers].[TravelCenterID]  LIKE '%' + @SearchText +'%'
        OR [dbo].[TravelOffers].[RegionID] LIKE '%' + @SearchText + '%'
        OR.....
     )



This method doesn't require any If/ Logic or dynamic SQL.  It is very fast and reliable.  You will be amazed at how powerful this WHERE scheme can be.  Basically, you can set up a bare minimum of procedures to cover all sorts of different select requirements.  

0
 

Author Comment

by:smacca
ID: 22739804
Would the following sql make sense as well:

SELECT COUNT(TravelOffersID)
FROM
{
  your joined together table structure
}
WHERE(
     1 = 1 OR [dbo].[TravelOffers].[TravelCenterID] = @TravelCenterID)
      and (1 = 1 OR [dbo].[TravelOffers].[RegionID] = @RegionID)
      and....
      and
      ( [dbo].[TravelOffers].[TravelCenterID]  LIKE '%' + @SearchText +'%'
        OR [dbo].[TravelOffers].[RegionID] LIKE '%' + @SearchText + '%'
        OR.....
     )
)


0
 

Author Comment

by:smacca
ID: 22739817
Disregard question above. Thanks for your help.
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22739820
I don't think you want that 1=1 line there.  That will ensure that ALL rows are returned, which I think is not what you want.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:Steve Krile
ID: 22739835
Ah....disregarding.

But, that 1=1 doesn't check to see if a filter parameter was entered.
0
 

Author Comment

by:smacca
ID: 22746519
If my data layer, ensures null values are passed in when the none filter option is selected, would the sql below make sense.


Does this sql, mean that filter parameters are now OPTIONAL and you can filter by any combination.
SELECT 

  COUNT(*)

FROM

{

  your joined together table structure

}

WHERE(

  (@TravelCenterID IS NULL OR [dbo].[TravelOffers].[TravelCenterID] = @TravelCenterID)

  AND (@RegionID IS NULL OR [dbo].[TravelOffers].[RegionID] = @RegionID)

  AND    

  AND    

  AND    

  ( 

    [dbo].[TravelOffers].[TravelCenterID]  LIKE '%' + @SearchText +'%'

    OR [dbo].[TravelOffers].[RegionID] LIKE '%' + @SearchText + '%'

    OR.....

  )

)

Open in new window

0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22756559
Yep.  And I have had trouble ensuring nulls are passed, depending on what presentation layer you are using, so you can make your @ variables equal to some *thing*.  I've used things like:

 @TravelCenterID = -1

  Or

 @TravelCenterID = 'xxz$$%$%zxx'
0
 

Author Comment

by:smacca
ID: 22757195
That is brilliant - I am doing bit logic in all my procedures to figure out the combinations in the where clause.
Your SQL has saved me a alot of work and maintenance on my procedures in the future - thank you.

My Data Access Layer ensures that NULL is passed to the procedure when EMPTY strings are passed for string types, and 0 is passed with int types.


Thanks again for your great input.
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22757950
My pleasure.  I stumbled on this a few months back when I was using logic like this:

select ID from table as T where IsNull(@parameter,T.Parameter) = T.Parameter

This works fine, but has one HUGE flaw.  It's slow as hell.  Changing the statement to the examples above allows SQL to bail once it gets a match for the paramenter and not do the other examination.  For the record, thanks Olga!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now