PASSING ORDER BY AS PARAMETERS

I have a stored procedure that excepts @.orderby as a parameter
and executes following sql statement:
select * from titles
order by
case when @.orderby = 'title' then
title
end
asc
Is there anyway I can add second parameter @.direction and control order
ASC/DESC based on parameter value , like "case when @.direction then asc"?
Thanks in advance
Programmerhttp://www.aspfaq.com/2501
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||You can use:
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
However, the plan for such a query would result in a table scan plus a sort
operation.
If you want to allow an efficient plan based on the input, you should either
use dynamic execution, which has it's obvious limitations (SQL injection
attacks and other security issues), or the following static approach:
if @.col = 'title' and @.dir = 'asc'
select * from titles order by title
else if @.col = 'title' and @.dir = 'desc'
select * from titles order by title desc
else if @.col = 'price' and @.dir = 'asc'
select * from titles order by price
else if @.col = 'price' and @.dir = 'desc'
select * from titles order by price desc
...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||Hi
You can try as
exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sergey Zuyev" wrote:

> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||It's not quite that simple.
Create Procedure MyProcedure
@.OrderBy nvarchar(12)
AS
Select * /*We really should list the field names, but this is an
example*/ ,
OrderMeBy = Case @.OrderBy
When 'title' Then title
When 'Price_Asc' Then cast(Price as nvarchar(20))
When 'Price_Desc' Then cast(Price *-1 as nvarchar(20)) /*negated
to go in descending order*/
When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
'12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
recent dates a lower value, thus descending*/
END
From Titles
Order By OrderMeBy
What I wrote actually has a few sorting problems due to the CAST's, but it
demonstrates the method. I don't have a nice little formula for turning
text around to make it sort backwards like I do for numeric fields and
dates.
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||And what if the user specifies "delete from titles" as the value of @.order ?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
> Hi
> You can try as
> exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sergey Zuyev" wrote:
>|||Daniel, using multiple CASE expressions instead of one, where each deals
with one column only, solves all the issues related to differences in
datatypes.
Check out the solution I proposed. You will also find a treatment for the
direction issue.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> It's not quite that simple.
> Create Procedure MyProcedure
> @.OrderBy nvarchar(12)
> AS
> Select * /*We really should list the field names, but this is an
> example*/ ,
> OrderMeBy = Case @.OrderBy
> When 'title' Then title
> When 'Price_Asc' Then cast(Price as nvarchar(20))
> When 'Price_Desc' Then cast(Price *-1 as nvarchar(20))
> /*negated
> to go in descending order*/
> When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
> When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
> '12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
> recent dates a lower value, thus descending*/
> END
> From Titles
> Order By OrderMeBy
>
> What I wrote actually has a few sorting problems due to the CAST's, but it
> demonstrates the method. I don't have a nice little formula for turning
> text around to make it sort backwards like I do for numeric fields and
> dates.
> hth,
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>|||well if u specify the lenght to be 4, then the string would not be accepted
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"JT" wrote:

> And what if the user specifies "delete from titles" as the value of @.order
?
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
>
>|||Thanks, Itzik.
I saw your solution after I'd posted -- I got the basics of my solution here
on this group about 4 years ago. Perhaps SQL Server 7 did not support that
syntax?
Regardless, your solution is much cleaner & I will be putting it to work in
the future.
dwlison
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%235n$t8OnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Daniel, using multiple CASE expressions instead of one, where each deals
> with one column only, solves all the issues related to differences in
> datatypes.
> Check out the solution I proposed. You will also find a treatment for the
> direction issue.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
more
it
asc"?
>|||> I don't have a nice little formula for turning
> text around to make it sort backwards
Well, the other solutions are better, and this is far from efficient, but it
can be done. This assumes a case insensitive collation.
CREATE FUNCTION dbo.SortReverse
( @.in VARCHAR(32) )
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.i TINYINT, @.out VARCHAR(32)
SELECT @.i = 1, @.out = ''
WHILE @.i <= len(@.in)
BEGIN
SELECT @.out = @.out + CHAR(90+65-ASCII(UPPER(SUBSTRING(@.in, @.i, 1))))
SET @.i = @.i + 1
END
RETURN @.out
END
GO
Now you can do this:
DECLARE @.sortOrder VARCHAR(4)
SET @.sortOrder = 'ASC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
SET @.sortOrder = 'DESC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END

Passing Order By as parameter

A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
solution:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/t
hread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/u
rl]
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
Now I have a situation with multiple fields on which to sort, one of them
being descending. How can I use this method to come out with ORDER BY Price
DESC, Title ?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
DVBrown Company
(864)292-5888You could try ORDER BY -price
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:uEOm$UzzFHA.908@.tk2msftngp13.phx.gbl...
>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
> solution:
> [url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm
/thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[
/url]
> select * from titles
> order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
> Now I have a situation with multiple fields on which to sort, one of them
> being descending. How can I use this method to come out with ORDER BY
> Price
> DESC, Title ?
> Thanks.
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> DVBrown Company
> (864)292-5888
>|||On Wed, 12 Oct 2005 09:59:33 -0400, Daniel Wilson wrote:

>A couple months ago, Itzik Ben-Gan offered a nice, paramaterized Order By
>solution:
>[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/
thread/ae2994003a03954f/1ad2f13eb630fa4e?q=Daniel+Wilson&rnum=1#1ad2f13eb630fa4e[/
url]
>select * from titles
>order by
> case when @.col = 'title' and @.dir = 'asc' then title end,
> case when @.col = 'title' and @.dir = 'desc' then title end desc,
> case when @.col = 'price' and @.dir = 'asc' then price end,
> case when @.col = 'price' and @.dir = 'desc' then price end desc,
> ...
>Now I have a situation with multiple fields on which to sort, one of them
>being descending. How can I use this method to come out with ORDER BY Pric
e
>DESC, Title ?
>Thanks.
Hi Daniel,
select * from titles
order by
case when @.col1 = 'title' and @.dir1 = 'asc' then title end,
case when @.col1 = 'title' and @.dir1 = 'desc' then title end desc,
case when @.col1 = 'price' and @.dir1 = 'asc' then price end,
case when @.col1 = 'price' and @.dir1 = 'desc' then price end desc,
case when @.col2 = 'title' and @.dir2 = 'asc' then title end,
case when @.col2 = 'title' and @.dir2 = 'desc' then title end desc,
case when @.col2 = 'price' and @.dir2 = 'asc' then price end,
case when @.col2 = 'price' and @.dir2 = 'desc' then price end desc,
..
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

passing form variables to populate Report Param..

Is this possible? All of our users are required to login to our website in order to access their applications they have particular access to. is there a way to pass the same from variable to the first Report parameter. Currently i have it set up (development environment) that all report parameters are visible when we go to the web page to access report. I only want certain parameters available based on who is logged on, but i do not want to build 20 different reports to accomplish this.
Any help would be greatly appreciatedInstead of creating many different report you may take a look at creating
linked reports. Linked reports carry their own security settings, parameter
settings but share the same report definition. For example, you can provide
default value for a parameter and declare it read-only on a linked report.
Users who have access to this linked report won't be able to set the value
of that parameter.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Derek M" <DerekM@.discussions.microsoft.com> wrote in message
news:A332BCCD-D844-4B9C-B9B0-33C7698CAA62@.microsoft.com...
> Is this possible? All of our users are required to login to our website
in order to access their applications they have particular access to. is
there a way to pass the same from variable to the first Report parameter.
Currently i have it set up (development environment) that all report
parameters are visible when we go to the web page to access report. I only
want certain parameters available based on who is logged on, but i do not
want to build 20 different reports to accomplish this.
> Any help would be greatly appreciated

Passing datetime parameters to Reporting Services

Hi all...
How should I pass datetime parameters to a report in order for it to work?
This is the current scenario:
Dataset has, for example, this query:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN ? AND ?
where FECHA is of DATETIME type in an Oracle Database.
Both unnamed parameters are DateTime report parameters, which retrieve the
correct date values (using code embedded in the report)
When I run the report, I get the typical error message when date format is
incorrect:
"a non-numeric character was found where a numeric was expected"
It seems that report tries to pass the "-" of the date value to the unnamed
parameter.
Formerly I had this query that made the report to work:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
format). But using this way I can have (and I already had) problems with
incompatibilities in Regional Settings where the Reporting Server is
installed. For example, using this latter way, I forced the date to have the
format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
is installed, regional settings are different, so Report crashed because it
sent 08/31/2005 where database (in different server) expected 31/08/2005.
That's why I need to make it independent of regional settings of the target
server.
Any way to manage this?
Thanks a lot in advance
JaimeJaime
I believe this is an issue with the data being returned from the Query.
Not the rendering of the report.
If you run the report in the data tab and scroll to the bottom do you
get the same error?
You might want to take a look at the function Isdate(). Be advised this
will return valid dates so it will filter out any bad dates.
Sounds like someone put a character string in a date field. Does your
database allow this? Like an ASAP or somthing?
Leo
Jaime Stuardo wrote:
> Hi all...
> How should I pass datetime parameters to a report in order for it to work?
> This is the current scenario:
> Dataset has, for example, this query:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN ? AND ?
> where FECHA is of DATETIME type in an Oracle Database.
> Both unnamed parameters are DateTime report parameters, which retrieve the
> correct date values (using code embedded in the report)
> When I run the report, I get the typical error message when date format is
> incorrect:
> "a non-numeric character was found where a numeric was expected"
> It seems that report tries to pass the "-" of the date value to the unnamed
> parameter.
> Formerly I had this query that made the report to work:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> format). But using this way I can have (and I already had) problems with
> incompatibilities in Regional Settings where the Reporting Server is
> installed. For example, using this latter way, I forced the date to have the
> format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> is installed, regional settings are different, so Report crashed because it
> sent 08/31/2005 where database (in different server) expected 31/08/2005.
> That's why I need to make it independent of regional settings of the target
> server.
> Any way to manage this?
> Thanks a lot in advance
> Jaime
>|||Thanks Leo for answering...
When using the Data tab and run the query, I have to enter the date this
way:
30-apr-05 and query returns results.
The date field in database is correct, since as I told, when I use
TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
not DateTime as it should be. I cannot use the parameter as String as I
explained in my first post. In conclusion, the problem is only related to
passing parameters between Reporting Services and Oracle database.
Those DateTime parameters are calculated, for example, using this custom code:
Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
DateTime
Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
End Function
Finally, I'm going to tell you that I could display parameters just before
rendering the report (I'm using API to do it) and these are :
CUATRIMESTRE=1
AÃ?O=2005
NUMERO_POLIZA=5506666
FECHA_INICIO=5/1/2005 12:00:00 AM
FECHA_TERMINO=8/31/2005 12:00:00 AM
CUA_MESES=May - August
CUATRIMESTRE, AÃ?O and NUMERO_POLIZA are parameters the user enters.
FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
FECHA_INICIO and FECHA_TERMINO.
It's clear that Reporting Service is passing the datetime parameter formated
in a manner that isn't accepted by Oracle. I thought date time would be
passed as is, as a DATE datatype.
Any further help would be greately appreciated,
Thanks
Jaime
"cte25117@.centurytel.net" wrote:
> Jaime
> I believe this is an issue with the data being returned from the Query.
> Not the rendering of the report.
> If you run the report in the data tab and scroll to the bottom do you
> get the same error?
> You might want to take a look at the function Isdate(). Be advised this
> will return valid dates so it will filter out any bad dates.
> Sounds like someone put a character string in a date field. Does your
> database allow this? Like an ASAP or somthing?
> Leo
>
>
> Jaime Stuardo wrote:
> > Hi all...
> >
> > How should I pass datetime parameters to a report in order for it to work?
> > This is the current scenario:
> >
> > Dataset has, for example, this query:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN ? AND ?
> >
> > where FECHA is of DATETIME type in an Oracle Database.
> >
> > Both unnamed parameters are DateTime report parameters, which retrieve the
> > correct date values (using code embedded in the report)
> >
> > When I run the report, I get the typical error message when date format is
> > incorrect:
> > "a non-numeric character was found where a numeric was expected"
> >
> > It seems that report tries to pass the "-" of the date value to the unnamed
> > parameter.
> >
> > Formerly I had this query that made the report to work:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> >
> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> > format). But using this way I can have (and I already had) problems with
> > incompatibilities in Regional Settings where the Reporting Server is
> > installed. For example, using this latter way, I forced the date to have the
> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> > is installed, regional settings are different, so Report crashed because it
> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
> >
> > That's why I need to make it independent of regional settings of the target
> > server.
> >
> > Any way to manage this?
> > Thanks a lot in advance
> >
> > Jaime
> >
> >
>|||Have you tried CASTing the dates as Integers in the Oracel SQL query?
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:B960FCF6-7317-4197-8B76-55F79733BC71@.microsoft.com...
> Thanks Leo for answering...
> When using the Data tab and run the query, I have to enter the date this
> way:
> 30-apr-05 and query returns results.
> The date field in database is correct, since as I told, when I use
> TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
> not DateTime as it should be. I cannot use the parameter as String as I
> explained in my first post. In conclusion, the problem is only related to
> passing parameters between Reporting Services and Oracle database.
> Those DateTime parameters are calculated, for example, using this custom code:
> Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
> DateTime
> Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
> End Function
> Finally, I'm going to tell you that I could display parameters just before
> rendering the report (I'm using API to do it) and these are :
> CUATRIMESTRE=1
> AÑO=2005
> NUMERO_POLIZA=5506666
> FECHA_INICIO=5/1/2005 12:00:00 AM
> FECHA_TERMINO=8/31/2005 12:00:00 AM
> CUA_MESES=May - August
> CUATRIMESTRE, AÑO and NUMERO_POLIZA are parameters the user enters.
> FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
> CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
> FECHA_INICIO and FECHA_TERMINO.
> It's clear that Reporting Service is passing the datetime parameter formated
> in a manner that isn't accepted by Oracle. I thought date time would be
> passed as is, as a DATE datatype.
> Any further help would be greately appreciated,
> Thanks
> Jaime
> "cte25117@.centurytel.net" wrote:
>> Jaime
>> I believe this is an issue with the data being returned from the Query.
>> Not the rendering of the report.
>> If you run the report in the data tab and scroll to the bottom do you
>> get the same error?
>> You might want to take a look at the function Isdate(). Be advised this
>> will return valid dates so it will filter out any bad dates.
>> Sounds like someone put a character string in a date field. Does your
>> database allow this? Like an ASAP or somthing?
>> Leo
>>
>>
>> Jaime Stuardo wrote:
>> > Hi all...
>> >
>> > How should I pass datetime parameters to a report in order for it to work?
>> > This is the current scenario:
>> >
>> > Dataset has, for example, this query:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN ? AND ?
>> >
>> > where FECHA is of DATETIME type in an Oracle Database.
>> >
>> > Both unnamed parameters are DateTime report parameters, which retrieve the
>> > correct date values (using code embedded in the report)
>> >
>> > When I run the report, I get the typical error message when date format is
>> > incorrect:
>> > "a non-numeric character was found where a numeric was expected"
>> >
>> > It seems that report tries to pass the "-" of the date value to the unnamed
>> > parameter.
>> >
>> > Formerly I had this query that made the report to work:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
>> >
>> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
>> > format). But using this way I can have (and I already had) problems with
>> > incompatibilities in Regional Settings where the Reporting Server is
>> > installed. For example, using this latter way, I forced the date to have
>> > the
>> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
>> > is installed, regional settings are different, so Report crashed because it
>> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
>> >
>> > That's why I need to make it independent of regional settings of the target
>> > server.
>> >
>> > Any way to manage this?
>> > Thanks a lot in advance
>> >
>> > Jaime
>> >
>> >

Passing dates to ServerFilter from Access

Hello.
I have a solution written in Access. In order to filter a
report a date is passed the the serverfilter property.
e.g. MyFilterDate='01/28/2004'
This works fine on one machine but on another machine I
get an overflow error due to the fact that date is passed
in American Format.
Both machines appear configured with the same regional
settings, the VBA references are the same and anything I
have thought of have been the same.
They are both connected to the same SQL server.
Can anyone help?
If you pass in the date as 'yyyymmdd' it will never get confused.
Andrew J. Kelly SQL MVP
"Scamps" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
> Hello.
> I have a solution written in Access. In order to filter a
> report a date is passed the the serverfilter property.
> e.g. MyFilterDate='01/28/2004'
> This works fine on one machine but on another machine I
> get an overflow error due to the fact that date is passed
> in American Format.
> Both machines appear configured with the same regional
> settings, the VBA references are the same and anything I
> have thought of have been the same.
> They are both connected to the same SQL server.
> Can anyone help?
|||Thank you very much.
Problem appears solved immediately.
>--Original Message--
>If you pass in the date as 'yyyymmdd' it will never get
confused.
>--
>Andrew J. Kelly SQL MVP
>
>"Scamps" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
filter a[vbcol=seagreen]
passed[vbcol=seagreen]
I
>
>.
>

Passing a WHERE clause

I want to build a SQL WHERE (and possibly ORDER BY) clause
in a c# application and then pass them as parameters to a report that would
be used by a SQL Server stored procedure.
can anyone tell me how to do that?
Syntax for building it?
maybe a link to an example?
thanks
--
Lucas DargisYou have a very good sample available in samples. search for forms
authentication and open the custom security c# file and see the code you will
get good idea about how to write.
Amarnath
"akbikerboy" wrote:
> I want to build a SQL WHERE (and possibly ORDER BY) clause
> in a c# application and then pass them as parameters to a report that would
> be used by a SQL Server stored procedure.
> can anyone tell me how to do that?
> Syntax for building it?
> maybe a link to an example?
> thanks
> --
> Lucas Dargis|||could you be more specific?
Are you talking about MSDN or in VS?
I can't find what you are talking about
thanks
--
Lucas Dargis
"Amarnath" wrote:
> You have a very good sample available in samples. search for forms
> authentication and open the custom security c# file and see the code you will
> get good idea about how to write.
> Amarnath
> "akbikerboy" wrote:
> > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > in a c# application and then pass them as parameters to a report that would
> > be used by a SQL Server stored procedure.
> >
> > can anyone tell me how to do that?
> > Syntax for building it?
> > maybe a link to an example?
> >
> > thanks
> > --
> > Lucas Dargis|||sql server online help samples
Amarnath
"akbikerboy" wrote:
> could you be more specific?
> Are you talking about MSDN or in VS?
> I can't find what you are talking about
>
> thanks
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > You have a very good sample available in samples. search for forms
> > authentication and open the custom security c# file and see the code you will
> > get good idea about how to write.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > in a c# application and then pass them as parameters to a report that would
> > > be used by a SQL Server stored procedure.
> > >
> > > can anyone tell me how to do that?
> > > Syntax for building it?
> > > maybe a link to an example?
> > >
> > > thanks
> > > --
> > > Lucas Dargis|||I looked all over and i can't find what your are talking about. do you have a
link to it?
--
Lucas Dargis
"Amarnath" wrote:
> sql server online help samples
> Amarnath
> "akbikerboy" wrote:
> > could you be more specific?
> > Are you talking about MSDN or in VS?
> > I can't find what you are talking about
> >
> >
> > thanks
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > You have a very good sample available in samples. search for forms
> > > authentication and open the custom security c# file and see the code you will
> > > get good idea about how to write.
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > in a c# application and then pass them as parameters to a report that would
> > > > be used by a SQL Server stored procedure.
> > > >
> > > > can anyone tell me how to do that?
> > > > Syntax for building it?
> > > > maybe a link to an example?
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis|||Just go to search in online help and search for forms authentication and if
you have installed the samples, go to the samples folder and see the forms
authentication example code.
Amarnath
"akbikerboy" wrote:
> I looked all over and i can't find what your are talking about. do you have a
> link to it?
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > sql server online help samples
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > could you be more specific?
> > > Are you talking about MSDN or in VS?
> > > I can't find what you are talking about
> > >
> > >
> > > thanks
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > You have a very good sample available in samples. search for forms
> > > > authentication and open the custom security c# file and see the code you will
> > > > get good idea about how to write.
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > in a c# application and then pass them as parameters to a report that would
> > > > > be used by a SQL Server stored procedure.
> > > > >
> > > > > can anyone tell me how to do that?
> > > > > Syntax for building it?
> > > > > maybe a link to an example?
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis|||there are a million articles about forms authentication. and i don't see what
that has to do with passing a where clause from an application to RS...
thanks for trying though.
--
Lucas Dargis
"Amarnath" wrote:
> Just go to search in online help and search for forms authentication and if
> you have installed the samples, go to the samples folder and see the forms
> authentication example code.
> Amarnath
> "akbikerboy" wrote:
> > I looked all over and i can't find what your are talking about. do you have a
> > link to it?
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > sql server online help samples
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > could you be more specific?
> > > > Are you talking about MSDN or in VS?
> > > > I can't find what you are talking about
> > > >
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > You have a very good sample available in samples. search for forms
> > > > > authentication and open the custom security c# file and see the code you will
> > > > > get good idea about how to write.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > be used by a SQL Server stored procedure.
> > > > > >
> > > > > > can anyone tell me how to do that?
> > > > > > Syntax for building it?
> > > > > > maybe a link to an example?
> > > > > >
> > > > > > thanks
> > > > > > --
> > > > > > Lucas Dargis|||dude, I didn't ask you to type it on google to get million option. By the way
I was mentioning that if you have sample installed, you can have a look at
the sample code, I repeat the code, so that you will get some idea for
writing code in c# or VB.net.
Amarnath
"akbikerboy" wrote:
> there are a million articles about forms authentication. and i don't see what
> that has to do with passing a where clause from an application to RS...
> thanks for trying though.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Just go to search in online help and search for forms authentication and if
> > you have installed the samples, go to the samples folder and see the forms
> > authentication example code.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I looked all over and i can't find what your are talking about. do you have a
> > > link to it?
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > sql server online help samples
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > could you be more specific?
> > > > > Are you talking about MSDN or in VS?
> > > > > I can't find what you are talking about
> > > > >
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > You have a very good sample available in samples. search for forms
> > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > get good idea about how to write.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > be used by a SQL Server stored procedure.
> > > > > > >
> > > > > > > can anyone tell me how to do that?
> > > > > > > Syntax for building it?
> > > > > > > maybe a link to an example?
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis|||Ok, to guide you precisly,
If you have samples installed in your m/c, go to
C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
accordingly)
you can see a custom security solution file, click on that and open. search
for
"AuthenticationExtension.cs" file and click on view code and search for
"VerifyUser"
In that code you can see how it is defined for stored procedure using
commandtype
and parameter.add is used. instead of "@.username" build your where clause by
defining a variable and start adding all the conditions and pass it on using
parameter.add.
I think this should give some idea about how to write it.
Please let me know if you have any problems
Amarnath
"akbikerboy" wrote:
> there are a million articles about forms authentication. and i don't see what
> that has to do with passing a where clause from an application to RS...
> thanks for trying though.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Just go to search in online help and search for forms authentication and if
> > you have installed the samples, go to the samples folder and see the forms
> > authentication example code.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I looked all over and i can't find what your are talking about. do you have a
> > > link to it?
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > sql server online help samples
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > could you be more specific?
> > > > > Are you talking about MSDN or in VS?
> > > > > I can't find what you are talking about
> > > > >
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > You have a very good sample available in samples. search for forms
> > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > get good idea about how to write.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > be used by a SQL Server stored procedure.
> > > > > > >
> > > > > > > can anyone tell me how to do that?
> > > > > > > Syntax for building it?
> > > > > > > maybe a link to an example?
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis|||Well thanks for pointing that out precisely.
I guess I should have been more specific. I know how to create and pass a
parameter. Thatâ's not what I was asking. My question was how to write and
pass a 'where' clause specifically. What syntax to use.
For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
= 99874"
In that string, I have slashes, and quotes. When I try to pass this string
as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
that I know and nothing works.
Thanks again.
--
Lucas Dargis
"Amarnath" wrote:
> Ok, to guide you precisly,
> If you have samples installed in your m/c, go to
> C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> accordingly)
> you can see a custom security solution file, click on that and open. search
> for
> "AuthenticationExtension.cs" file and click on view code and search for
> "VerifyUser"
> In that code you can see how it is defined for stored procedure using
> commandtype
> and parameter.add is used. instead of "@.username" build your where clause by
> defining a variable and start adding all the conditions and pass it on using
> parameter.add.
> I think this should give some idea about how to write it.
> Please let me know if you have any problems
> Amarnath
>
> "akbikerboy" wrote:
> > there are a million articles about forms authentication. and i don't see what
> > that has to do with passing a where clause from an application to RS...
> >
> > thanks for trying though.
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > Just go to search in online help and search for forms authentication and if
> > > you have installed the samples, go to the samples folder and see the forms
> > > authentication example code.
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I looked all over and i can't find what your are talking about. do you have a
> > > > link to it?
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > sql server online help samples
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > could you be more specific?
> > > > > > Are you talking about MSDN or in VS?
> > > > > > I can't find what you are talking about
> > > > > >
> > > > > >
> > > > > > thanks
> > > > > > --
> > > > > > Lucas Dargis
> > > > > >
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > get good idea about how to write.
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > > "akbikerboy" wrote:
> > > > > > >
> > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > >
> > > > > > > > can anyone tell me how to do that?
> > > > > > > > Syntax for building it?
> > > > > > > > maybe a link to an example?
> > > > > > > >
> > > > > > > > thanks
> > > > > > > > --
> > > > > > > > Lucas Dargis|||Oh Ok, so you meant this. the same way, how you have used in the example,
with double quotes and single quotes. if doesnt work try concatenating some
thing like this. e.g "Where aa = " + " ' " + "01/01/2007" + " ' "
Let me know whether it is working ?
Amarnath
"akbikerboy" wrote:
> Well thanks for pointing that out precisely.
> I guess I should have been more specific. I know how to create and pass a
> parameter. Thatâ's not what I was asking. My question was how to write and
> pass a 'where' clause specifically. What syntax to use.
> For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
> = 99874"
> In that string, I have slashes, and quotes. When I try to pass this string
> as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
> that I know and nothing works.
> Thanks again.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Ok, to guide you precisly,
> >
> > If you have samples installed in your m/c, go to
> >
> > C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> > Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> > accordingly)
> >
> > you can see a custom security solution file, click on that and open. search
> > for
> > "AuthenticationExtension.cs" file and click on view code and search for
> > "VerifyUser"
> >
> > In that code you can see how it is defined for stored procedure using
> > commandtype
> > and parameter.add is used. instead of "@.username" build your where clause by
> > defining a variable and start adding all the conditions and pass it on using
> > parameter.add.
> >
> > I think this should give some idea about how to write it.
> > Please let me know if you have any problems
> >
> > Amarnath
> >
> >
> > "akbikerboy" wrote:
> >
> > > there are a million articles about forms authentication. and i don't see what
> > > that has to do with passing a where clause from an application to RS...
> > >
> > > thanks for trying though.
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Just go to search in online help and search for forms authentication and if
> > > > you have installed the samples, go to the samples folder and see the forms
> > > > authentication example code.
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > I looked all over and i can't find what your are talking about. do you have a
> > > > > link to it?
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > sql server online help samples
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > could you be more specific?
> > > > > > > Are you talking about MSDN or in VS?
> > > > > > > I can't find what you are talking about
> > > > > > >
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis
> > > > > > >
> > > > > > >
> > > > > > > "Amarnath" wrote:
> > > > > > >
> > > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > > get good idea about how to write.
> > > > > > > >
> > > > > > > > Amarnath
> > > > > > > >
> > > > > > > > "akbikerboy" wrote:
> > > > > > > >
> > > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > > >
> > > > > > > > > can anyone tell me how to do that?
> > > > > > > > > Syntax for building it?
> > > > > > > > > maybe a link to an example?
> > > > > > > > >
> > > > > > > > > thanks
> > > > > > > > > --
> > > > > > > > > Lucas Dargis|||so for the query i tried
select....
where @.where
parameter: LocationCode + "'" + LA + "'"
I am getting the following error
An error occurred whild executing the query
An expression of non-boolean type specified in a context where a condition
is expected, near @.where
error:4145
I am running the query in the report designed in the Data tab
thanks
--
Lucas Dargis
"akbikerboy" wrote:
> I want to build a SQL WHERE (and possibly ORDER BY) clause
> in a c# application and then pass them as parameters to a report that would
> be used by a SQL Server stored procedure.
> can anyone tell me how to do that?
> Syntax for building it?
> maybe a link to an example?
> thanks
> --
> Lucas Dargis|||For the query i use
SELECT...
WHERE @.where
parameter: declared as a string
LocationCode = + "'" + LA + "'"
and i got the following error:
An error occurred whild executing the query.
An expression of non-boolean type specified in a contex where a condition is
expected, near '@.where'.
Error: 4145
I am running the query in the Data tab in the Report Builder
thanks
--
Lucas Dargis
"Amarnath" wrote:
> Oh Ok, so you meant this. the same way, how you have used in the example,
> with double quotes and single quotes. if doesnt work try concatenating some
> thing like this. e.g "Where aa = " + " ' " + "01/01/2007" + " ' "
> Let me know whether it is working ?
> Amarnath
> "akbikerboy" wrote:
> > Well thanks for pointing that out precisely.
> >
> > I guess I should have been more specific. I know how to create and pass a
> > parameter. Thatâ's not what I was asking. My question was how to write and
> > pass a 'where' clause specifically. What syntax to use.
> >
> > For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
> > = 99874"
> >
> > In that string, I have slashes, and quotes. When I try to pass this string
> > as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
> > that I know and nothing works.
> >
> > Thanks again.
> >
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > Ok, to guide you precisly,
> > >
> > > If you have samples installed in your m/c, go to
> > >
> > > C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> > > Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> > > accordingly)
> > >
> > > you can see a custom security solution file, click on that and open. search
> > > for
> > > "AuthenticationExtension.cs" file and click on view code and search for
> > > "VerifyUser"
> > >
> > > In that code you can see how it is defined for stored procedure using
> > > commandtype
> > > and parameter.add is used. instead of "@.username" build your where clause by
> > > defining a variable and start adding all the conditions and pass it on using
> > > parameter.add.
> > >
> > > I think this should give some idea about how to write it.
> > > Please let me know if you have any problems
> > >
> > > Amarnath
> > >
> > >
> > > "akbikerboy" wrote:
> > >
> > > > there are a million articles about forms authentication. and i don't see what
> > > > that has to do with passing a where clause from an application to RS...
> > > >
> > > > thanks for trying though.
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Just go to search in online help and search for forms authentication and if
> > > > > you have installed the samples, go to the samples folder and see the forms
> > > > > authentication example code.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > I looked all over and i can't find what your are talking about. do you have a
> > > > > > link to it?
> > > > > > --
> > > > > > Lucas Dargis
> > > > > >
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > sql server online help samples
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > > "akbikerboy" wrote:
> > > > > > >
> > > > > > > > could you be more specific?
> > > > > > > > Are you talking about MSDN or in VS?
> > > > > > > > I can't find what you are talking about
> > > > > > > >
> > > > > > > >
> > > > > > > > thanks
> > > > > > > > --
> > > > > > > > Lucas Dargis
> > > > > > > >
> > > > > > > >
> > > > > > > > "Amarnath" wrote:
> > > > > > > >
> > > > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > > > get good idea about how to write.
> > > > > > > > >
> > > > > > > > > Amarnath
> > > > > > > > >
> > > > > > > > > "akbikerboy" wrote:
> > > > > > > > >
> > > > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > > > >
> > > > > > > > > > can anyone tell me how to do that?
> > > > > > > > > > Syntax for building it?
> > > > > > > > > > maybe a link to an example?
> > > > > > > > > >
> > > > > > > > > > thanks
> > > > > > > > > > --
> > > > > > > > > > Lucas Dargis|||By two way you can solve this.
1. use stored proc to concatenate all the "select", includng the values
which comes from parameter ie where clause
2. By directly giving in the data tab..
e.g I have taken some samples from adventure... it goes like this..
paste this code in the data tab it executes successfully..
declare @.s as nvarchar(1000)
set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
Person.Contact WHERE ' + @.w
exec sp_executesql @.s
the @.w is what I entered " as (Title = 'Mr.').
PS. "where", is the reserved word, so name @.where to some other name.
So basically what i meant here is to built using stored proc or use string
and concatenate with your parameter.
Amarnath
"akbikerboy" wrote:
> so for the query i tried
> select....
> where @.where
> parameter: LocationCode + "'" + LA + "'"
> I am getting the following error
> An error occurred whild executing the query
> An expression of non-boolean type specified in a context where a condition
> is expected, near @.where
> error:4145
> I am running the query in the report designed in the Data tab
> thanks
> --
> Lucas Dargis
>
> "akbikerboy" wrote:
> > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > in a c# application and then pass them as parameters to a report that would
> > be used by a SQL Server stored procedure.
> >
> > can anyone tell me how to do that?
> > Syntax for building it?
> > maybe a link to an example?
> >
> > thanks
> > --
> > Lucas Dargis|||AWESOME! very clever.
well your second solution is just what i was looking for, however, it brings
up another problem.
Since the query is now created in a variable, RS doesn't recognize the
fields in the layout tab. how do i reference the fields so i can create the
report?
thanks again
--
Lucas Dargis
"Amarnath" wrote:
> By two way you can solve this.
> 1. use stored proc to concatenate all the "select", includng the values
> which comes from parameter ie where clause
> 2. By directly giving in the data tab..
> e.g I have taken some samples from adventure... it goes like this..
> paste this code in the data tab it executes successfully..
> declare @.s as nvarchar(1000)
> set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
> Person.Contact WHERE ' + @.w
> exec sp_executesql @.s
> the @.w is what I entered " as (Title = 'Mr.').
> PS. "where", is the reserved word, so name @.where to some other name.
> So basically what i meant here is to built using stored proc or use string
> and concatenate with your parameter.
> Amarnath
>
> "akbikerboy" wrote:
> > so for the query i tried
> > select....
> > where @.where
> >
> > parameter: LocationCode + "'" + LA + "'"
> >
> > I am getting the following error
> >
> > An error occurred whild executing the query
> > An expression of non-boolean type specified in a context where a condition
> > is expected, near @.where
> >
> > error:4145
> >
> > I am running the query in the report designed in the Data tab
> >
> > thanks
> > --
> > Lucas Dargis
> >
> >
> > "akbikerboy" wrote:
> >
> > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > in a c# application and then pass them as parameters to a report that would
> > > be used by a SQL Server stored procedure.
> > >
> > > can anyone tell me how to do that?
> > > Syntax for building it?
> > > maybe a link to an example?
> > >
> > > thanks
> > > --
> > > Lucas Dargis|||So you can forget my last question. I reread your last post and figured it out.
i put the sql code you provided me into a stored proc and called that proc
while sending the Where Clause as a parameter and it worked PERFECTLY!!!
i can't thank you enough. i've been stuck on this for a few weeks.
Lucas Dargis
"akbikerboy" wrote:
> AWESOME! very clever.
> well your second solution is just what i was looking for, however, it brings
> up another problem.
> Since the query is now created in a variable, RS doesn't recognize the
> fields in the layout tab. how do i reference the fields so i can create the
> report?
> thanks again
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > By two way you can solve this.
> > 1. use stored proc to concatenate all the "select", includng the values
> > which comes from parameter ie where clause
> > 2. By directly giving in the data tab..
> > e.g I have taken some samples from adventure... it goes like this..
> >
> > paste this code in the data tab it executes successfully..
> >
> > declare @.s as nvarchar(1000)
> > set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
> > Person.Contact WHERE ' + @.w
> > exec sp_executesql @.s
> >
> > the @.w is what I entered " as (Title = 'Mr.').
> >
> > PS. "where", is the reserved word, so name @.where to some other name.
> > So basically what i meant here is to built using stored proc or use string
> > and concatenate with your parameter.
> >
> > Amarnath
> >
> >
> > "akbikerboy" wrote:
> >
> > > so for the query i tried
> > > select....
> > > where @.where
> > >
> > > parameter: LocationCode + "'" + LA + "'"
> > >
> > > I am getting the following error
> > >
> > > An error occurred whild executing the query
> > > An expression of non-boolean type specified in a context where a condition
> > > is expected, near @.where
> > >
> > > error:4145
> > >
> > > I am running the query in the report designed in the Data tab
> > >
> > > thanks
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > in a c# application and then pass them as parameters to a report that would
> > > > be used by a SQL Server stored procedure.
> > > >
> > > > can anyone tell me how to do that?
> > > > Syntax for building it?
> > > > maybe a link to an example?
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis

Passing "ORDER BY" parameter causes error

Hi everyone,

I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:

CREATE PROCEDURE GetFromTable
(
@.SortOn varchar(20)
)
AS
SELECT *
FROM Table
ORDER BY @.SortOn
GO

This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?

Thanks in advance,

PietWhy?

This is bad on so many levels...why not go dynamic crazy?

USE Northwind
GO

CREATE PROC mySproc99
@.TABLE_NAME sysname, @.WHERE varchar(2000), @.ORDERBY varchar(2000)
AS
BEGIN
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM ' + @.TABLE_NAME
+ ' WHERE ' + @.WHERE
+ ' ORDER BY ' + @.ORDERBY
EXEC(@.sql)
END
GO

EXEC mySproc99 'Orders','OrderId > 0','ShippedDate'
GO

DROP PROC mySproc99
GO|||Brilliant code! Henceforth, the only stored proc in my databases shall be this "one-size-fits-all" piece of code. However, I think I may make it even more universal like this:

USE Northwind
GO

CREATE PROC mySproc99
@.SQLStatement(4000)
AS
BEGIN
EXEC(@.SQLStatement)
END
GO

EXEC mySproc99 'select * from Orders where OrderId > 0 order by ShippedDate'
GO

DROP PROC mySproc99
GO

Much more functional and versatile, wouldn't you agree? The only stored procedure you will ever need!|||blindman,

Are you for reals?

Henceforth, the only stored proc in my databases shall be this "one-size-fits-all" piece of code.

Or, are you being a bit facetious?

Thanks for the help guys. Going dynamic makes things a bit easier for sure.

Piet|||I'm suspecting the BRETT was being facetious...|||What meaning does "This is bad on so many levels" carry?|||Piet:

When Brett wrote "This is bad on so many levels..." he was trying to convey the idea that dynamic code is potentially a bad thing, and when used in this way (to dynamically change the order of the results), it is worse than usual.

Dynamic code opens up all kinds of possibilities for problems like SQL Injection, etc. If the user has any control over what string gets passed into your dynamic SQL, you've basically given them the keys to the kingdom as far as what they can do.

Both Brett and Blindman were attempting to show some of the problems that can arise from dynamic code. While there are a few uses for dynamic code (and heaven knows I seem to post a lot of it), they are really few and far between for production code. Dynamic SQL belongs in the DBA's bag of tricks, but you need to be very stingy about using it... It should only be as a last resort, and no part of the dynamic SQL should EVER be controlled by the end user.

-PatP|||You should think about why you are asking the database to custom-order results in the first place. Ordering the results may stray into the realm of data-presentation, and would then be better left to the interface to handle.

What interface, dare I ask, are you using?|||Many years ago (8?) I was experimenting with procedure subscripts in 6.5, and actually employed this rarely used feature in one of my apps. It may sound inefficient, but it is by far much better than resorting to dynamic SQL:

create proc myproc;1 as
select * from pubs.dbo.authors order by 1
go
create proc myproc;2 as
select * from pubs.dbo.authors order by 2
go
create proc myproc;3 as
select * from pubs.dbo.authors order by 3
go
create proc myproc;4 as
select * from pubs.dbo.authors order by 4
go

Then all your FE needs to do is to make determination which subscript to append to the call of the proc to get the results in the specific order.|||Blindman,

Good question and I think you're right that I could probably take care of the sorting in a Dataview. I am using a repeater in a web form app using c#.

Thanks for the reminder.

Piet

pasing a set of records to SP

hi
I need to send a set of records to a SP in order to make some comparisos and
calculations and return back from the SP the same records but modified
What is the best (faster,optimal) way to send the records from my client App
via internet to the SQLDB in order to the SP reading those records?
I just discover that a SP does not accept arrays as input parameters.
thks
kenhttp://www.sommarskog.se/arrays-in-sql.html
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:F69119F4-7020-4B87-9BCA-414F3A446AFC@.microsoft.com...
> hi
> I need to send a set of records to a SP in order to make some comparisos
and
> calculations and return back from the SP the same records but modified
> What is the best (faster,optimal) way to send the records from my client
App
> via internet to the SQLDB in order to the SP reading those records?
>
> I just discover that a SP does not accept arrays as input parameters.
> thks
> ken
>