please help me to cearte this stored procedure

Hi ,

I want to make a report of records of a table, there is abut 15 fields that this report based on them , so we need a select query like this

Select f1,f2from Table1where f3=@.f3and f4=@.f4 and ….and f17=@.f17

-- f1 = field1 and ….

The problem is sometimes @.fs are empty, for example if @.f4was empty so "and f4=@.f4" should be excluded from the select query .(and it means there is no limitation for f4 field)

I know, probably I couldn't explain my purpose very well, but I hope somebody kindly try to understand it .

how can I perform that in a stored procedure?

Please help me

Thank you

try it like this:

SELECT f1,f2FROM Table1WHERE (f3=@.f3OR @.f3ISNULL)AND (f4=@.f4OR @.f4ISNULL)AND ….AND (f17=@.f17OR @.f17ISNULL)

|||

Is in your example @.f4 empty or null? I think null, so I created the following query for you:

Select

f1from table1where f1= @.f1and(f4= @.f4or @.f4isnull)

This query selects all the records where f1 matches @.f1 and f4 matches @.f4 or @.f4 is null (and will be ignored then).

I hope this helps

Richard

|||

mbanavige & richardsoeteman.net thank you very, very much!.

|||

Hi

Assume there are 3 tables like these:

Table0

Primary key

Name

1

Name1

2

Name2

3

Name3

Table 1:

Foreign key

Column1

1

Data1

2

Data1

Table2:

Foreign key

Column2

2

Data2

3

Data2

And there are 2 parameters that they may be null: @.Data1 and @.Data2

I need a select query that

-selects "Name2" from Table0 if:

@.Data1="Data1"

@.Data2="Data2"

-selects "Name1, Name2" from Table0 if:

@.Data1="Data1"

@.Data2=null

-selects "Name2, Name3" from Table0 if:

@.Data1=null

@.Data2="Data2"

And selects "Name1, Name2, Name3" from Table0 if both of@.Data1 &@.Data2 wasnull.

I know I did not explain very well again but it's the final step of my project and I really need help. So please help me again

Thanks,

|||

Same concept:

Read this post:http://forums.asp.net/thread/1440706.aspx

|||

thank you Mike,

What about parameters that areint ordecimal and we want to ignore them , they can not benull ,they can be 0.

thank you Mike,

|||

You can use the same concept

Select

f1from table1where(f1= @.f1or @.f1=0)

|||Thank you Richard,

Please help me populate my date fields

Hi,

I have the following table:

CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO

The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:

Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as xupdate orders set orderdateonly=convert(varchar,orderdate,105),order Dayname=datename(dw,orderdate),orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||populate only orderDate in this

CREATE TABLE [dbo].[Orders] (
[OrdID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[OrderDateONLY] AS ([orderdate]) ,
[OrderDayName] AS (datename(weekday,[orderdate])) ,
[OrderMonth] AS (datename(month,[orderdate]))
.....
)|||harsal_in,

Thanks for the reply...i think i'm very close now except for one problem...when I run the statement i get an error message saying:

"The conversion of a char datatype to datetime datatype resulted in an out of range datetime value"

The problem seems to be with the "orderdateonly=convert(varchar,orderdate,105)" but I can't figure out :confused:|||it works very well on my machine.
anyways try this:
update orders set orderdateonly=convert(datetime,convert(varchar,ord erdate,105)),orderDayname=datename(dw,orderdate),o rderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||harshal_in,

I wonder what's wrong at my end becuase I am still getting the same error. I have SP1 and could this be the problem? I'll update to SP3 but for now is there a solution.

Thanks.|||Format 120 (or 121 Brett...) are better for date conversion because they are interpreted unambiguously by SQL Server.

update orders
set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
orderDayname=datename(dw,orderdate),
orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),
orderweekofyear=datename(wk,orderdate)

...but the problem with your design here is that you will need to ensure that anytime the orderdate is modified that all the other columns are updated as well. I recommend that instead of having columns to store these values directly you should create them as calculated fields using the above formulas. This way, they will automatically be synchronized with the orderdate field.|||Unless it's a warehouse...or for performance reasons...which I can't see it...

NEVER store derived data....

What's the reason..

If you store derived data, then you get caught in the trap of making sure the derived data is TRUE to the source...all the time...

Which means an additional process..

This is OLTP, right?

in OLAP, it done once and never changes, so it is TRUE at the time of the derivation

OLTP is fluid, and alway in a state of flux...(like the capacitor :D )

So why?sql

please help me about list of table

i need to give list all table in my database plaese give me qurity?

thanks a lot

SELECT [name] FROM [sysobjects] WHERE TYPE = 'U'|||SELECT * FROM sysobjects
WHERE type = 'u'
ORDER BY [NAME]|||SELECT * will give you ever column but Its a way better practice to add in every column name that you have. And only use the ones that you need.
|||Rather than select data out of the sysobjects table, which is notguaranteed to be forwards/backwards compatible with different SQLServer versions, I encourage you to use the INFORMATION_SCHEMA.TABLESview, which is supposed to work with each SQL Server version.

SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'

please help me about build new table

please help me

i need for example

when my user clicke in the button a new table build in my sql database

please help me

Try the links below for more info on create table statement for SQL Server 2000/2005. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp

http://msdn2.microsoft.com/en-us/library/ms174979.aspx

sql

Please help me

i have 2 tables like yarn_fpdt_in and yarn_fpdt_out the yarn_fpdt_in contain barcode,pallet,qty and yarn_fpdt_out contain same like 1st table like following

SELECT * FROM YARN_FPDT_IN;

BarcodePalletGrossWtQty
702171132900
70717917757599
70718316752599
7071959752599

SELECT * FROM YARN_FPDT_out;

BarcodePalletGrossWtQty
702111132900
702123132400
20705611765725
2070653665620
20707713648603
2070815752599

and question is

please create one another table like yarn_fpdt_result and display the result like this

BarcodePalletGrossWtQty
7021331322200
20705611765725
2070653665620
20707713648603
2070815752599
70717917757599
70718316752599
7071959752599

ok

its very urgent plz help me

Quote:

Originally Posted by asvinalbert

i have 2 tables like yarn_fpdt_in and yarn_fpdt_out the yarn_fpdt_in contain barcode,pallet,qty and yarn_fpdt_out contain same like 1st table like following

SELECT * FROM YARN_FPDT_IN;

BarcodePalletGrossWtQty
702171132900
70717917757599
70718316752599
7071959752599

SELECT * FROM YARN_FPDT_out;

BarcodePalletGrossWtQty
702111132900
702123132400
20705611765725
2070653665620
20707713648603
2070815752599

and question is

please create one another table like yarn_fpdt_result and display the result like this

BarcodePalletGrossWtQty
7021331322200
20705611765725
2070653665620
20707713648603
2070815752599
70717917757599
70718316752599
7071959752599

ok

its very urgent plz help me

Use UNION

Select * from YARN_FPDT_IN
UNION
Select * from YARN_FPDT_out|||As you have posted a question in the Article section it is being moved to SQL Server Forum

MODERATOR

Please help Index slowed down our production system

Hi all,
I have a query connecting users to my products by and user groups and
today I added a nonclustered index to my table the one that maps users
to user groups (added a nonclusted index to both userID and UserGroups
in ASC order). About 6 hours later our CPU usage went from .01 - .10%
to 100+%? So I removed it and disable some parts of my SQL to
improve the speed. What's happening here? Please help? Would
removing the index change my table back to it's previous index'
please helpppppppppppppppp...
Thx
MStill UPDATE STATISTICS.
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>|||Can you show us the query + info about the indexes?
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>

Please help in solving a query..

Hello,

I need help in solving a task of my project. In my project there is a table where the student fill 6 preference of branch which he would like to join. The table look like this.

Appl_no pref1 pref2 pref3 pref4 pref5 pref6

-

125 5 4 1 6 8 2

126 2 3 4 1 7 6

127 5 2 1 6 4 3

128 2 3 5 7 1 4

The preference table looks like this:

Pref1 Branch

-

1 EEE

2 ECE

3 MECH

4 BT

5 IT

6 CIVIL

7 CHEM

8 ARCH

:

:

23 etc

I need the table which displays the preferences of students like this..

Appl_no EEE ECE MECH BT IT CIVIL CHEM ARCH

125 3 6 2 1 4 5

-

126 4 1 2 3 6 5

127 3 2 6 5 1 4

--

128 5 1 2 6 3 4

Please help me......

Regards

Ram

I try to create the query by unpivot,pivot and CTE, hope this help.

I assume that pref1 - pref6' value is unique for each Appl_no,
therefore, the value of Appl_no 128/pref6 change from 5 to 4.

Code Snippet

-- CREATE TABLE [t1565585a]
CREATE TABLE [dbo].[t1565585a]
(
[Appl_no] [int] NOT NULL,
[pref1] [int] NOT NULL,
[pref2] [int] NOT NULL,
[pref3] [int] NOT NULL,
[pref4] [int] NOT NULL,
[pref5] [int] NOT NULL,
[pref6] [int] NOT NULL,
CONSTRAINT [PK_t1565585a] PRIMARY KEY CLUSTERED
(
[Appl_no] ASC
)
) ON [PRIMARY]

insert into dbo.t1565585a values (125,5,4,1,6,8,2);
insert into dbo.t1565585a values (126,2,3,4,1,7,6);
insert into dbo.t1565585a values (127,5,2,1,6,4,3);
insert into dbo.t1565585a values (128,2,3,5,7,1,4);

-- CREATE TABLE [t1565585b]
CREATE TABLE [dbo].[t1565585b]
(
[Branch_no] [int] NOT NULL,
[Branch] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_t1565585b] PRIMARY KEY CLUSTERED
(
[Branch_no] ASC
)
) ON [PRIMARY]

insert into dbo.t1565585b values (1, 'EEE');
insert into dbo.t1565585b values (2, 'ECE');
insert into dbo.t1565585b values (3, 'MECH');
insert into dbo.t1565585b values (4, 'BT');
insert into dbo.t1565585b values (5, 'IT');
insert into dbo.t1565585b values (6, 'CIVIL');
insert into dbo.t1565585b values (7, 'CHEM');
insert into dbo.t1565585b values (8, 'ARCH');

-- execute Query
with
-- CTE: [ApplPrefBranch_no] : unpivot t1565585a
[ApplPrefBranch_no] ([Appl_no], [Pref_no], [Branch_no])
as
(
select
[Appl_no],
convert(int, substring([Pref_no], 5, 1)) as [Pref_no],
[Branch_no]
from
(
select
[Appl_no],
[pref1],
[pref2],
[pref3],
[pref4],
[pref5],
[pref6]
from dbo.t1565585a
) p
unpivot
(
[Branch_no]
for [Pref_no]
in
(
[pref1],
[pref2],
[pref3],
[pref4],
[pref5],
[pref6]
)
) pvt
),
-- CTE: [ApplPrefBranch] : replace Branch_no to Branch
[ApplPrefBranch] ([Appl_no], [Pref_no], [Branch])
as
(
select
[Appl_no],
[Pref_no],
[Branch]
from [ApplPrefBranch_no] [no]
left join [t1565585b] [b]
on [no].[Branch_no] = [b].[Branch_no]
)
-- generate result by pivot
select
[Appl_no],
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
from
(
select [Appl_no], [Branch], [Pref_no]
from [ApplPrefBranch]
) p
pivot
(
max([Pref_no])
for [Branch]
in
(
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
)
) pvt

|||

If you use sql server 2005...

Code Snippet

Create Table #application (

[Appl_no] int ,

[pref1] int ,

[pref2] int ,

[pref3] int ,

[pref4] int ,

[pref5] int ,

[pref6] int

);

Insert Into #application Values('125','5','4','1','6','8','2');

Insert Into #application Values('126','2','3','4','1','7','6');

Insert Into #application Values('127','5','2','1','6','4','3');

Insert Into #application Values('128','2','3','5','7','1','5');

Create Table #branch (

[Id] int ,

[Branch] Varchar(100)

);

Insert Into #branch Values('1','EEE');

Insert Into #branch Values('2','ECE');

Insert Into #branch Values('3','MECH');

Insert Into #branch Values('4','BT');

Insert Into #branch Values('5','IT');

Insert Into #branch Values('6','CIVIL');

Insert Into #branch Values('7','CHEM');

Insert Into #branch Values('8','ARCH');

Select U.Appl_No,U.Pref,B.Branch BranchName into #Applications

From

(Select Appl_No,1 Pref,Pref1 Branch From #application

Union All

Select Appl_No,2,Pref2 From #application

Union All

Select Appl_No,3,Pref3 From #application

Union All

Select Appl_No,4,Pref4 From #application

Union All

Select Appl_No,5,Pref5 From #application

Union All

Select Appl_No,6,Pref6 From #application) as U Join #branch B On U.Branch=B.Id

Select Appl_No,[EEE],

[ECE],

[MECH],

[BT],

[IT],

[CIVIL],

[CHEM],

[ARCH] from (Select Appl_No, Pref, BranchName From #Applications) as PP

Pivot(

Min(Pref) For BranchName in

(

[EEE],

[ECE],

[MECH],

[BT],

[IT],

[CIVIL],

[CHEM],

[ARCH]

)

) as Pvt

|||

If you use sql server 2000...

Create Table #application (

[Appl_no] int ,

[pref1] int ,

[pref2] int ,

[pref3] int ,

[pref4] int ,

[pref5] int ,

[pref6] int

);

Insert Into #application Values('125','5','4','1','6','8','2');

Insert Into #application Values('126','2','3','4','1','7','6');

Insert Into #application Values('127','5','2','1','6','4','3');

Insert Into #application Values('128','2','3','5','7','1','5');

Create Table #branch (

[Id] int ,

[Branch] Varchar(100)

);

Insert Into #branch Values('1','EEE');

Insert Into #branch Values('2','ECE');

Insert Into #branch Values('3','MECH');

Insert Into #branch Values('4','BT');

Insert Into #branch Values('5','IT');

Insert Into #branch Values('6','CIVIL');

Insert Into #branch Values('7','CHEM');

Insert Into #branch Values('8','ARCH');

Select U.Appl_No,U.Pref,B.Branch BranchName into #Applications

From

(Select Appl_No,1 Pref,Pref1 Branch From #application

Union All

Select Appl_No,2,Pref2 From #application

Union All

Select Appl_No,3,Pref3 From #application

Union All

Select Appl_No,4,Pref4 From #application

Union All

Select Appl_No,5,Pref5 From #application

Union All

Select Appl_No,6,Pref6 From #application) as U Join #branch B On U.Branch=B.Id

Select [Main].Appl_No

,[EEE].Pref [EEE]

,[ECE].Pref [ECE]

,[MECH].Pref [MECH]

,[BT].Pref [BT]

,[IT].Pref [IT]

,[CIVIL].Pref [CIVIL]

,[CHEM].Pref [CHEM]

,[ARCH].Pref [ARCH]

from

#application [Main]

Left Outer Join #Applications [EEE] On main.Appl_No = [EEE].Appl_No And [EEE].BranchName='EEE'

Left Outer Join #Applications [ECE] On main.Appl_No = [ECE].Appl_No And [ECE].BranchName='ECE'

Left Outer Join #Applications [MECH] On main.Appl_No = [MECH].Appl_No And [MECH].BranchName='MECH'

Left Outer Join #Applications [BT] On main.Appl_No = [BT].Appl_No And [BT].BranchName='BT'

Left Outer Join #Applications [IT] On main.Appl_No = [IT].Appl_No And [IT].BranchName='IT'

Left Outer Join #Applications [CIVIL] On main.Appl_No = [CIVIL].Appl_No And [CIVIL].BranchName='CIVIL'

Left Outer Join #Applications [CHEM] On main.Appl_No = [CHEM].Appl_No And [CHEM].BranchName='CHEM'

Left Outer Join #Applications [ARCH] On main.Appl_No = [ARCH].Appl_No And [ARCH].BranchName='ARCH'

|||

Hello Yoshihiro Kawabata ,

Thank you very much for helping me. I'm very much impressed.

Keep smiling,

Please help in queries

Hi,

I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.

In Book table, BookID, BookTitle, CategoryID

In Category table, CategoryID, CategoryName

In Subject table, SubjectID, SubjectName, CategoryID

In UserDownload table, UserID, BookID

In User table, UserID, UserName

I used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.

When user downloads book, I update UserDownload table.

The result I want to get is, Top Ten Download Subject. How can I get? Please help me.

The way I approach this kind of query is to first list the columns I need to return. Then I start thinking about which tables I need to touch to get that data, and then I think about how the data is related. Something like this should work for you:

SELECT
Subject.SubjectID,
Subject.SubjectName,
TopDownloads.BookCount
FROM
Subject
INNER JOIN
Book ON Subject.CategoryID = Book.CategoryID
INNER JOIN
(SELECT TOP 10 BookID, COUNT(*) AS BookCount FROM UserDownload GROUP BY BookID ORDER BY COUNT(*) DESC) AS TopDownloads ON Book.BookID = TopDownloads.BookID

Please help deciphering error message

I just changed my dataset syntaxes from the typical
SELECT col FROM table WHERE col3 = @.value
to
="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
For some reason I'm getting the following pop-up:
--
Processing Errors
--
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
â'ds_Legal_Entityâ'.
--
OK
--
I am not familiar with the CommandText syntax to understand where the error
might be. My query is below, could someone tell me what the problem might be?
Thanks!
Mike
="SELECT DISTINCT dbo.t_d_legal.legal_desc
FROM dbo.t_d_legal
INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
IIF(Parameters!BusKey.Value = 0,"","
AND
(dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
" ORDER BY dbo.t_d_legal.legal_desc"think you had extra single quote:
="SELECT col FROM table WHERE col3 = " & Parameters!Code.Value & " rest of
code"
otherwise if you need quotes due to your parm value being character you'll
need to double up on the quotes. read BOL.
"Bassist695" wrote:
> I just changed my dataset syntaxes from the typical
> SELECT col FROM table WHERE col3 = @.value
> to
> ="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
> For some reason I'm getting the following pop-up:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Cannot set the command text for data set 'ds_Legal_Entity'.
> Error during processing of the CommandText expression of dataset
> â'ds_Legal_Entityâ'.
> --
> OK
> --
> I am not familiar with the CommandText syntax to understand where the error
> might be. My query is below, could someone tell me what the problem might be?
> Thanks!
> Mike
> ="SELECT DISTINCT dbo.t_d_legal.legal_desc
> FROM dbo.t_d_legal
> INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
> WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
> IIF(Parameters!BusKey.Value = 0,"","
> AND
> (dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
> " ORDER BY dbo.t_d_legal.legal_desc"

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
Mike
Please do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:
> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help !!!

Hi, i have a problem about t-sql.

Table is with data

ID Name Part_Count

- --

1 Bush 3

2 Blair 2

3 Erdogan 2

Result table must be

ID NAME PART PART_COUNT

- - --

1 BUSH 3 1

2 BUSH 3 2

3 BUSH 3 3

4 BLAIR 2 1

5 BLAIR 2 2

6 ERDOGAN 2 1

7 ERDOGAN 2 2

HOW CAN I DO ?

The query below uses a couple of tricks. In order to expand the original part_count into a matching number of rows, you need a source of numbers stored in rows. Spt_values is a system table that has such a source of numbers. You could also build your own table or a table function.

Second, to generate the id notice that the original table can be used to get the starting id for the final list by summing the part_count field of all the rows with lower ids.

I have included my work tables so that you will have a complete example.

If this is for an assignment, I'd bet that you are supposed to use a cursor. That solution you ought to be able to figure out on your own.

/*Create table #one(
pid int not null Identity(1,1),
pname varchar(10) not null,
part_count int not null
)

insert #one values( 'Bush', 3 )
insert #one values( 'Blair', 2 )
insert #one values( 'Erdogan', 2 )
*/

Select id = (
Select start = IsNull( Sum( p2.part_count ), 0 )
From #one p2
Where p2.pid < parts.pid
) + v.number + 1,
name = parts.pname,
part = parts.part_count,
part_count = v.number + 1
From #one parts,
master..spt_values v
Where v.type = 'P'
And v.number < parts.part_count

Please help - unusual warning when creating a column

Hi everyone,
This is weird. I create a new column programatically using the "ALTER
TABLE" command (you know the one) inside SQL Query Analyzer. The
column is indeed created but the following warning is thrown:
WARNING: The table 'OrderRecords' has been created but its maximum row
size (15,420) exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes
I then checked a likely column. I ran a wee query to find the maximum
row and it returned a row of maximum length of 2,647 characters.
Now I'm totally confused. Does anyone know why such a warning is being
thrown? Any suggestions/comments/ideas/queries to run - much
appreciated.
Puzzled,
Al.The whole size of the rows exceeds 8060 bytes, thats the most possible
bytes SQl Server can handle per row, so this is just a warning (not an
error), that it might could be if you exceed the magic border, data
will be truncated.
If you are aware of that and you=B4ll be happy with it or you have
another way to handle this, this is fine, but you should keep that in
mind. For the best you should redesign your table by putting some
values in another table or thinking of changing the actual types of the
columns, if that is possible.
HTH, Jens Suessmeyer.|||This means that the sum of all you columns can in theory be 15,420
bytes
If someone inserts data and it exceeds 8060 bytes, then the statement
will fail
look at all the field sizes in this table and make sure they don't
exceed 8060 bytes
http://sqlservercode.blogspot.com/|||> This is weird. I create a new column programatically using the "ALTER
> TABLE" command (you know the one) inside SQL Query Analyzer. The
> column is indeed created but the following warning is thrown:
> WARNING: The table 'OrderRecords' has been created but its maximum row
> size (15,420) exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes
It is a WARNING about the maximum possible rowsize based on the columns
defined for the table. Just guessing, but it is likely that you have a
couple (or a bunch) of large varchar columns in the table that could, if
completely filled with data, exceed the maximum possible rowsize supported
by sql server.|||Thanks everyone - that's fine. I can live with that. Thanks for all
your feedback.
Al.
The happy one.

Please help - setting order of select in a self-referencing table

Hello,
I have a table of product categories that looks like this (air code, so
could be some typos, but it's basically right)...
create table producttypes (
ptype int not null identity(1,1) primary key,
typename varchar(50) not null default '',
ParentCat int references producttypes(ptype)
)
This allows categories to contain subcategories and so on.
In order to allow me to see the hierarchy, I am using the following
code, modified from some found in "Inside SQL Server 7.0" by Kalen
Delaney.
-- SQL starts
declare @.level int, @.current int
create table #stack (depthlevel int, ptype int)
create table #orgchart (seqno int identity, orglevel int not null, ptype int
not null)
set rowcount 1
select @.level=1, @.current=ptype from producttypes where ptype=parentcat
set rowcount 0
insert into #stack (depthlevel, ptype) values (@.level, @.current)
while (@.level>0)
begin
if exists (select * from #stack where depthlevel=@.level)
begin
set rowcount 1
select @.current=ptype from #stack where depthlevel=@.level
set rowcount 0
insert into #orgchart (orglevel, ptype) select @.level, @.current
delete from #stack where depthlevel=@.level and ptype=@.current
insert into #stack select @.level+1, ptype from producttypes where parentcat=
@.current and parentcat<>ptype
if @.@.ROWCOUNT > 0 select @.level=@.level+1
end
else
select @.level=@.level-1
end
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno
drop table #stack, #orgchart
-- SQL ends
This produces a set of results that gives me all the categories. I can
use the orglevel field to tell what level I'm at, so if it changes, I
can see if I've gone up or down the hierarchy.
Now, the problem is that I can't work out how to set the order of
categories. For example, if the above SQL produces...
1 16 16 Products
2 17 16 Outdoor Toys
3 1 17 Trampolines
3 2 17 Slides
3 3 17 Swings
3 4 17 Accessories
3 5 17 Climbing Frames
3 7 17 Bicycles
you can see that the categories on level 3 are not in any obvious order.
I would like to have them in alphabetical order.
Any ideas how I would modify the above SQL to do this? TIA
Alan Silver
(anything added below this line is nothing to do with me)Try
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
The results should be sorted first by the orglevel, and results within the
same orglevel will be sorted alphabetically. The syntax reads like "order
results first by o.seqno, then by e.typename"
"Alan Silver" wrote:

> Hello,
> I have a table of product categories that looks like this (air code, so
> could be some typos, but it's basically right)...
> create table producttypes (
> ptype int not null identity(1,1) primary key,
> typename varchar(50) not null default '',
> ParentCat int references producttypes(ptype)
> )
> This allows categories to contain subcategories and so on.
> In order to allow me to see the hierarchy, I am using the following
> code, modified from some found in "Inside SQL Server 7.0" by Kalen
> Delaney.
> -- SQL starts
> declare @.level int, @.current int
> create table #stack (depthlevel int, ptype int)
> create table #orgchart (seqno int identity, orglevel int not null, ptype i
nt not null)
> set rowcount 1
> select @.level=1, @.current=ptype from producttypes where ptype=parentcat
> set rowcount 0
> insert into #stack (depthlevel, ptype) values (@.level, @.current)
> while (@.level>0)
> begin
> if exists (select * from #stack where depthlevel=@.level)
> begin
> set rowcount 1
> select @.current=ptype from #stack where depthlevel=@.level
> set rowcount 0
> insert into #orgchart (orglevel, ptype) select @.level, @.current
> delete from #stack where depthlevel=@.level and ptype=@.current
> insert into #stack select @.level+1, ptype from producttypes where par
entcat=@.current and parentcat<>ptype
> if @.@.ROWCOUNT > 0 select @.level=@.level+1
> end
> else
> select @.level=@.level-1
> end
> select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
> join producttypes as e on e.ptype=o.ptype order by o.seqno
> drop table #stack, #orgchart
> -- SQL ends
>
> This produces a set of results that gives me all the categories. I can
> use the orglevel field to tell what level I'm at, so if it changes, I
> can see if I've gone up or down the hierarchy.
> Now, the problem is that I can't work out how to set the order of
> categories. For example, if the above SQL produces...
> 1 16 16 Products
> 2 17 16 Outdoor Toys
> 3 1 17 Trampolines
> 3 2 17 Slides
> 3 3 17 Swings
> 3 4 17 Accessories
> 3 5 17 Climbing Frames
> 3 7 17 Bicycles
> you can see that the categories on level 3 are not in any obvious order.
> I would like to have them in alphabetical order.
> Any ideas how I would modify the above SQL to do this? TIA
> --
> Alan Silver
> (anything added below this line is nothing to do with me)
>|||>Try
>select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
>join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
>The results should be sorted first by the orglevel, and results within the
>same orglevel will be sorted alphabetically. The syntax reads like "order
>results first by o.seqno, then by e.typename"
Mark,
Thanks for the reply, but if you look carefully, it's not that simple.
Your suggestion will list all level 2 categories together, followed by
all level 3 and so on. This will not give the correct hierarchy as there
may be several level 2 categories each with subcategories (ie level 3).
If this isn't clear, look at the longer category listing shown later on.
This is a more complete list than the abbreviated one I showed before.
Note that the ordering is done on seqno, which is the order in which the
categories were put into the orgchart table. The ordering is not done on
the level at all.
I think I need to modify the way the categories are pulled out of the
producttypes table when they are inserted into the stack table. Trouble
is, I can't see how to control the ordering as they are being pulled one
at a time.
Thanks for the reply. Any further help would be appreciated.
Longer category listing follows...
level typename
1 Products
2 Outdoor Toys
3 Trampolines
3 Slides
3 Swings
3 Accessories
3 Climbing Frames
3 Bicycles
2 Indoor Toys
3 Snooker Tables
3 Boy's Toys
4 Hard Puzzles
3 Girl's Toys
4 Easy Puzzles
3 Board Games
3 Pets
4 Ferrets
4 Dogs
4 Cats
>"Alan Silver" wrote:
>
Alan Silver
(anything added below this line is nothing to do with me)sql

Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou can create the temp table and insert the execution of the dts package into the temp table. This will "capture" the results and effectively mask them from being seen as output. You will also probably want to use SET NOCOUNT ON in your procedure.

Please help - Is this even possible?

I am executing multiple stored procedures in a stored procedure, pulling back a table of results, which is activated by a crystal report. One of the stored procedures activates a DTS Package. My problem is that the activation of the dts package returns output results, which in turn throws off my crystal report. Is there a way to still run this stored procedure as is but without returning the DTS output results??

I am using Sql 2000 and Crystal 8.5.

I would really appreciate any suggestions.

ThanksYou'd be more likely to get an answer to this question in the MS-SQL forum (http://www.dbforums.com/f7).

-PatPsql

please help

Hi
I got a table with 2 columns as follows
col1 col2
10 193.51
10 194.5
10 202.71
20 192.79
20 197.6
20 192.9
30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here
0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3
10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null
20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null
30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 Null
kalyan kameshkalikoi,
What criteria can we use to select the next row or the row that follows?
use northwind
go
create table t1 (
col1 int not null,
col2 numeric(9, 2)
)
go
insert into t1 values(10, 193.51)
insert into t1 values(10, 194.5)
insert into t1 values(10, 202.71)
insert into t1 values(20, 192.79)
insert into t1 values(20, 197.6)
insert into t1 values(20, 192.9)
insert into t1 values(30, 192.76)
insert into t1 values(30, 191.91)
insert into t1 values(30, 187.9)
go
alter table t1
add c1 int not null identity constraint uq_t1_c1 unique
go
select
a.col1,
a.col2,
(b.col2 / a.col2 - 1) * 100.00 as col3
from
t1 as a
left join
t1 as b
on b.c1 = (
select min(c.c1)
from t1 as c
where c.col1 = a.col1 and c.c1 > a.c1
)
order by a.c1
go
alter table t1
drop constraint uq_t1_c1
alter table t1
drop column c1
go
drop table t1
go
AMB
"kalikoi" wrote:

> Hi
>
> I got a table with 2 columns as follows
>
> col1 col2
>
> 10 193.51
> 10 194.5
> 10 202.71
>
> 20 192.79
> 20 197.6
> 20 192.9
>
> 30 192.76
> 30 191.91
> 30 187.9
>
> Now i need to add a column dynamically thru sql statement to the table
> so that my output should be as follows
>
> here
>
> 0.511601468=(194.5/193.51-1)*100
> 4.221079692=(202.71/194.5-1)*100
> and so on
>
> col1 col2 col3
>
> 10 193.51 0.511601468
> 10 194.5 4.221079692
> 10 202.71 null
>
> 20 192.79 2.494942684
> 20 197.6 -2.37854251
> 20 192.9 null
>
> 30 192.76 -0.440962855
> 30 191.91 -2.08952113
> 30 187.9 Null
>
> --
> kalyan kameshsql

Please Help

I have table like this..

Column1 Column2

--

1 Murder & Nonnegligent Manslaughter

1 Negligent Manslaughter

1 Justifiable Homicide

1 Kidnaping/Abduction

How to display: Murder & Nonnegligent Manslaughter,Negligent Manslaughter,Justifiable Homicide, Kidnaping/Abduction

Thanks

Malar:

See if this post, which discusses how to map multiple rows into a single column, provides some help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1262561&SiteID=1

|||

Here is a mockup:

declare @.sample table
( column1 integer,
column2 varchar(500)
)
insert into @.sample values (1, 'Murder & Nonnegligent Manslaughter')
insert into @.sample values (1, 'Negligent Manslaughter')
insert into @.sample values (1, 'Justifiable Homicide')
insert into @.sample values (1, 'Kidnaping/Abduction')
insert into @.sample values (2, 'Just a nominal entry')

select distinct column1,
left (replace (o.list, '~', '&'), len (o.list)-1)
as [List]
from @.sample a
cross apply
( select replace(column2,'&','~') + ',' as [text()]
from @.sample s
where a.column1 = s.column1
for xml path ('')
) o (list)
order by a.column1

-- column1 List
-- -- --
-- 1 Murder & Nonnegligent Manslaughter,Negligent Manslaughter,Justifiable Homicide,Kidnaping/Abduction
-- 2 Just a nominal entry

|||Thanks for your reply. I am using SQL Server 2000. I cannot use for xml path (''). Is there any other way to solve this issue?|||Yes, there is; one way is to use a scalar function to return the needed data. I will get an example of this in a minute. In the meantime, I would like to recruit suggestions from other members.|||

Here is an example using a scalar UDF:

create table dbo.mockup
( column1 integer,
seq integer,
column2 varchar(500)
)
insert into mockup values (1, 1, 'Murder & Nonnegligent Manslaughter')
insert into mockup values (1, 2, 'Negligent Manslaughter')
insert into mockup values (1, 3, 'Justifiable Homicide')
insert into mockup values (1, 4, 'Kidnaping/Abduction')
insert into mockup values (2, 1, 'Just a nominal entry')
go

create function dbo.stringProduct
( @.prm_column1 integer
)
returns varchar(255)
as
begin

declare @.retValue varchar(255) set @.retValue = ''

select @.retValue = @.retValue
+ case when len(@.retValue) = 0 then ''
else ',' end
+ column2
from mockUp
where column1 = @.prm_column1
order by seq

return @.retValue

end

go

select distinct column1,
dbo.stringProduct (column1)
as [List]
from mockUp

-- column1 List
-- -- --
-- 1 Murder & Nonnegligent Manslaughter,Negligent Manslaughter,Justifiable Homicide,Kidnaping/Abduction
-- 2 Just a nominal entry

Please Help

I am trying to pull the last 30 records in a table. I'm trying to write a
dynamic stored procedure in order to do this. I am joining a couple tables
and the table i need to get the 30 records out of is the second table. I
can't seem to get it to work with my code. Below is the code that I am using
to try to do this, I have declared the variables earlier in this procedure:
(Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
From dbo.schedulegovcom
Where LotID<=@.HighNum
and >=@.LowNum
Group by LotID) pTry:
SELECT TOP 30
[field list]
FROM
dbo.schedulegovcom
ORDER BY
LotID DESC
Mike
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am
> using
> to try to do this, I have declared the variables earlier in this
> procedure:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||You can not mix resultset with assigning value to a variable in the same
select statement.
-- wrong
select @.i = orderid, customerid from dbo.orders
AMB
"A.B." wrote:

> I am trying to pull the last 30 records in a table. I'm trying to write a
> dynamic stored procedure in order to do this. I am joining a couple tables
> and the table i need to get the 30 records out of is the second table. I
> can't seem to get it to work with my code. Below is the code that I am usi
ng
> to try to do this, I have declared the variables earlier in this procedure
:
> (Select Distinct LotID, @.LowNum = max(LotID)-30, @.HighNum = max(LotID)
> From dbo.schedulegovcom
> Where LotID<=@.HighNum
> and >=@.LowNum
> Group by LotID) p|||Thanks that worked
"Mike Jansen" wrote:

> Try:
> SELECT TOP 30
> [field list]
> FROM
> dbo.schedulegovcom
> ORDER BY
> LotID DESC
> Mike
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:F449DAD0-7B92-4E1A-9676-1604C2D79D3A@.microsoft.com...
>
>