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

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

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

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.