quote:

---

Originally posted by graz

If the URL has an integer as a parameter, that is type checked when it is passed into the stored procedure.

---

--create a table

create table #test(
id bigint identity(1,1) Primary key,
someText nvarchar(50) not null,
someInt int not null
)



--fill in some values

insert into #test 
SELECT 'foo',1 
UNION ALL SELECT 'foo',2 
UNION ALL SELECT 'bar',2 
UNION ALL SELECT 'bar',2 
UNION ALL SELECT 'baz',1



--create sql command and parameter list 
--(sql 2005 and forward can use nvarchar(MAX) instead of nvarchar(4000)

DECLARE @sql NVARCHAR(4000)
DECLARE @sqlList NVARCHAR(4000)
SET @sql=N'SELECT * FROM #test WHERE id=@RequiredId OR (someText=@SearchText AND someInt=@SearchValue)'
SET @sqlList=N'@RequiredId BIGINT, @SearchText NVARCHAR(50), @SearchValue INT'



--These could be the input parameters of the 
--stored procedure where you've put the dynamic sql

DECLARE @ActualSearchIdValue BIGINT
DECLARE @ActualTextValue NVARCHAR(50)
DECLARE @ActualIntValue INT

SET @ActualSearchIdValue =1
SET @ActualTextValue ='bar'
SET @ActualIntValue =2


--execute the dynamic sql

EXEC sp_executeSQL 
	@sql,
	@sqlList,
	@RequiredId = @ActualSearchIdValue ,
	@SearchText = @ActualTextValue ,
	@SearchValue = @ActualIntValue


--clean up

drop table #test

id | someText | someInt
------------------------------
1  | foo      | 1
3  | bar      | 2
4  | bar      | 2

' or 1=1

quote:

---

Originally posted by dsutherland

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)

---

quote:

---

Originally posted by patrick2525

Wouldn't adding the 'OPTION(RECOMPILE)' to the @sql string be a good idea, or is this not necessary?

---

quote:

---

Originally posted by graz

quote:

---

Originally posted by dsutherland

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)

---

This is the exact type of code I'm trying to avoid. Using ISNULL around the column prevents the use of indexes on those columns so this will always generate table scans. That's why you use dynamic SQL in these cases.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

---

quote:

---

Originally posted by simkin2004

quote:

---

Originally posted by graz

quote:

---

Originally posted by dsutherland

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE CustomerID = ISNULL(@CustomerID, CustomerID)
AND ContactID = ISNULL(@ContactID, ContactID)

---

This is the exact type of code I'm trying to avoid. Using ISNULL around the column prevents the use of indexes on those columns so this will always generate table scans. That's why you use dynamic SQL in these cases.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

---

The indexes can be added back to this query (without using Dynamic SQL) with a simple OR statement.

SELECT [SalesOrderID], [OrderDate], [Status],
[CustomerID], [ContactID]
FROM [Sales].[SalesOrderHeader]
WHERE (CustomerID IS NULL OR CustomerID = @CustomerID)
AND (ContactID IS NULL OR ContactID = @ContactID)

---

WHERE
    (@CustomerID IS NULL OR [CustomerID] = @CustomerID)
    AND (@ContactID IS NULL OR [ContactID] = @ContactID)

quote:

---

Originally posted by simkin2004

Thank you, Transact Charlie.

WOO HOO! Safe Performant Dynamic SQL, here we come!

---

SELECT		o.* 
FROM		dbo.Orders AS o
CROSS APPLY	(
			SELECT	CASE WHEN o.OrderDate = @OrderDate THEN 1 ELSE 0 END
				+ CASE WHEN o.RequiredDate = @RequiredDate THEN 1 ELSE 0 END
				+ CASE WHEN o.ShippedDate = @ShippedDate THEN 1 ELSE 0 END
		) AS f(Ranks)
WHERE		f.Ranks > 0
ORDER BY	f.Ranks DESC