Creating an "ALL" Parameter Value

I am trying to create an "All" parameter. I created a stored procedure that says:

Code Snippet

CREATE PROCEDURE dbo.Testing123

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD
Order by ID
GO

Then I createded a report parameter and set the default to All

I also created a filter that sets the textbox vaule to the report parameter.

In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?

In the query that returns the data for the report, I suspect you're doing something like:

WHERE id = @.ID

What you would need to do is:

WHERE id = @.ID OR @.ID IS NULL

I think that you should be setting your default value to NULL instead of "ALL", that would remove the need for your filter.

|||

Is this what you mean?

Code Snippet

CREATE PROCEDURE dbo.Testing123

@.id char

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD

Where ID = @.id or @.id is NULL

Order by ID

Should this make it work?

I am also getting this error: "The report parameter 'pid' has a DefaultValue or ValidValue that depends on the report parameter "pid" Forward dependencies are not valid."

|||

No, I meant for you to put it in the query that is returning the data for your report, not for the parameter.

I am assuming that what you have currently is a parameter as a drop down box where they select the id from the list generated by the query that you have posted above. Then, the user presses "view Report" and a report for that id is executed with data filled in by some other query, that currently has

Where ID = @.id

to return just the data for the id you have selected.

If you add

or @.id is NULL

to the where clause, it will detect if the user has selected ALL and not filter the results.

Maybe I have misunderstood what you're trying to do?

|||You were right! Thank you so much for your help with this

Creating a VIEW

I'm trying to create a view in EM.
SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
ORDER BY dbo.wf_styles.code
The error message I get was "Incorrect syntax near '100'". I didn't put "TOP
100 PERCENT" there (not sure what it does...) but EM puts it back after I
delete it. How can I get this to work?
Thanks!!
But theWill,
The ORDER BY clause cannot be used in a view definition without the TOP
clause listed. Do you really want to include the ORDER BY in the view
definition? Could cause unexpected results when querying the view and using
a different ORDER BY clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||Will,
Also move
AND dbo.wf_bom.raw_type = 'F'
to the WHERE clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||(a) write your VIEW in Query Analyzer, not Enterprise Mangler
(b) update your database to not be in 6.5 compatibility mode, where TOP
wasn't supported.
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
No - this is the unpreserved table in an outer join. Doing this changes the
semantics of the query.|||I need that to be part of the JOIN condition because it's a LEFT JOIN.
"Jerry Spivey" wrote:

> Will,
> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok, I took out the ORDER BY, now I'm getting:
Error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_styles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_bom'.
Query:
SELECT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
Thanks.
"Jerry Spivey" wrote:

> Will,
> The ORDER BY clause cannot be used in a view definition without the TOP
> clause listed. Do you really want to include the ORDER BY in the view
> definition? Could cause unexpected results when querying the view and usi
ng
> a different ORDER BY clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok...that I've never seen before. Would you mind elaborating on that for my
understanding?
Thanks Scott.
Jerry
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> No - this is the unpreserved table in an outer join. Doing this changes
> the semantics of the query.
>|||wf_styles and wf_bom has a 1-to-many relationship. One line in wf_styles and
many wf_bom lines for each item (bom = Bill of Materials, what each item
contains). I only want to join where the wf_bom.raw_type = 'F'. If it's not
F, return NULL. If you put it in the WHERE condition, the results returned
would only be items that have F, which is not really what I wanted.
Basically, I want a list of all the styles in my table. If I have the amount
of fabric yards that style uses, display that too. That's why I need the LEF
T
JOIN. If you were to put that clause in the WHERE, it would be "Display all
styles in the table and the fabric yards it uses". But that won't work for m
e
since I have some styles where I don't have the fabric yards. Does that
explain it?
"Jerry Spivey" wrote:

> Ok...that I've never seen before. Would you mind elaborating on that for
my
> understanding?
> Thanks Scott.
> Jerry
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
>
>

creating a user with dbo access

Hi,

I need to create a user with dbo access to a specific database using sql. I created a login and added a user to the login. How do i grant dbo privilege to this user (using sql)? Is there any system stored procedure for this? Please help.

Thanks in advance

Hi. Try

sp_changeowner [@.loginame=]'login'

Changes the owner of the current database.

sql

Creating a primary key as a non clustered index

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

It appears to me from the code above that you actually created the clustered index on the Id field.|||

As rottengeek noticed, you are creating the clustered index on column [Id], but even if you create it on column [code], does not expect any specific order if you are not using the "order by" clause in your "select" statement. That is the only way to assure a specific order.

Quaere Verum - Clustered Index Scans - Part I

http://www.sqlmag.com/articles/index.cfm?articleid=92886&

Quaere Verum - Clustered Index Scans - Part II

http://www.sqlmag.com/articles/index.cfm?articleid=92887&

Quaere Verum - Clustered Index Scans - Part III

http://www.sqlmag.com/articles/index.cfm?articleid=92888&

AMB

|||

you are correct, so i have modified it to have the clustered index on the Code field.

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Code]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

create\alter sp in schema privilage

I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?Gal (Gal@.discussions.microsoft.com) writes:
> I need to let my user no ddl changes in dbo schema, only
> creaete(new)/update permision in MySchema schema.
It's a little unclear what exactly what you want to permit in MySchema,
but to give them all, do
GRANT CONTROL ON SCHEMA::MySchema TO youruser
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

create view

CREATE VIEW dbo.viewNeedPrices
AS
SELECT equityID, tickerRealTick
FROM dbo.equities
WHERE (equityID NOT IN
(SELECT equityID
FROM prices
WHERE priceDate = '1/1/2004'))
This view works.
I want to be able to do this without hardcoding the date.
I tried using '?'. DIdn't work.
Any way to do this?Hi Won,
On SQL Server 2000 you can use a table valued user defined function:
CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
RETURNS TABLE
AS
RETURN
SELECT equityID, tickerRealTick
FROM dbo.equities
WHERE (equityID NOT IN
(SELECT equityID
FROM prices
WHERE priceDate = @.priceDate))
Jacco Schalkwijk
SQL Server MVP
"Won Lee" <noemail@.nospam.com> wrote in message
news:%23qvAkpc5DHA.2300@.TK2MSFTNGP10.phx.gbl...
> CREATE VIEW dbo.viewNeedPrices
> AS
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = '1/1/2004'))
> This view works.
> I want to be able to do this without hardcoding the date.
> I tried using '?'. DIdn't work.
> Any way to do this?
>|||Jacco Schalkwijk wrote:
> Hi Won,
> On SQL Server 2000 you can use a table valued user defined function:
> CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
> RETURNS TABLE
> AS
> RETURN
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = @.priceDate))
>
Thanks will give this a try.|||Jacco Schalkwijk wrote:
> Hi Won,
> On SQL Server 2000 you can use a table valued user defined function:
> CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
> RETURNS TABLE
> AS
> RETURN
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = @.priceDate))
>
Thanks. Worked like a charm.
Won

create view

CREATE VIEW dbo.viewNeedPrices
AS
SELECT equityID, tickerRealTick
FROM dbo.equities
WHERE (equityID NOT IN
(SELECT equityID
FROM prices
WHERE priceDate = '1/1/2004'))
This view works.
I want to be able to do this without hardcoding the date.
I tried using '?'. DIdn't work.
Any way to do this?Hi Won,
On SQL Server 2000 you can use a table valued user defined function:
CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
RETURNS TABLE
AS
RETURN
SELECT equityID, tickerRealTick
FROM dbo.equities
WHERE (equityID NOT IN
(SELECT equityID
FROM prices
WHERE priceDate = @.priceDate))
Jacco Schalkwijk
SQL Server MVP
"Won Lee" <noemail@.nospam.com> wrote in message
news:%23qvAkpc5DHA.2300@.TK2MSFTNGP10.phx.gbl...

quote:

---

> CREATE VIEW dbo.viewNeedPrices
> AS
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = '1/1/2004'))
> This view works.
> I want to be able to do this without hardcoding the date.
> I tried using '?'. DIdn't work.
> Any way to do this?
>

---

|||Jacco Schalkwijk wrote:

quote:

---

> Hi Won,
> On SQL Server 2000 you can use a table valued user defined function:
> CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
> RETURNS TABLE
> AS
> RETURN
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = @.priceDate))
>

---

Thanks will give this a try.|||Jacco Schalkwijk wrote:

quote:

---

> Hi Won,
> On SQL Server 2000 you can use a table valued user defined function:
> CREATE FUNCTION dbo.udfNeedPrices(@.priceDate DATETIME)
> RETURNS TABLE
> AS
> RETURN
> SELECT equityID, tickerRealTick
> FROM dbo.equities
> WHERE (equityID NOT IN
> (SELECT equityID
> FROM prices
> WHERE priceDate = @.priceDate))
>

---

Thanks. Worked like a charm.
Won