Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure?
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.com
Aditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables
sqlsql

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatena
tion, or expose a
computed column in the table. That way the data doesn't get out of sync in c
ase someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How wil
l
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null space
s
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concate
nation, or expose a
> computed column in the table. That way the data doesn't get out of sync in
case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL i
n either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to
go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:
> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How
> > will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:
> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>> Assuming no nulls in firstname or lastname column:
>> UPDATE tbl
>> SET FULLNAME = FirstName + ' ' + LastName
>> But, why do you need to store this? You can create a view with the concatenation, or expose a
>> computed column in the table. That way the data doesn't get out of sync in case someone modifies
>> the
>> first name or last name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
>> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
>> > LASTNAME, ADDRESS, TELEPHONE,...
>> > Now i have added a new column in the table, naming it FULLNAME. In this i
>> > want to insert the FIRSTNAME and the LASTNAME
>> > combined with a space in between, the rest all remaining the same. How will
>> > i do it?Is it possible with a Stored Procedure'
>> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
>> >
>> > You can also email me at : aditya595@.yahoo.com
>>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining two database - best way to transfer objects and data

Hi,
I have two databases that I need to combine into one.
What is the best way to transfer all objects, data, constraints,
relationships, stored procedures, triggers, etc., from one to the other?
I'd like to set it up in a way where I can first run it on a development
environment where I can make all the necessary code changes, and then run th
e
exact same actions on the live server when the time comes.Hi
It sounds like you are not using a version control system to stored your
code? If so you would be able to extract the ddl from your version control
system and create the tables/views/stored procedures... in the new database
and it would only need a method to transfer the data. Instead you may want t
o
script the objects using the scripting options in Enterprise Manager or usin
g
DMO, make the changes and then load the data (say using BCP or DTS). You
could also use tools such as DBGhost http://www.innovartis.co.uk/home.aspx o
r
Red Gate's SQL compare http://www.red-gate.com/ to create your scripts.
Before you start you may want to make sure that the structure of your
development and live systems are the same (say using the above tools)
John
"Gal Steinitz" wrote:

> Hi,
> I have two databases that I need to combine into one.
> What is the best way to transfer all objects, data, constraints,
> relationships, stored procedures, triggers, etc., from one to the other?
> I'd like to set it up in a way where I can first run it on a development
> environment where I can make all the necessary code changes, and then run
the
> exact same actions on the live server when the time comes.

Combining two database - best way to transfer objects and data

Hi,
I have two databases that I need to combine into one.
What is the best way to transfer all objects, data, constraints,
relationships, stored procedures, triggers, etc., from one to the other?
I'd like to set it up in a way where I can first run it on a development
environment where I can make all the necessary code changes, and then run the
exact same actions on the live server when the time comes.Hi
It sounds like you are not using a version control system to stored your
code? If so you would be able to extract the ddl from your version control
system and create the tables/views/stored procedures... in the new database
and it would only need a method to transfer the data. Instead you may want to
script the objects using the scripting options in Enterprise Manager or using
DMO, make the changes and then load the data (say using BCP or DTS). You
could also use tools such as DBGhost http://www.innovartis.co.uk/home.aspx or
Red Gate's SQL compare http://www.red-gate.com/ to create your scripts.
Before you start you may want to make sure that the structure of your
development and live systems are the same (say using the above tools)
John
"Gal Steinitz" wrote:
> Hi,
> I have two databases that I need to combine into one.
> What is the best way to transfer all objects, data, constraints,
> relationships, stored procedures, triggers, etc., from one to the other?
> I'd like to set it up in a way where I can first run it on a development
> environment where I can make all the necessary code changes, and then run the
> exact same actions on the live server when the time comes.sqlsql

Combining these 2 short Stored Procedures

CREATE PROCEDURE MyBooks_Selling
(
@.MemberID SMALLINT
)
AS

SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@.MemberID

GO
GRANT EXEC
ON MyBooks_Selling
TO bto
GO

CREATE PROCEDURE MyBooks_Buying
(
@.MemberID SMALLINT
)
AS

SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@.MemberID

GO
GRANT EXEC
ON MyBooks_Buying
TO bto
GO

Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure

thxSomething like:

CASE @.NewInput
 WHEN 'blah' THEN
 SELECT * FROM Seller
 ELSE
 SELECT * FROM Buyer
END

You'll need to check the exact syntax in Books Online

Cheers
Ken|||thx a lot

Combining Stored Procedures

I have two stored procedures

ALTER PROCEDURE dbo.qryCountOne
(@.inputID int)
AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
(dbo.TableOne.value = @.inputID)

ALTER PROCEDURE dbo.qryCountTwo
(@.inputID int)
AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
(dbo.TableTwo.value = @.inputID)

What would be the best way to combine these two, so that I only have to
make one database query, and the two values (CountOne, and CountTwo)
will get returned to me?

Any help\pointers greatly appreciated,

Noel"Noel" <vbgooglegroups@.yahoo.com> wrote in message
news:1120752722.113719.37280@.g44g2000cwa.googlegro ups.com...
>I have two stored procedures
> ALTER PROCEDURE dbo.qryCountOne
> (@.inputID int)
> AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
> (dbo.TableOne.value = @.inputID)
> ALTER PROCEDURE dbo.qryCountTwo
> (@.inputID int)
> AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
> (dbo.TableTwo.value = @.inputID)
> What would be the best way to combine these two, so that I only have to
> make one database query, and the two values (CountOne, and CountTwo)
> will get returned to me?
>
> Any help\pointers greatly appreciated,
> Noel

Output parameters are usually the best way to return scalar values from a
stored proc, so perhaps something like this?

create proc dbo.GetRowCounts
@.TableOneID int
@.TableOneCount int OUTPUT,
@.TableTwoID int,
@.TableTwoCount int OUTPUT
as
begin
select @.TableOneCount = count(*)
from dbo.TableOne
where col = @.TableOneID

select @.TableTwoCount = count(*)
from dbo.TableTwo
where col = @.TableTwoID
end

If you have to use a result set instead of output parameters, then see
"UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
MSSQL, so if that is the real column name, you might want to consider
changing it if possible - see "Reserved Keywords" in BOL.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> If you have to use a result set instead of output parameters, then see
> "UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
> MSSQL, so if that is the real column name, you might want to consider
> changing it if possible - see "Reserved Keywords" in BOL.

It's listed among the "Future keywords". Given the record of SQL Server
I would not hold my breath until all those words become reserved.

T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> T-SQL has this funny notion of unreserved keywords, and they seem to grow in number with every release.<<

They got that idea from ANSI, which has such a list when we were
looking at the SQL3 working draft.|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.<<
> They got that idea from ANSI, which has such a list when we were
> looking at the SQL3 working draft.

Nah, I was thinking of things like OUTPUT - which must have been around
since the 80s. OUTPUT is a keyword, but it's not reserved and you
can create a table or a column with that name, without any quoting.

But I assume you were thinking of the list of "Future keywords". That
does indeed seem like an ANSI list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's great, thanks!

Noel|||Yes, I agree it's unlikely to be a problem, but I generally prefer to
recommend that people follow best practices as documented by Microsoft.
For me, that's a better option than assuming that something has never
been a problem in the past, so it's going to be OK in the future (cf
the short article in this month's SQL Server Magazine on xp_reg% procs
behaviour in SP4).

Simon

Combining Pass-Through Queries into a Stored Proc.

Is it possible to combine multiple Views into a Stored Procedure? Can I reference the results from one (1) View within the stored procedure? If so, how would I go about it?
Thanks!!Refer to Books online for Using Pass-Through Queries as Tables topic.

combining multiple select statements in a SP

I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:

select name, age, title
from tablea

select name, age, title
from tableb

Could you combine these queries into a single result set?

Yes you can. You can use join or union to do this..

|||

Hi,

try like this:

select name, age, title
from tablea

UNION ALL //or use Union

select name, age, title
from tableb

Hope this helps

Combining Fields in a stored procedure

my question is this...I have to fields I want to retreive..One is a nvarchar and the other is an integer...I want to return them in this format
(interger field + '/' + nvarcharfield) as combinedfield
problem is i get errors when I try to get this value
I just need the info I know you cant add theses two together...

Example of output needed...

31/OfficeVisit

my sp

ALTER procedure EncounterCodes_NET
(
@.ClinicID int
)
as
select
CombinedField=(EnCodeID + EnCodeDesc)
from
Clinic_Encodes
Where
ClinicID=@.ClinicID
Order by Sortorderreplace the EncodeID with

Cast(EnCodeID as varchar)

Combining archive tables into a single table

Hi,
I had a table in my database which will update every month ... so we used to
update the table every month and stored the archieve tables in a seperate
database.
--ID is the primary key for this table and all historical of the record will
have the same ID
Now I have to combine all those tables(Around 30 tables and each had around
3k columns) into one table based on the primary key of current version table
.
-Each Archieve table had one Unique Cycle_id
Note: The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
Now,the structure of my new table can be the same as "current version" table
(this month) with additional field cycle_id
Pls try to help me guys, which way is better to achieve this."Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Hi,
> I had a table in my database which will update every month ... so we used
> to
> update the table every month and stored the archieve tables in a seperate
> database.
> --ID is the primary key for this table and all historical of the record
> will
> have the same ID
> Now I have to combine all those tables(Around 30 tables and each had
> around
> 3k columns) into one table based on the primary key of current version
> table.
> -Each Archieve table had one Unique Cycle_id
> Note: The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the time
> Now,the structure of my new table can be the same as "current version"
> table
> (this month) with additional field cycle_id
> Pls try to help me guys, which way is better to achieve this.
Take a look at the Partitioned Views topic in Books Online.
David Portas
SQL Server MVP
--|||David,
Thats a good idea ...i just went through that ,but the problem is to make
partioned view on partioned tables we need to have all smilar structure
tables.I think then only it will be possible to combine(Union) all those and
show it as One Table.
But in my case,as I said
-- The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
--And i have to add a column to uniquely represent which version it is(Is
there any other solution to ditinguish the versions)
"David Portas" wrote:

> "Kumar" <Kumar@.discussions.microsoft.com> wrote in message
> news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Take a look at the Partitioned Views topic in Books Online.
> --
> David Portas
> SQL Server MVP
> --
>
>|||"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:18358F29-A0FB-45F5-9586-E30A127703E9@.microsoft.com...
> David,
> Thats a good idea ...i just went through that ,but the problem is to make
> partioned view on partioned tables we need to have all smilar structure
> tables.I think then only it will be possible to combine(Union) all those
> and
> show it as One Table.
> But in my case,as I said
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
>
> "David Portas" wrote:
>
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
That's easily fixed then - add the columns to the older tables. Why would
that be a problem?

> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
Yes you do have to add such a column. Without that your design is weak,
whether or not you choose to use a partitioned view. It's not generally a
good idea to have multiple tables of the same structure with duplicate data.
Any reason you didn't or don't combine them as a single table? Re-reading
your post it seems that was your actual question. The answer is just to
insert all the data to a common table using INSERT statements. Maybe I'm not
quite understanding what your problem is. Perhaps it would help if you
posted some sample DDL.
David Portas
SQL Server MVP
--|||If archived tables have less columns than the current table, simply add null
values to the union selects where the actual values are missing.
Also add a column that will contain a distinct value for each of the
partitions.
If you post some DDL we can give you a better illustration.
ML
http://milambda.blogspot.com/|||Thanks ML,David
My table is like huge one with almost 30 columns ..any way iam displaying
some of those for demonstration
--Lets say,this is my current version table and the newly creating should be
in this format
CREATE TABLE [dbo].[COPY_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL--This is newly added column and which is
not there in previous versions
)
I had another table "Cycle",which maintains ids of the previous version
tables..like
Cycle_ cycle Publication data table name
name
1005 2005-11-04 22:59:18.653 dbo.GLOBAL_CC_MASTER_1005
0905 2005-10-07 13:35:15.330 dbo.GLOBAL_CC_MASTER_0905
0805 2005-09-08 02:26:43.873 dbo.GLOBAL_CC_MASTER_0805
0705 2005-08-08 22:13:04.013 dbo.GLOBAL_CC_MASTER_0705
0605 2005-07-07 19:03:43.020 dbo.GLOBAL_CC_MASTER_0605
0505 2005-06-06 17:34:03.517 dbo.GLOBAL_CC_MASTER_0505
0405 2005-05-10 12:15:12.027 dbo.GLOBAL_CC_MASTER_0405
0305 2005-04-11 23:38:59.073 dbo.GLOBAL_CC_MASTER_0305
Now I have to add all these tables into one table
"Archieve_GLOBAL_CC_MASTER" and has to include 'cycle_name' as primary key
along with 'id_pk', which should be look like:
CREATE TABLE [dbo].[Archieve_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[CYCLE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,--composite Primary key,New column which is not there in current table
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL
)
I think this should help you for better understanding|||It may be late and I may need glasses, but I think you've just come up with
a
solution. Create the new table and migrate all data from the old tables,
creating missing values at insert.
ML
http://milambda.blogspot.com/|||Kumar wrote:
> Thanks ML,David
> My table is like huge one with almost 30 columns ..any way iam displaying
> some of those for demonstration
> --Lets say,this is my current version table and the newly creating should
be
> in this format
>
Like this:
INSERT INTO [dbo].[archive_global_cc_master]
(id_pk, cycle_name, batch_id, source_id, ledger_id, payables_id,
claim_status)
SELECT id_pk, 1005, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_1005
UNION ALL
SELECT id_pk, 0905, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0905
UNION ALL
SELECT id_pk, 0805, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0805
UNION ALL ... etc
I'm not clear what you want to do with your keys. Are other tables to
reference Archive on a surrogate IDENTITY key? If so you'll want to
assign a new IDENTITY in which case id_pk won't be IDENTITY in your
archive table.
Are you sure all those other columns need to be nullable? Are you sure
you have an alternate key in each table? If not you may have
duplicates. I'm not convinced that you have a sound design here to
start with, but that could be a mistaken assumption given that this is
just a fragment.
David Portas
SQL Server MVP
--

Combining a stored procedure and a table in one Dataset?

Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?

Thanks.

Brad

If you mean a SQL style join at the dataset level then the answer is no.

You could always use a batch of SQL instead of a simple select and use temporary tables within the query to perform the JOIN. Even better create a stored procedure to do it.

If the data is coming from different databases/platforms then could use linked servers.

sqlsql

Combining a stored procedure and a table in one Dataset?

Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?

Thanks.

Brad

If you mean a SQL style join at the dataset level then the answer is no.

You could always use a batch of SQL instead of a simple select and use temporary tables within the query to perform the JOIN. Even better create a stored procedure to do it.

If the data is coming from different databases/platforms then could use linked servers.

Combining 2 databases as one

Hi,
We need to combine 2 databases as one on SQL Server 2000. One database has
wickedly many View tables and the other has more wickedly many Stored
Procedures. What would be the best way to combine them.
YCScript the objects in one of the database and use the script to create the objects in the other
database. And test, test, test. Also see: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"YC" <asppsa@.hotmail.com> wrote in message news:%23I0KtzDhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We need to combine 2 databases as one on SQL Server 2000. One database has wickedly many View
> tables and the other has more wickedly many Stored Procedures. What would be the best way to
> combine them.
> YC
>sqlsql

Combine two stored procedure results

Hi,
I have two databases DB2006, DB2005.I have the Stored Procedure getdata which has the 2 parameters startdate and end date.This Stored procedure exist in all databases.

STored procedure CallGetdata
@.startdate datetime
@.enddate datetime
If startdate < 1/1/2007 the call getdata in the DB2006
if startdate <1/1/2006 then call getdata in the DB2005.
Here the problem is if startdate is 6/1/2005 and Enddate is '3/1/2006' then combine the stored procedure results from the DB2006 and DB2005 databases.
I have one idea i.e create a temp table and insert the two Stored procedure results into it.
Create #table1(name varchar(20))
insert into #table1 exec DB2006.dbo.getdata
insert into #table1 exexc DB2005.dbo.getdata
Select * from #table1
drop table #table1.
Anyone please give me better idea than creating temp table.

Thanks in advance

Can you change getdata (or is this something your stuck with?)

Another possible option is to turn your getdata stored procedure into a table valued function. This means you won't be able to exec it directly though, you will have to access it in a query. Then you could do:

select * from db2006.dbo.getdata(...) union select * from db2005.dbo.getdata(...).

This would most likely perform better (although I can't say for sure). If getdata() is one query (or can be turned into one query), then you can use an inline table valued function, and this will definitely perform better then the temp table solution.

Also, your probably better off using a table variable in this case as opposed to a temp table.

|||

if you are stuck with the stored procedure as-is, then your idea is the best one. Unless the proc is extremely complex (and with a name like getdata, it is not going to be easy for us to guess :) then building a database for queries that span databases is a better idea and union the results together. Or consider the ideas that Adam has given also.

I would consider not having databases with the year in the name, and just have one database that spans years, personally. That is clearly the most solid answer and will make your reporting easier. If this wasis a performance idea, there are ways to make this work far better than with multiple databases. And if both databases are on the same drive, you are possibly not saving much...

|||

Thank you very much for your ideas.For the reports the stored procedures already created.But now to imrove the performance they created the separate 3 databases one for current year and other for previous year and remaining(all previous years are in Hist databases).Now I need to migrate the existing stored procedure to all databases all working fine but the problem is when they enter startdate which is in one year and end date in another year, in this case we need to combine the results of two stored procedures from two databases.Thatswhy I created a separate stored procedure and temp table is used for combining the two SP results.

Thanks

|||

Hi,

Which one give better performance whether the Stored procedure with table datatype to insert the combined results from two databases, Or table valued functions.

Thanks.

Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>

Combine two lots of xml in to one?

I have two stored procedures each returning xml using for xml explicit:
GetOrders returns data as:
<Orders><Order id = "1"/><Order id = "2"></Orders>
GetCustomers returns data as:
<Customers><Customer id = "1"/><Customer id = "2"/></Customers>
Now what I want is a third procedure that reuses both these stored
procedures to get customers and orders like so:
GetCustomersAndOrders returns data as:
<CustomersAndOrders>
<Orders>
<Order id = "1"/>
<Order id = "2">
</Orders>
<Customers>
<Customer id = "1"/>
<Customer id = "2"/>
</Customers>
</CustomersAndOrders>
Is there a way to do it?
Thanks!
In SQL Server 2000, you have to do this on the mid-tier. You can use the
SQLXML templates for example.
In SQL Server 2005, you would need to change the stored procs into
user-defined functions and use another FOR XML call to compose them, if you
want to do it on the server.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>I have two stored procedures each returning xml using for xml explicit:
> GetOrders returns data as:
> <Orders><Order id = "1"/><Order id = "2"></Orders>
> GetCustomers returns data as:
> <Customers><Customer id = "1"/><Customer id = "2"/></Customers>
> Now what I want is a third procedure that reuses both these stored
> procedures to get customers and orders like so:
> GetCustomersAndOrders returns data as:
> <CustomersAndOrders>
> <Orders>
> <Order id = "1"/>
> <Order id = "2">
> </Orders>
> <Customers>
> <Customer id = "1"/>
> <Customer id = "2"/>
> </Customers>
> </CustomersAndOrders>
> Is there a way to do it?
> Thanks!
>
|||Thanks for your feedback. Shame that it is not possible though.
Is there a way to do it, say, by casting both lots of xml data to strings
and concatenating them with surrounding root tags?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2000, you have to do this on the mid-tier. You can use the
> SQLXML templates for example.
> In SQL Server 2005, you would need to change the stored procs into
> user-defined functions and use another FOR XML call to compose them, if
you
> want to do it on the server.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eMtxfo%238EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||You cannot cast results of FOR XML in SQL Server 2000 since it can only be
transported to the mid-tier. And even in SQL Server 2005, you cannot cast
the result of a stored procedure since stored procedures operate via a
side-effect.
This is not only the case for XML but for any result that a stored proc
produces as a side-effect.
So the best way to do what you want is on the client-side.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:%23eG%23roJ9EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks for your feedback. Shame that it is not possible though.
> Is there a way to do it, say, by casting both lots of xml data to strings
> and concatenating them with surrounding root tags?
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OsUBnVB9EHA.1084@.TK2MSFTNGP15.phx.gbl...
> you
>

Combine Many Stored Procedures into One

Hello All,
I am new to stored procedures and I was wondering if there is any way
to accomplish this with one stored procedure. The reason I want to do
this is, because this way I will only need to create 1 C# function in
my asp.net application that passes two variables.
All of your help would be greatly apperciated.
Here is what I got so far. Below I have approx. 20 select statements
that return a count for each type of fields from 1 table. I want to
retrieve those fields in my asp.net function. When I run this stored
proc. I get multiple record sets. I was wondering if I can do this in
one recordset.
CREATE PROCEDURE dbo.Portal_CountCTReplace
(
@.StartDate nvarchar(100),
@.EndDate nvarchar(100)
)
AS
SELECT COUNT
(Portal_CTQA.ChkrplKnob) AS CountOfChkrplKnob
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplKnob) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplRibbon) AS CountOfChkrplRibbon
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplRibbon) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplChanger) AS CountOfChkrplChanger
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplChanger) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplBTray) AS CountOfChkrplBTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplBTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplTTray) AS CountOfChkrplTTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplTTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplLTray) AS CountOfChkrplLTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplLTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplRTray) AS CountOfChkrplRTray
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplRTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl5SI) AS CountOfChkrpl5SI
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl5SI) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl400mtckit) AS CountOfChkrpl400mtckit
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl400mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl4100mtckit) AS CountOfChkrpl4100mtckit
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl4100mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplJet4000) AS CountOfChkrplJet4000
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplJet4000) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplJet8000) AS CountOfChkrplJet8000
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplJet8000) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplGenicomCable) AS CountOfChkrplGenicomCable
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplGenicomCable) != 'NO') AND
(Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplATBCable) AS CountOfChkrplATBCable
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplATBCable) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplNC6000Battery) AS CountOfChkrplNC6000Battery
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplNC6000Battery) != 'NO') AND
(Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplNC4000Battery) AS CountOfChkrplNC4000Battery
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplNC4000Battery) != 'NO') AND
(Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplSlimline) AS CountOfChkrplSlimline
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplSlimline) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.ChkrplSlimDrive) AS CountOfChkrplSlimDrive
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplSlimDrive) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl40HD1) AS CountOfChkrpl40HD1
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD1) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
SELECT COUNT
(Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))First off make sure to add SET NOCOUNT ON to the beginning of your sp but
then create a table variable and insert each counting into it with the
associated name of the count like this:
DECLARE TABLE @.Temp ([Who] VARCHAR(20), [Totals] INT)
INSERT INTO @.Temp ([Who], [Totals])
SELECT 'CountOfChkrplRibbon',
COUNT(Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
...
Then do one select at the end from the table variable.
SELECT * FROM @.Temp.
Andrew J. Kelly SQL MVP
<manmit.walia@.gmail.com> wrote in message
news:1143552580.816972.165340@.u72g2000cwu.googlegroups.com...
> Hello All,
> I am new to stored procedures and I was wondering if there is any way
> to accomplish this with one stored procedure. The reason I want to do
> this is, because this way I will only need to create 1 C# function in
> my asp.net application that passes two variables.
> All of your help would be greatly apperciated.
> Here is what I got so far. Below I have approx. 20 select statements
> that return a count for each type of fields from 1 table. I want to
> retrieve those fields in my asp.net function. When I run this stored
> proc. I get multiple record sets. I was wondering if I can do this in
> one recordset.
> CREATE PROCEDURE dbo.Portal_CountCTReplace
> (
> @.StartDate nvarchar(100),
> @.EndDate nvarchar(100)
> )
> AS
>
> SELECT COUNT
> (Portal_CTQA.ChkrplKnob) AS CountOfChkrplKnob
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplKnob) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplRibbon) AS CountOfChkrplRibbon
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplRibbon) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplChanger) AS CountOfChkrplChanger
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplChanger) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplBTray) AS CountOfChkrplBTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplBTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplTTray) AS CountOfChkrplTTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplTTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplLTray) AS CountOfChkrplLTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplLTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplRTray) AS CountOfChkrplRTray
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplRTray) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl5SI) AS CountOfChkrpl5SI
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl5SI) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl400mtckit) AS CountOfChkrpl400mtckit
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl400mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl4100mtckit) AS CountOfChkrpl4100mtckit
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl4100mtckit) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplJet4000) AS CountOfChkrplJet4000
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplJet4000) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplJet8000) AS CountOfChkrplJet8000
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplJet8000) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplGenicomCable) AS CountOfChkrplGenicomCable
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplGenicomCable) != 'NO') AND
> (Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplATBCable) AS CountOfChkrplATBCable
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplATBCable) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplNC6000Battery) AS CountOfChkrplNC6000Battery
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplNC6000Battery) != 'NO') AND
> (Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplNC4000Battery) AS CountOfChkrplNC4000Battery
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplNC4000Battery) != 'NO') AND
> (Portal_CTQA.Date_Cleaned BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplSlimline) AS CountOfChkrplSlimline
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplSlimline) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.ChkrplSlimDrive) AS CountOfChkrplSlimDrive
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.ChkrplSlimDrive) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl40HD1) AS CountOfChkrpl40HD1
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl40HD1) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>
> SELECT COUNT
> (Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
> FROM Portal_CTQA
> WHERE
> (((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
> BETWEEN @.StartDate AND @.EndDate))
>|||Use union all between select statements
or
simply
Select count(col1) as col1count, count(col2) as
col2count,...count(col20) as col20count
from yourtable
Madhivanan|||Basically you have two options:
1) either declare as many output parameters as there are values you need; or
2) assign the values to as many local variables, then select them at the end
of the procedure - e.g.:
set @.var1 = (
SELECT COUNT (Portal_CTQA.ChkrplKnob) AS CountOfChkrplKnob
FROM Portal_CTQA
WHERE
(((Portal_CTQA.ChkrplKnob) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))
)
...
select @.var1 as <column name>
,...
ML
http://milambda.blogspot.com/|||Hey Thanks for the help so far, but I am still lost...
This is what I have so far as a test... When I run this, I get an
syntax error at 'DECLARE TABLE'
CREATE PROCEDURE dbo.Portal_CountCT2Replace
(
@.StartDate nvarchar(100),
@.EndDate nvarchar(100)
)
AS
DECLARE TABLE @.Temp ([Who] VARCHAR(20), [Totals] INT)
INSERT INTO @.Temp ([Who], [Totals])
SELECT 'CountOfChkrplRibbon',
COUNT(Portal_CTQA.Chkrpl40HD2) AS CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE
(((Portal_CTQA.Chkrpl40HD2) != 'NO') AND (Portal_CTQA.Date_Cleaned
BETWEEN @.StartDate AND @.EndDate))|||manmit.walia@.gmail.com wrote:
> Hey Thanks for the help so far, but I am still lost...
> This is what I have so far as a test... When I run this, I get an
> syntax error at 'DECLARE TABLE'
> CREATE PROCEDURE dbo.Portal_CountCT2Replace
> (
> @.StartDate nvarchar(100),
> @.EndDate nvarchar(100)
> )
> AS
> DECLARE TABLE @.Temp ([Who] VARCHAR(20), [Totals] INT)
The syntax you were given was slightly messed up. It should be
DECLARE @.Temp TABLE ([Who] VARCHAR(20), [Totals] INT)|||On 28 Mar 2006 05:29:40 -0800, manmit.walia@.gmail.com wrote:

>Hello All,
>I am new to stored procedures and I was wondering if there is any way
>to accomplish this with one stored procedure. The reason I want to do
>this is, because this way I will only need to create 1 C# function in
>my asp.net application that passes two variables.
>All of your help would be greatly apperciated.
Hi manmit.walia,
Looking at the code you posted, you should get a tremendous performance
boost if you combine the 20 SELECT COUNT statements into one single
statement with some CASE expression. As an added bonus, it'll also get
you the result in a single recordset.
SELECT SUM(CASE WHEN ChkrplKnob <> 'NO' THEN 1 ELSE 0 END) AS
CountOfChkrplKnob,
SUM(CASE WHEN ChkrplRibbon <> 'NO' THEN 1 ELSE 0 END) AS
CountOfChkrplRibbon,
...
SUM(CASE WHEN Chkrpl40HD2 <> 'NO' THEN 1 ELSE 0 END) AS
CountOfChkrpl40HD2
FROM Portal_CTQA
WHERE Date_Cleaned BETWEEN @.StartDate AND @.EndDate
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||Thanks all...I have learned from this tasks. I got mine to work and it
really increased performance.
Once agian. Thanks.|||You need to learn Standard SQL and good programming. The correct
syntax is "<>" not "!=" and never use insanely long NVARCHAR for data
elements that have a known data type. You are asking for a Chinese
sutra to show up as a start date! Why do you use as many parens in SQL
as you would in LISP?
What you are trying to od is a standard SQL progrqamming technique.
Get a copy of SQL FOR SMARTIES for help, after you get the basics down.
CREATE PROCEDURE dbo.portal_sum.ReportChecks
(@.start_date DATETIME, @.end_date DATETIME)
AS SELECT
SUM (CASE WHEN chkrplknob <> 'NO' THEN 1 ELSE 0 END) AS rplknob_cnt,
SUM (CASE WHEN chkrplribbon <> 'NO' THEN 1 ELSE 0 END) AS
rplribbon_cnt,
SUM (CASE WHEN chkrplchange <> 'NO' THEN 1 ELSE 0 END) AS rplchang_cnt,
SUM (CASE WHEN chkrplbtray <> 'NO' THEN 1 ELSE 0 END) AS rplbtray_cnt,
Etc.
FROM Portal_CTQA
WHERE clean_date BETWEEN @.start_date AND @.end_date;
I would probably put @.start_date, @.end_date in the SELECT list for
documentation.

Combine Add/Edit SP's?

Rather than having 2 separate stored procedures to add and update something
like a customer record, are there any drawbacks to having one stored proc
that does both? If the custID is passed in, then it would do the update, and
if the custID param is NULL than it would do an insert. Would this approach
have any performance implications?Personally, I like that design (which some refer to as "upsert"). An
efficient pattern you can follow is:
UPDATE Tbl
SET ...
WHERE custID = @.custID
--This means no row exists already
IF @.@.ROWCOUNT = 0
BEGIN
INSERT Tbl (...)
VALUES (...)
END
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> Rather than having 2 separate stored procedures to add and update
something
> like a customer record, are there any drawbacks to having one stored proc
> that does both? If the custID is passed in, then it would do the update,
and
> if the custID param is NULL than it would do an insert. Would this
approach
> have any performance implications?|||I Use this ALL the time, but add to it using the following "design pattern"
If @.PK Is Null
Begin
Insert (ColA, ColB, ColC, ...)
Values(@.ParameterA, @.ParameterB, @.ParameterC, ...)
Set @.PK = ScopeIdentity()
End
Else If Exists (Select * From Table
Where PK = @.PK)
Begin
-- Using IsNull allows you to NOT pass in a parameter
-- and thereby effectively NOT update it (Set Null
Default values)
Update Table Set
ColA = IsNull (@.ParameterA, ColA),
ColB = IsNull (@.ParameterB, ColB),
ColC = IsNull (@.ParameterC, ColC),
..
Where PK = @.PK
End
Else
Begin
Set Identity_Insert TableName On -- When PK Is IDentity
Insert (PK, ColA, ColB, ColC, ...)
Values(@.PK, @.ParameterA, @.ParameterB, @.ParameterC, ...)
Set Identity_Insert TableName Off -- When PK Is IDentity
End
-- And then at the end, regardless of which path was taken,
Select @.PK As PK
"Adam Machanic" wrote:

> Personally, I like that design (which some refer to as "upsert"). An
> efficient pattern you can follow is:
>
> UPDATE Tbl
> SET ...
> WHERE custID = @.custID
> --This means no row exists already
> IF @.@.ROWCOUNT = 0
> BEGIN
> INSERT Tbl (...)
> VALUES (...)
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> something
> and
> approach
>
>

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
I just answered my own question about 5 minutes after posting. The sp_OAxxx stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
|||Look up OLE Automation in BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?
|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison