Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dynamic SQL and EXEC sp_executesql - incorrect calling

Posted on 2008-10-16
10
1,691 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

807 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