running total from stored proc

i need to return the number of rows from a query as something
eg select ID,column from table where column = something
then return ID,column,expr1 (which is the total of records returned)
is this possible ?
my query looks like
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')mark
You can look at an OUTPUT parameters in stored procedure (see BOL)
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.ret INT
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
GROUP BY DateLeadReceived ,LeadLoggedBy,Type
RETURN @.ret
"mark" <mark@.remove.com> wrote in message
news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
> i need to return the number of rows from a query as something
> eg select ID,column from table where column = something
> then return ID,column,expr1 (which is the total of records returned)
> is this possible ?
> my query looks like
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
>
>
>|||Uri
What's the difference between using return @.var or using output paramter ?
"Uri Dimant" wrote:

> mark
> You can look at an OUTPUT parameters in stored procedure (see BOL)
>
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.ret INT
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
> GROUP BY DateLeadReceived ,LeadLoggedBy,Type
> RETURN @.ret
>
> "mark" <mark@.remove.com> wrote in message
> news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
>
>|||Well , a difference as you said RETURN (unlike OUTPUT) statement terminates
the batch and none of the statements within SP are executed. RETURN
statement is usually used to reterun an error number or @.@.idenitity value.
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||One is a return value and the other is output parameters. For a return value
, it can only be int and
most of us only use this to communicate success/error. You can have several
output parameters and
they can be of almost any datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||that code didnt seem to work, but ive solved the problem like this
thanks
mark
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
declare @.total int
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
select @.total=@.@.rowcount
select @.total as totalcolumn
GO

running stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

running stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

sql

Running Stored Proc inside of Access and getting error

Running Stored Proc inside of Access and getting "The stored procedure
executed successfully but did not return records". When I run in Query
Analyzer it runs just fine. The Stored Proc is as follows:
CREATE PROCEDURE MAINT_EditorActivitybyTime (@.Enter_Start_Date_
smalldatetime, @.Enter_End_Date_ smalldatetime)
AS
DROP TABLE TEMP_MA_Node
--stored proc to create one table from MA_Node and MA_Node_Status10
DECLARE @.Temp_Node_Status10 table(NodeID bigint, NodeName nvarchar(100),
TypeID int, Status tinyint, OwnerID int, Url nvarchar(2048), Path
nvarchar(1000))
INSERT INTO @.Temp_Node_Status10
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, Path
FROM MA_Node_Status10
SELECT *
INTO TEMP_MA_Node
FROM @.Temp_Node_Status10
INSERT INTO TEMP_MA_Node
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, 'Live'
FROM MA_Node
SELECT dbo.MA_Change.CreateTime, dbo.MA_User.Email,
dbo.MA_Change.ChangeNodeID, dbo.MA_Change.DraftAction, TEMP_MA_Node.NodeName
,
TEMP_MA_Node.TypeID, TEMP_MA_Node.Status
FROM dbo.MA_Change INNER JOIN
dbo.MA_User ON dbo.MA_Change.ModifyUser =
dbo.MA_User.UserID LEFT OUTER JOIN
TEMP_MA_Node ON dbo.MA_Change.ChangeNodeID =
TEMP_MA_Node.NodeID
WHERE (dbo.MA_Change.CreateTime BETWEEN @.Enter_Start_Date_ AND
@.Enter_End_Date_)
ORDER BY dbo.MA_User.Email, dbo.MA_Change.CreateTime
GO
It also returns data in Query Analyzer like this:
2006-01-10 11:40:00.927 andy1221 198643 2 asdfasdf 4 0
2006-01-10 11:41:35.553 andy1221 198644 2 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:44:39.963 andy1221 198644 4 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:45:02.637 andy1221 30064 4 Reference Education Distance
Learning 4 0Hi Andy,
use
SET NOCOUNT ON
at the beginning of the SP
and
SET NOCOUNT OFF
as the last statetement of your proc.
If you use the sp like yours you will recieve multiple recordsets in Access.
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm