Originally posted by lambrini_socialist
View Post
CREATE PROCEDURE search_orders @custid nchar(5) = NULL,
@shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
SELECT @sql = @sql + ' AND CustomerID LIKE ''' + @custid + ''''
IF @shipname IS NOT NULL
SELECT @sql = @sql + ' AND ShipName LIKE ''' + @shipname + ''''
EXEC(@sql)
If you set @shipname to: ' DROP TABLE Orders --
Then @Sql is executed as:
SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE orders --'
But, yes, you are right, paramatization (sp?) is the key but do you not class the above as dynamic SQL cos i do?
Comment