I'm having trouble getting the following stored procedure to work. When I
call it, I get 0 rows returned. I call it like so:
EXEC RetrieveShipments 'City','Is','Omaha'
or
EXEC RetrieveShipments 'City','Contains','Omaha'
neither method returns any rows. Each field in the database is defined as
char(255).
There are numerous rows in the database where City is Omaha.
Any help is appreciated.
----
--
ALTER PROCEDURE [dbo].[RetrieveShipments]
@.ColumnName varchar(32),
@.Expr varchar(16),
@.Data varchar(64)
AS
BEGIN
SET NOCOUNT ON;
IF @.Expr = 'Is' BEGIN
SELECT *
FROM Shipped
WHERE @.ColumnName = @.Data
END
IF @.Expr = 'Contains' BEGIN
SELECT *
FROM Shipped
WHERE @.ColumnName LIKE '%'+@.Data
OR @.ColumnName LIKE '%'+@.Data+'%'
OR @.ColumnName LIKE @.Data+'%'
END
ENDYou can't pick a column name dynamically like that. Please read these
articles:
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Terry Olsen" <tolsen64@.hotmail.com> wrote in message
news:ODCTtz2RGHA.4792@.TK2MSFTNGP14.phx.gbl...
> I'm having trouble getting the following stored procedure to work. When I
> call it, I get 0 rows returned. I call it like so:
> EXEC RetrieveShipments 'City','Is','Omaha'
> or
> EXEC RetrieveShipments 'City','Contains','Omaha'
> neither method returns any rows. Each field in the database is defined as
> char(255).
> There are numerous rows in the database where City is Omaha.
> Any help is appreciated.
> ----
--
> ALTER PROCEDURE [dbo].[RetrieveShipments]
> @.ColumnName varchar(32),
> @.Expr varchar(16),
> @.Data varchar(64)
> AS
> BEGIN
> SET NOCOUNT ON;
> IF @.Expr = 'Is' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName = @.Data
> END
> IF @.Expr = 'Contains' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName LIKE '%'+@.Data
> OR @.ColumnName LIKE '%'+@.Data+'%'
> OR @.ColumnName LIKE @.Data+'%'
> END
> END
>
>|||Okay, after perusing through the articles (I printed them out for in-depth
reading later), I came up with this and it works. I'd like some input on
whether this is "good form" and "safe code". I'd like to see how it could
be done better if you have examples. Thanks.
ALTER PROCEDURE [dbo].[RetrieveShipments]
@.ColumnName char(255),
@.Expr char(255),
@.Data char(255)
AS
SET NOCOUNT ON;
DECLARE @.sql varchar(4000)
IF @.Expr = 'Is' BEGIN
SELECT @.sql = 'SELECT * FROM Shipped WHERE ' + @.ColumnName + ' = ''' + @.Data
+ ''''
END
IF @.Expr = 'Contains' BEGIN
SELECT @.sql = 'SELECT * FROM Shipped WHERE (' +
@.ColumnName + ' LIKE ' + '''%' + @.data + ''') OR (' +
@.ColumnName + ' LIKE ' + '''%' + @.data + '%'') OR (' +
@.ColumnName + ' LIKE ' + '''' + @.data + '%'')'
END
EXEC(@.sql)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O1SUt42RGHA.4920@.tk2msftngp13.phx.gbl...
> You can't pick a column name dynamically like that. Please read these
> articles:
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html|||On Tue, 14 Mar 2006 20:36:40 -0700, Terry Olsen wrote:
>Okay, after perusing through the articles (I printed them out for in-depth
>reading later), I came up with this and it works. I'd like some input on
>whether this is "good form" and "safe code".
Hi Terry,
Not at all.
Change this line
EXEC(@.sql)
to
PRINT @.sql
Then execute your procedure with these arguments:
EXEC [dbo].[RetrieveShipments]
@.ColumnName = '1 = 2; DROP TABLE Shipped; --',
@.Expr = 'Is',
@.Data = 'Irrelevant'
and imagine what would have happened if you had not changed the EXEC to
PRINT.
Other errors are the use of char instead of varchar (scroll the output
of the previous exercise to the right to see the rest of the query -
probably not what you intended either) and the unneeded use of three
LIKE expressions: % matches _ZERO_ or more characters, so you can just
use
LIKE '%Anything%'
instead of
LIKE '%Anything%' OR LIKE '%Anything' OR LIKE 'Anything%'
> I'd like to see how it could
>be done better if you have examples.
See the print-out that you've already made of:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||You can do it, one of the things that you need to ensure that you do however
is give your user(s) Select permissions for the target table if your
security exposure permits you to do so...
Example based on your original...
--
alter PROCEDURE [dbo].[RetrieveShipments]
@.ColumnName varchar(32),
@.Expr varchar(16),
@.Data varchar(64)
AS
declare @.dothis nvarchar (2000)
BEGIN
SET NOCOUNT ON;
IF @.Expr = 'Is' BEGIN
select @.dothis = ' SELECT * FROM Shipped WHERE ' + @.ColumnName + ' = ' +
@.Data
END
IF @.Expr = 'Contains' BEGIN
select @.dothis = ' SELECT * FROM Shipped WHERE ' + @.ColumnName + ' LIKE %' +
@.Data + ' OR ' + @.ColumnName + ' LIKE %' + @.Data + '% OR ' + @.ColumnName + '
LIKE ' + @.Data + '%'
END
END
execute(@.dothis)
--
Alice
========================================
=================
please respond to the newsgroups so that everyone can see the answers!
========================================
=================
"Terry Olsen" <tolsen64@.hotmail.com> wrote in message
news:ODCTtz2RGHA.4792@.TK2MSFTNGP14.phx.gbl...
> I'm having trouble getting the following stored procedure to work. When I
> call it, I get 0 rows returned. I call it like so:
> EXEC RetrieveShipments 'City','Is','Omaha'
> or
> EXEC RetrieveShipments 'City','Contains','Omaha'
> neither method returns any rows. Each field in the database is defined as
> char(255).
> There are numerous rows in the database where City is Omaha.
> Any help is appreciated.
> ----
--
> ALTER PROCEDURE [dbo].[RetrieveShipments]
> @.ColumnName varchar(32),
> @.Expr varchar(16),
> @.Data varchar(64)
> AS
> BEGIN
> SET NOCOUNT ON;
> IF @.Expr = 'Is' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName = @.Data
> END
> IF @.Expr = 'Contains' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName LIKE '%'+@.Data
> OR @.ColumnName LIKE '%'+@.Data+'%'
> OR @.ColumnName LIKE @.Data+'%'
> END
> END
>
>
Jens K. Suessmeyer wrote: