query

Hi, I have a query to build a returned record as and order by Name:
ID, Name
5 A
1 B
3 C '?
4 D
2 E
Now, I want to add a parameter (for example ID=3) to the query (or somehow),
so the returned record only return:
ID, Name
4 D
2 E
How to do that? Thanks.Please post the text of your query, then I will have my worker bees make the
changes you request.|||Try,
select [id], [name]
from t1
where [id] > 3
order by [name]
AMB
"js" wrote:

> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or somehow
),
> so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Add a WHERE clause to your query. It is possible to create a stored
procedure which accepts a parameter. The stored procedure would return data
just as your query does.
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or
> somehow), so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Thanks...
Add a WHERE clause(where [id] > 3) to query will filter out (2, E)?
select [id], [name]
from t1
where [id] > 3
order by [name]
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O1uUovATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Add a WHERE clause to your query. It is possible to create a stored
> procedure which accepts a parameter. The stored procedure would return
> data just as your query does.
> --
> Keith
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||One solution would be:
Select T.Id, T.Name
From Table As T
Where Ascii(T.Name) > Ascii('C')
Another would be
Select T.Id, T.Name
From Table As T
Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
Of course, by doing it this way, you'll likely get an index or table scan.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
> Hi, I have a query to build a returned record as and order by Name:
> ID, Name
> 5 A
> 1 B
> 3 C '?
> 4 D
> 2 E
>
> Now, I want to add a parameter (for example ID=3) to the query (or somehow
),
> so the returned record only return:
> ID, Name
> 4 D
> 2 E
>
> How to do that? Thanks.
>
>|||Actually, SQL as I just discovered, SQL is smart enough to do this sort of
analysis on strings for you. So you should be able to do this:
Select T.Id, T.Name
From Table As T
Where T.Name > 'C'
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23CLAj9ATFHA.3036@.TK2MSFTNGP10.phx.gbl...
> One solution would be:
> Select T.Id, T.Name
> From Table As T
> Where Ascii(T.Name) > Ascii('C')
> Another would be
> Select T.Id, T.Name
> From Table As T
> Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
> Of course, by doing it this way, you'll likely get an index or table scan.
>
> Thomas
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||Thanks Thomas,
I don't know 'C' but only "ID=3"

Query:
select [id], [name] from tb1
union
select [id], [name] from tb2
union
select [id], [name] from tb3
ORDER BY [name]
Output:
How to return this base the previous query:
Can I use a cursor in sql:
dim rs
sql0= "DECLARE TBs_Cursor CURSOR FOR "
sql1 = "select [id], [name] from tb1 union select [id], [name] from tb2
union select [id], [name] from tb3 ORDER BY [name]"
sql2 = sql0 + sql1 + 'OPEN Cursor, Loop if ID < 3, then delete, and return
the records'
rs.open sql2,...
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23CLAj9ATFHA.3036@.TK2MSFTNGP10.phx.gbl...
> One solution would be:
> Select T.Id, T.Name
> From Table As T
> Where Ascii(T.Name) > Ascii('C')
> Another would be
> Select T.Id, T.Name
> From Table As T
> Where Cast(T.Name As Binary(1)) > Cast('C' As Binary(1))
> Of course, by doing it this way, you'll likely get an index or table scan.
>
> Thomas
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:e1O1SpATFHA.3056@.TK2MSFTNGP14.phx.gbl...
>|||On Thu, 28 Apr 2005 11:52:44 -0400, "js" <js@.someone@.hotmail.com> wrote:

>Hi, I have a query to build a returned record as and order by Name:
>ID, Name
>5 A
>1 B
>3 C '?
>4 D
>2 E
>
>Now, I want to add a parameter (for example ID=3) to the query (or somehow)
,
>so the returned record only return:
>ID, Name
>4 D
>2 E
>
>How to do that? Thanks.
>
Hi js,
Try if this does what you want:
SELECT ID, Name
FROM MyTable
WHERE Name > (SELECT Name
FROM MyTable
WHERE Id = 3)
ORDER BY Name
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> I don't know 'C' but only "ID=3"
Not sure what you mean here.
Thomas

Queried Parameter Null Values

Hello,

For queried parameters, I need to have the option of using a null value as well. Is there a way to include a null value, or do I have to include it in the query that is returned?

Thanks.

Use DBNull.Value in .NET as the parameter value.

If this does not help you, please clarify your question.

|||

I have a parameter that pulls back data to it. But, this field should be optional, so the user should also be able to select null as another value. How do I allow the user to select null? I can't figure this out... By the way, I tried setting the default value to System.DBNull.Value, and that didn't work... nothing returns, but I did try the query and the query did work. For some reason, this is with RS...

|||

What are you using to send the command to the database? An ObjectDataSource, custom ADO.NET, etc. If you can tell us what you're using, how the users are currently selecting values, etc, it would help.

|||

I'm using a Reporting Services DataSet, standard report... The query works fine, and it has been tested. I can't figure out how to send a null value to the stored procedure parameter. That is what the problem is.

|||

Hello,

Here is the answer that I've found: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1789337&SiteID=1

QA - Debugger

How do I input a blank but not NULL value into a varchar parameter during
sproc debugging?
It seems that no matter I typed ('') / '' or leave it blank. It still not a
value that I wanted for the params.
LeonardP
Have you tried initializing the parameter to '' when it is declared? If no
value is provided, it uses the '' for it's value.
Ex: @.P1 varchar(10) = '', @.P2 varchar(5), etc...
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:O$dgcLpTEHA.164@.TK2MSFTNGP12.phx.gbl...
> How do I input a blank but not NULL value into a varchar parameter during
> sproc debugging?
> It seems that no matter I typed ('') / '' or leave it blank. It still not
a
> value that I wanted for the params.
> LeonardP
>

q; sp_executesql and speed

Hello,
I am using sp_executesql this to pass parameter to sql string and I am
seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
done by another process. When it comes to speed and deadlock, would you
recomand not using sp_executesql?Hi
I don't think you blocking is caused by sp_executesql. Look at what you are
executing and make sure that it is properly optimized and the transactions
are short.
John
"JIM.H." wrote:
> Hello,
> I am using sp_executesql this to pass parameter to sql string and I am
> seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
> done by another process. When it comes to speed and deadlock, would you
> recomand not using sp_executesql?
>

q; sp_executesql and speed

Hello,
I am using sp_executesql this to pass parameter to sql string and I am
seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
done by another process. When it comes to speed and deadlock, would you
recomand not using sp_executesql?Hi
I don't think you blocking is caused by sp_executesql. Look at what you are
executing and make sure that it is properly optimized and the transactions
are short.
John
"JIM.H." wrote:

> Hello,
> I am using sp_executesql this to pass parameter to sql string and I am
> seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
> done by another process. When it comes to speed and deadlock, would you
> recomand not using sp_executesql?
>

q; How to write this function

How can I write a function that accepts a data as a parameter and if the date
is
1. Last Monday in May or
2. First Monday in September or
3. 4th Tuesday in November
returns true otherwise returns false.The best answer, if these are particular dates known ahead of time, is
a calendar table. But brute force works too.
declare @.d datetime
set @.d = '28 Nov 2006'
SELECT CASE WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 5
AND DATEPART(day,@.d) between 25 and 31
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 9
AND DATEPART(day,@.d) between 1 and 7
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Tuesday'
AND DATEPART(month,@.d) = 11
AND DATEPART(day,@.d) between 22 and 28
THEN 'True'
ELSE 'False'
END as Answer
Roy Harvey
Beacon Falls, CT
On Wed, 27 Dec 2006 04:43:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>How can I write a function that accepts a data as a parameter and if the date
>is
>1. Last Monday in May or
>2. First Monday in September or
>3. 4th Tuesday in November
>returns true otherwise returns false.|||JIM
Take a look at Steve Kass's function
create function dbo.NthWeekDay(
@.first datetime, -- First of the month of interest (no time part)
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(weekday,@.result))%7
end
go
-- Find the 5th Thursday of August, 2002
select dbo.NthWeekDay('2002/08/01',5,5) as D
select datename(weekday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWeekDay('2002/08/01',5,5) as D
) X
go
drop function NthWeekDay
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FED1D4FF-5F9E-414B-B115-2118EE32F4EC@.microsoft.com...
> How can I write a function that accepts a data as a parameter and if the
> date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>|||On Wed, 27 Dec 2006 07:18:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Thank you very much for your help, last one should be 4th Thursday (sorry my
>mistake), how would this change the code?
Well the fourth Thursday would be a lot like the fourth Tuesday,
wouldn't it? Just copy the day range from that one.
Roy|||Wow, I've been seeing homework assignments getting posted for 20 years now.
"JIM.H." wrote:
> How can I write a function that accepts a data as a parameter and if the date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>

Q: sub-report visible/invisible

Hello,
I have a parameter and may sub-reports. I want to make one sub-report be
visible or invisible based one of the parameter true or false. Can I do this?answered in your other post, but answer is yes.
from other thread -
Yes that should work fine, right click on the greyed out subreport on your
master report, go to properties and visibility and stick the expression in
the box.
=iif(parameters!name.value = "true",false, true)
basically says "if parameter is true then show else don't show."
Greg
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:DB8F757A-0E83-4665-8205-04A51AF539BB@.microsoft.com...
> Hello,
> I have a parameter and may sub-reports. I want to make one sub-report be
> visible or invisible based one of the parameter true or false. Can I do
> this?
>

Q: Report Parameter dropdown

Hello,
I need to create a MyPar parameter dropdown list, that is why I created a
new dataset that gets data through a stored procedure. I also add @.MyPar into
the query string of MyMainDataSet . I defined this parameter in Parameter
screen for both available and default values.
When I run the report, I get the following error.
Query execution failed for data set MyMainDataSet.
Must declare the variable @.MyPar
What is problem?You may have to update the data set directly.. Ensure you are using the
correct case for everything...
right click the ellipsis on the data tab after selecting the data set, go to
the parameters tab, and manually add the parameter...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:058B7DC5-6B26-48CC-8F88-6B6E41BAFC59@.microsoft.com...
> Hello,
> I need to create a MyPar parameter dropdown list, that is why I created a
> new dataset that gets data through a stored procedure. I also add @.MyPar
> into
> the query string of MyMainDataSet . I defined this parameter in Parameter
> screen for both available and default values.
> When I run the report, I get the following error.
> Query execution failed for data set MyMainDataSet.
> Must declare the variable @.MyPar
> What is problem?
>