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 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 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.

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 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.

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

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?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|||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?

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 proced
ure 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

COM object from Store Procedure

Hello there
Can i use a COM object from Store Procedure?
Thanks-
--
<Harvey Triana />Harvey Triana wrote:

> Hello there
> Can i use a COM object from Store Procedure?
> Thanks-
> --
> <Harvey Triana />
In SQL Server 2000 take a look at the sp_OA procs in Books Online:
sp_OACreate, sp_OAMethod, etc.
In SQL Server 2005 the preferred alternative would be to call a .NET
class from a proc.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Dont forget to use sp_OADestroy to destroy the object when you are
done. We have seen memory leak issues when we didnt use sp_OADestroy.
SAI|||Thank a lot-
yes, i know kindnesses of 2005 version.
But, i want testing SQL Server 200/COM vs SQL Server 2005/VB.NET in this
plan (I s the best efficiency)
<Harvey Triana />
Drilling View Developer (52 oil wells in 2005)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
mensaje news:1138724769.911891.36830@.o13g2000cwo.googlegroups.com...
> Harvey Triana wrote:
>
> In SQL Server 2000 take a look at the sp_OA procs in Books Online:
> sp_OACreate, sp_OAMethod, etc.
> In SQL Server 2005 the preferred alternative would be to call a .NET
> class from a proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Columns referenced inside store procedures may not exist issue

I have a stored procedure which I want to be added to 2 different databases.
However, I run into an issue that some statements in the stored procedure ar
e
referencing columns only exist in one of the databases. I want to SQL Server
to parse those statements only if those columns exist in the database.
Thanks.Sorry, this is not how it works (you could use dynamic SQL, but that is not
generally a good option). You will need to make two versions of the
procedure or add the columns in the other database.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:75D605C4-6843-4E83-8E49-39252BA25FEA@.microsoft.com...
>I have a stored procedure which I want to be added to 2 different
>databases.
> However, I run into an issue that some statements in the stored procedure
> are
> referencing columns only exist in one of the databases. I want to SQL
> Server
> to parse those statements only if those columns exist in the database.
>
> Thanks.

Columns not updating from Stored procedure

Ok, so I've got the following stored procedure:

ALTER PROCEDURE dbo.tbUserPreferences_UpdateOrInsert

(
@.username varchar(50),
@.preferences varchar(300),
@.view_name varchar(300),
@.default_view varchar(10) = 'Y'
)

AS
UPDATE tbUserPreferences SET @.default_view='N' WHERE username=@.username

-- IF NOT EXISTS (
-- SELECT *
-- FROM tbUserPreferences
-- WHERE username=@.username
-- AND view_name=@.view_name
-- )
-- INSERT INTO tbUserPreferences (username, preferences,view_name,default_view) VALUES (@.username,@.preferences,@.view_name,@.default_view)
RETURN

The commented out section works fine, but the UPDATE line does not. I know there are columns that have "username=@.username", but this call is not updating their default_view column.

Please, if anybody knows why, let me in on the secret. Thanks!Try to execute and check result:

UPDATE tbUserPreferences SET @.default_view='N' WHERE username='your sp param'

select @.@.rowcount|||Wow, I'm so silly. And it took me looking at your reply to get it.

The code I ment to try was:

UPDATE tbUserPreferences SET default_view='N' WHERE username=@.username

"default_view" not "@.default_view". Thank you for the reply. Even though I didn't need to test your suggestion, it made me realize my problem. Thanks!|||It's still a good example of why you should error check your code...

Column, parameter, or variable #1: Cannot find data type SqlDatareader

Hello Everyone,

A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:

public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index)
{
if(dr.GetSqlValue(index) == null)
return SqlInt32.Null;
else
return dr.GetSqlInt32(index)
}

I than allways get the following ErrorMessage:

Column, parameter, or variable #1: Cannot find data type SqlDatareader.

Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.

My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.

Would appreciate any kind of help! Thanks

You may need to refer to the SqlDataReader with full qualified name if you haven't using the proper namespace:

System.Data.SqlClient.SqlDataReader

Column size in emails generated using XP sendmail

Hi, with some help today I was able to get my stored procedure running
and have the results emailed to me. However, this is how its showing
up:
Accounting_Year WK_IN_FYEAR Location
GL_Account
Col Data Difference
----- ---- -----------
----
------- ------- -------
2007 49 Test1
500-001
-2587872.0200 -2587872.0200 .0000
2007 49 Test2
500-001
-3344713.5000 -3344713.5000 .0000
2007 49 Test3
500-001

Is there anyway to line them up side by side properly? When i have two
colms selected the format comes out ok. Thanks for all the help
again!

Here is the sp:

CREATE PROCEDURE [dbo].[spEmailVariance]
(
@.SubjectLine as varchar(500),
@.EmailRecipient VARCHAR(100)
)
AS

DECLARE @.strBody varchar(5000)
set @.SubjectLine = 'Weekly Flash Update'
SET @.strBody =

'Select statement'

exec master.dbo.xp_sendmail
@.recipients= 'XX@.XXXX.com',
@.subject= @.SubjectLine,
@.query = @.strbody

RETURN
GOTake a look at the @.width and @.attach_results parameters of xp_sendmail.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Dec 14, 7:52 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Take a look at the @.width and @.attach_results parameters of xp_sendmail.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

You are awesome! That works... I didnt use the @.attach_results TRUE
because i was getting a system error. I used @.width set to 170 and
that worked fine.