Having problems getting stored procedure to work.

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

Hard coding colum names in returned DetailsView table

Hi all,

We're selecting data from our database, FirstName, LastName, MobileNumber etc.

We're using the detaials view function to return it in a table upon selection. However all of the variables are returned as they are in the database, ie:without spaces.

We tried putting in spaces by selecting "AS what ever", but MSSQL does not seem to like spaces.

Any ideas?

Thanks

Hey,

Are you trying to make the column names have spaces, or the data/ If the first, try using:

AS "First Name"

OR

AS [First Name]

|||

You can rename the columns like this:

SELECT PhoneNumber AS [Phone Number]
FROM TestTable

Handling errors returned by SSRS

We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.

How can that be done?. We are making URL access to the report server.

Thanks.

Please help me to understand this better. If you use URL addressability what application layer will handle the error messages? If you use the VS.NET 2005 Report Viewer control, your application can handle the ReportError event.|||

Alright,

So we are using an iframe in our application which we are making a url call to the report server. Hence, if there is a problem like "access" denied, we do not want the iframe to read "SQL Server error" but have some error that shows that the user is interacting with our application. This can only be done if on the report server we could write some generic error page which will always get called anytime ssrs throws an error.

Thanks.

|||Sorry, you are out of lack here. URL addressability is certainly very easy but not that flexible. Same limitations apply as invoking a server-side web page by URL.|||

Hi,

I have my report viewer control and reporterror event to handel all the exception.Do we need to call the reporterror event in my code or automatically it will be called when error occurs?

Thanks,

Ranjan

|||Not sure what you mean by call the event. The event handler will be called for you when the event happens (in this case the report errors out).|||

Hi Teo,

If u have any sample code of how to show a report in reportviewer along with reporterror event and if you can post it here it would be very helpful.

Thanks,

Ranjan

|||

private void reportViewer1_ReportError(object sender, Microsoft.Reporting.WinForms.ReportErrorEventArgs e)

{

// use e.Exception to get to the exception

// set e.Handled to true to prevent the ReportViewer from displaying an error message.

}

More about ReportViewer in this article.

Handling errors returned by SSRS

We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.

How can that be done?. We are making URL access to the report server.

Thanks.

Please help me to understand this better. If you use URL addressability what application layer will handle the error messages? If you use the VS.NET 2005 Report Viewer control, your application can handle the ReportError event.|||

Alright,

So we are using an iframe in our application which we are making a url call to the report server. Hence, if there is a problem like "access" denied, we do not want the iframe to read "SQL Server error" but have some error that shows that the user is interacting with our application. This can only be done if on the report server we could write some generic error page which will always get called anytime ssrs throws an error.

Thanks.

|||Sorry, you are out of lack here. URL addressability is certainly very easy but not that flexible. Same limitations apply as invoking a server-side web page by URL.|||

Hi,

I have my report viewer control and reporterror event to handel all the exception.Do we need to call the reporterror event in my code or automatically it will be called when error occurs?

Thanks,

Ranjan

|||Not sure what you mean by call the event. The event handler will be called for you when the event happens (in this case the report errors out).|||

Hi Teo,

If u have any sample code of how to show a report in reportviewer along with reporterror event and if you can post it here it would be very helpful.

Thanks,

Ranjan

|||

private void reportViewer1_ReportError(object sender, Microsoft.Reporting.WinForms.ReportErrorEventArgs e)

{

// use e.Exception to get to the exception

// set e.Handled to true to prevent the ReportViewer from displaying an error message.

}

More about ReportViewer in this article.