I coulnt return a table from a procedure . Pls Help me

I couln't return a table from a procedure . Pls Help meCan you send the procedure ? Would help|||I didn't think (again?) you could...

I tried

USE Northwind
Go

CREATE PROC mySproc
@.Results TABLE ([name] sysname) OUTPUT
AS

INSERT INTO @.Results ([name]) SELECT [name] FROM sysobjects
GO

EXEC mySproc

And that didn't work...then I rea din BOL

E. Use an OUTPUT cursor parameter
OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.

First, create the procedure that declares and then opens a cursor on the titles table:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @.titles_cursor CURSOR VARYING OUTPUT
AS
SET @.titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @.titles_cursor
GO

Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

USE pubs
GO
DECLARE @.MyCursor CURSOR
EXEC titles_cursor @.titles_cursor = @.MyCursor OUTPUT
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @.MyCursor
END
CLOSE @.MyCursor
DEALLOCATE @.MyCursor
GO

But why do you need to do this?|||My 2 cents ...

A Function can return a table variable but not a procedure. See if you can rewrite the SP as a UDF.

Originally posted by aneeshattingal
I couln't return a table from a procedure . Pls Help me|||Hey aneeshattingal,

Just a guess...but you have an Oracle background and are switching to sql server?

I can't stat SQL in a user account

Windows 2000 SP4 and SQL Server 2000 SP3
When I try to start SQL Server in the account
DOMAIN\Administrator, the system return the error 22042 (-
2147024890).
I can not start in a user account and I can not send
email.
Could you help me please ? ThanksAre you logged on to NT box using a domain admin account ?
if not then I don't think it will let you specify a
Domain\Administrator as a sql service start up account. It
would be a gross violation of security principles. Specify
the same account that you are using to log in to the
system, or a local administrator.
hth.
>--Original Message--
>Windows 2000 SP4 and SQL Server 2000 SP3
>When I try to start SQL Server in the account
>DOMAIN\Administrator, the system return the error 22042 (-
>2147024890).
>I can not start in a user account and I can not send
>email.
>Could you help me please ? Thanks
>.
>|||I am logged with the DOMAIN\Administator on the server
when I tried to set up it.
But if I am logged with a other account with the admin
rights I have the same message.
>--Original Message--
>Are you logged on to NT box using a domain admin
account ?
>if not then I don't think it will let you specify a
>Domain\Administrator as a sql service start up account.
It
>would be a gross violation of security principles.
Specify
>the same account that you are using to log in to the
>system, or a local administrator.
>hth.
>>--Original Message--
>>Windows 2000 SP4 and SQL Server 2000 SP3
>>When I try to start SQL Server in the account
>>DOMAIN\Administrator, the system return the error 22042
(-
>>2147024890).
>>I can not start in a user account and I can not send
>>email.
>>Could you help me please ? Thanks
>>.
>.
>|||the error you are getting is about the server not able to
verify the user account you specified. type following
command on DOS command prompt just to make sure the domain
controller is responding:
net users /DOMAIN
This should list all users in the domain you are logged
into.
Try using Local System Account in service startup for sql
server.
hth.
>--Original Message--
>I am logged with the DOMAIN\Administator on the server
>when I tried to set up it.
>But if I am logged with a other account with the admin
>rights I have the same message.
>>--Original Message--
>>Are you logged on to NT box using a domain admin
>account ?
>>if not then I don't think it will let you specify a
>>Domain\Administrator as a sql service start up account.
>It
>>would be a gross violation of security principles.
>Specify
>>the same account that you are using to log in to the
>>system, or a local administrator.
>>hth.
>>--Original Message--
>>Windows 2000 SP4 and SQL Server 2000 SP3
>>When I try to start SQL Server in the account
>>DOMAIN\Administrator, the system return the error 22042
>(-
>>2147024890).
>>I can not start in a user account and I can not send
>>email.
>>Could you help me please ? Thanks
>>.
>>.
>.
>|||The command DOS net users /DOMAIN is OK. I can see le the
users list in the domain.
But if I use the local system account I cant send email
with SQL Agent and I need it.
>--Original Message--
>the error you are getting is about the server not able
to
>verify the user account you specified. type following
>command on DOS command prompt just to make sure the
domain
>controller is responding:
>net users /DOMAIN
>This should list all users in the domain you are logged
>into.
>Try using Local System Account in service startup for
sql
>server.
>hth.
>>--Original Message--
>>I am logged with the DOMAIN\Administator on the server
>>when I tried to set up it.
>>But if I am logged with a other account with the admin
>>rights I have the same message.
>>--Original Message--
>>Are you logged on to NT box using a domain admin
>>account ?
>>if not then I don't think it will let you specify a
>>Domain\Administrator as a sql service start up
account.
>>It
>>would be a gross violation of security principles.
>>Specify
>>the same account that you are using to log in to the
>>system, or a local administrator.
>>hth.
>>--Original Message--
>>Windows 2000 SP4 and SQL Server 2000 SP3
>>When I try to start SQL Server in the account
>>DOMAIN\Administrator, the system return the error
22042
>>(-
>>2147024890).
>>I can not start in a user account and I can not send
>>email.
>>Could you help me please ? Thanks
>>.
>>.
>>.
>.
>sql

I cant do a Min() Group By?

I can't beleive it i'm supose to be a DB programmer and i can't think how to do this min() group by:

I need the return the SupID only, for each of the cheapest 'QFProductRef'
Ie the result should be (1,6,3,4)

table is called supplierInventory
SupIDQFProductRefDLRPriceAfterDisc
160138355.41
2601139416.12
3601387411.03
4600885312.25
5601383512.09
660113947.11
7601387412.81
8600885314.22

I'll have to go back to being a lumber jack if someone help!.
thanks in advancecan you xplain a little more...|||This query returns 4,6,1,3. If there were two or more records that tie for the lowest price then it would return each of their SupIDs.

Select supplierInventory.SupID
 FROM
 (Select QFProductRef, Min(DLRPriceAfterDisc ) as DLRPriceAfterDisc
 From supplierInventory
 Group By QFProductRef
 ) A
 INNER JOIN supplierInventory ON A.QFProductRef=supplierInventory.QFProductRef AND A.DLRPriceAfterDisc=supplierInventory.DLRPriceAfterDisc

I can't get this view faster

Hey guys I can't get this view to be faster. It is causing me serious problems. I am trying to get all this data gathered to return to the client but I can't make it any faster. My Low return time is 24 seconds and my High return time is 77 Seconds. But the it will take 24 Seconds even if no data is returned.

What can i do?

I have indexed every foreign key.

i have pictures of my execution path that you can look at at the following address

http://salessimplicity.dnsalias.com/ssnet/needhelp/needhelp.htm

If you need anything to help me let me know I am more than willing to help to get this thing going faster..

The Main view is below

Code Snippet

create VIEW dbo.vuFollowUpTriggerAll AS
SELECT
Client.ID,
SubdivFU.ID AS FollowUpSubdivID,
FollowUpDone.ID AS FollowUpID,
Case
When SubdivFU.DeptID is Not Null then Agent1.ID
Else Agent2.ID
END As SalesAgentID,
-- Agent2.ID AS SalesAgentID,
Subdiv.ID AS SubdivisionID,
Subdiv.MarketingName AS SubdivisionName,
Buyer.FirstName,
Buyer.LastName,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As SalesAgentFirst,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As SalesAgentLast,
-- Agent2.FirstName AS SalesAgentFirst,
-- Agent2.LastName AS SalesAgentLast,
FollowUpDone.DateItemEntered,
FollowUpDone.DateItemComplete AS DateComplete,
FollowUpDone.Custom,
--Removed ClientStatus.Status - didn't see it used in code.
SubdivFU.ContactDesc AS TaskDesc,
FollowUpDone.Removed,
--For Contingencies and Deposit Reminders, we subtract the days rather then add them.
DATEADD(Day, TE.Multiplier * ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate) AS DueDate,
ClientDate.DepositID,
SubdivFU.ContactType AS TaskType,
SubdivFU.MasterReportID,
SubdivFU.EmailLetterID,
Buyer.Email,
Buyer.DoNotSendEmail,
Buyer.DoNotSendLetter,
SubdivFU.OverrideOptOut,
FollowUpDone.UserID,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As AssignedToFirstName,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As AssignedToLastName
FROM
dbo.tbSubdivision Subdiv
INNER JOIN dbo.tbSubdivisionFollowUp SubdivFU ON SubdivFU.SubdivisionID = Subdiv.ID
-- tbTrigger Event is the table that helps us put this whole spaghetti together
--it joins the Event type to the Date Type to the Multiplier.
INNER JOIN dbo.tbTriggerEvent TE ON TE.ID = SubdivFU.TriggerEvent
--tbDateType is what connects vuClient Dates the appropriate date field.
-- INNER JOIN dbo.tbDateType DT ON DT.ID = TE.DateTypeID
INNER JOIN dbo.tbClient Client ON Subdiv.ID = Client.SubdivisionID
AND ((SubdivFU.ClientRankID = Client.ClientRankID AND TE.UseRank = 1)
OR (TE.UseRank = 0))
--vuCLientDates replaces what we were going to use as tbClientDates but it is almost as fast to generate this on the fly
--as it is to generate it on the fly.
INNER JOIN dbo.vuClientDates ClientDate ON ClientDate.DateTypeID = TE.DateTypeID and Client.ID = ClientDate.ClientID
INNER JOIN dbo.tbMember Member ON Member.ClientID = Client.ID
AND Member.MemberTypeID = 1 --Buyer Type
INNER JOIN dbo.tbIndividual Buyer ON Member.IndividualID = Buyer.ID
--vuFollowUpTriggerGetAgent is a view we use to connect Clients to their Agents in each Department.
Left Outer Join vuFollowUpTriggerGetAgent GetAgent on GetAgent.DeptID = SubdivFU.DeptID and Client.ID = GetAgent.ClientID and GetAgent.SubdivisionID = Subdiv.ID
Left Outer JOIN dbo.tbUser Agent1 ON Agent1.ID = GetAgent.UserID
Inner JOIN dbo.tbUser Agent2 ON Agent2.ID = Client.UserID
LEFT OUTER JOIN dbo.tbFollowUp FollowUpDone ON FollowUpDone.SubdivFollowUpID = SubdivFU.ID
AND FollowUpDone.ClientID = Client.ID
AND isNull(FollowUpDone.DepositID, 0) = isNull(ClientDate.DepositID, 0)
WHERE (FollowUpDone.DateItemComplete Is Null)
AND (isnull(FollowUpDone.Removed,0) = 0)
AND (SubdivFU.Active = 1)
AND ((SubdivFU.TriggerEvent = 'R' AND DATEDIFF(DAY, ClientDate.EventDate, GETDATE()) < 0 )
OR (SubdivFU.TriggerEvent = 'O' AND (FLOOR(CAST(GETDATE() AS float)) >= DATEADD(Day, ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate)) )
OR (SubdivFU.TriggerEvent <> 'R' and SubdivFU.TriggerEvent <> 'O')
)
AND (SubdivFU.OverrideOptOut = 1
OR ((SubdivFU.ContactType = 'O')
OR (SubdivFU.ContactType = 'L' AND Buyer.DoNotSendLetter = 0)
OR (SubdivFU.ContactType = 'E' AND Buyer.DoNotSendEmail = 0)
OR (SubdivFU.ContactType = 'C' AND Buyer.DoNotCall = 0)))
AND (((SubdivFU.DeptID IS Not Null) and (Agent1.ID is Not Null)) or (SubdivFU.DeptID is Null) )

It joins to a few other views defined below

Code Snippet

ALTER View dbo.vuFollowUpTriggerGetAgent AS
-- 5/14/07 DebP Added link to tbDept in order to exclude inactive departments.

SELECT SFU.DeptID, tbClient.ID AS ClientID,
ISNULL(tbClientDept.UserID, ISNULL(tbSubdivisionDept.UserID, Null)) AS UserID,
tbSubdivision.ID as SubdivisionID
FROM tbSubdivision
INNER JOIN dbo.tbClient ON tbClient.SubdivisionID = tbSubdivision.ID
INNER Join
(SELECT SubdivisionID, DeptID
FROM dbo.tbSubdivisionFollowUp
INNER JOIN dbo.tbDept ON dbo.tbSubdivisionFollowUp.DeptID = dbo.tbDept.ID
AND dbo.tbDept.Active <> 0
GROUP BY SubdivisionID, DeptID)
SFU ON SFU.SubdivisionID = tbSubdivision.ID
-- The following is no longer needed since we're doing an inner join to tbDept.
--AND SFU.DeptID is Not Null
LEFT OUTER JOIN dbo.tbSubdivisionDept ON tbSubdivisionDept.DeptID = SFU.DeptID
AND tbSubdivisionDept.SubdivisionID = tbSubdivision.ID
AND tbSubdivisionDept.UserID is not Null
LEFT OUTER JOIN dbo.tbClientDept ON tbClientDept.DeptID = SFU.DeptID
AND tbClient.ID = tbClientDept.ClientID
AND tbClientDept.UserID is not Null

and

Code Snippet

ALTER view dbo.vuClientDates as

SELECT tbClient.ID as ClientID,
Case
When tbClient.ClientStatusID = 4 then 1
When tbClient.ClientStatusID = 1 then 2
When tbClient.ClientStatusID = 2 then 4
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then 11
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then 5
End as DateTypeID,

Case
When tbClient.ClientStatusID = 4 then VisitDate
When tbClient.ClientStatusID = 1 then ContractDate
When tbClient.ClientStatusID = 2 then ActualCloseDate
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then VisitDate
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then CancellationDate
End
as EventDate,
Null as DepositID, Null as StageID, Null as EscrowStageID From tbClient

UNION ALL

SELECT tbClient.ID as ClientID, 3 as DateTypeID, ExpirationDate as EventDate, Null as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
where (tbClient.Contingency = 1)
AND (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in ( 1, 2, 4)
)

UNION ALL

SELECT tbClient.ID as ClientID, 6 as DateTypeID, DateDue as EventDate, Deposits.ID as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
INNER JOIN dbo.tbDeposits Deposits ON Deposits.ClientID = tbClient.ID
AND Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL
Where (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in( 1,2,4)
)

These are the indexes i have added for this

Code Snippet

CREATE INDEX [IX_tbIndividual] on [dbo].[tbIndividual] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient] on [dbo].[tbClient] ([ClientStatusID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_1] on [dbo].[tbClient] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_2] on [dbo].[tbClient] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember] on [dbo].[tbMember] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_1] on [dbo].[tbMember] ([IndividualID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_2] on [dbo].[tbMember] ([MemberTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbDeposits] on [dbo].[tbDeposits] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_2] on [dbo].[tbSubdivisionFollowUp] ([TriggerEvent]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp] on [dbo].[tbSubdivisionFollowUp] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_1] on [dbo].[tbSubdivisionFollowUp] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_3] on [dbo].[tbSubdivisionFollowUp] ([ContactType]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_2] on [dbo].[tbFollowUp] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp] on [dbo].[tbFollowUp] ([SubdivFollowUpID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_1] on [dbo].[tbFollowUp] ([MasterReportID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_3] on [dbo].[tbFollowUp] ([DepositID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionDept] on [dbo].[tbSubdivisionDept] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision] on [dbo].[tbUserSubdivision] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision_1] on [dbo].[tbUserSubdivision] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClientDept_1] on [dbo].[tbClientDept] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent] on [dbo].[tbTriggerEvent] ([DateTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent_1] on [dbo].[tbTriggerEvent] ([TriggerEventDesc]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([AvailablePlanOptionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([RoomPlanID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([Username]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_LoginID] on [dbo].[tbUser] ([LoginID]) WITH FILLFACTOR = 95

Hmm... Three table scans (at 25% of the execution plan each) seem to indicate you're missing appropriate indexes somewhere.|||Ok but where?
|||

You have a lot of joins in your query. Have you tried using temporary tables instead to store intermediate results and then join them ?

|||

Well, two of them are on the tbClient with CancelationDate and ClientStatusID, and one of those also looks at Contingency.

(Those are tables 2 and 3 with the 25% costs on the far right of the plan).

You're only indexing on ClientStatusID

You're also using a lot of functions in the filtering which will pop the plan back to table scans.

You may want to develop and "grow" this incrementally. Just do the first two tables and get them honed in, then add in the next table.

Get a good clean manageable baseline to grow from.

|||Also, to go along with Dale, you'll want to create indexes based on the composite key of the join. Don't just create an index for each column individually. Do as he says and "grow" the view. Each time you add something new, review the execution plan again to ensure nothing has snuck up on you.|||

These are all great ideas...

Thanks

|||How do i get rid of a hash match left?

I can not return the rows affected just using

I can not return the rows affected just using
int a = myCommand.ExecuteNonQuery(); it always return -1
WHY?The documentation says:
For UPDATE, INSERT, and DELETE statements, the return value is the
number of rows affected by the command. For all other types of
statements, the return value is -1. If a rollback occurs, the return
value is also -1.
Is your NonQuery a single UPDATE, INSERT, or DELETE statement? If it's
anything more than that (a batch with several statements, for example),
the docs say you'll get -1.
Steve Kass
Drew University
Kenny M. wrote:

>I can not return the rows affected just using
>int a = myCommand.ExecuteNonQuery(); it always return -1
>WHY?
>
>|||Yeap it is an update query with 3 conditions
ALTER PROCEDURE dbo.UpdateTicketStatus
@.NroTicket VARCHAR(12) = NULL,
@.IDAgencia VARCHAR(12) = NULL,
AS
SET NOCOUNT ON
DECLARE @.Sqlstring VARCHAR(3000)
SELECT @.Sqlstring = 'UPDATE Ventas SET Status = ''A'''
SELECT @.Sqlstring = @.Sqlstring + ' WHERE DateDiff(n, FechaHora, GetDate())
<= 5'
SELECT @.Sqlstring = @.Sqlstring + ' AND NroTicket = ''' +
CONVERT(VARCHAR,@.NroTicket) + ''''
SELECT @.Sqlstring = @.Sqlstring + ' AND ID = ''' +
CONVERT(VARCHAR,@.IDAgencia) + ''''
EXECUTE (@.Sqlstring)|||You could try adding RETURN(@.@.ROWCOUNT) to the end of the sproc -- I seem to
recall using that at one point for this purpose... Another possible
workaround is to remove SET NOCOUNT ON -- ExecuteNonQuery might use the
rowcount internally to get its value.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:906602A3-4E31-458E-865D-102EFD87CEBE@.microsoft.com...
> Yeap it is an update query with 3 conditions
>
> ALTER PROCEDURE dbo.UpdateTicketStatus
> @.NroTicket VARCHAR(12) = NULL,
> @.IDAgencia VARCHAR(12) = NULL,
> AS
> SET NOCOUNT ON
> DECLARE @.Sqlstring VARCHAR(3000)
> SELECT @.Sqlstring = 'UPDATE Ventas SET Status = ''A'''
> SELECT @.Sqlstring = @.Sqlstring + ' WHERE DateDiff(n, FechaHora, GetDate())
> <= 5'
> SELECT @.Sqlstring = @.Sqlstring + ' AND NroTicket = ''' +
> CONVERT(VARCHAR,@.NroTicket) + ''''
> SELECT @.Sqlstring = @.Sqlstring + ' AND ID = ''' +
> CONVERT(VARCHAR,@.IDAgencia) + ''''
> EXECUTE (@.Sqlstring)|||Kenny,
The procedure isn't just an UPDATE statement - it's an EXECUTE statement.
And while you didn't provide the command text of myCommand, it's not an
UPDATE statement either - it's a procedure call. I suspect you will
only get
the rowcount returned if the command text of myCommand is 'UPDATE ...'
SK
Kenny M. wrote:

>Yeap it is an update query with 3 conditions
>
>ALTER PROCEDURE dbo.UpdateTicketStatus
> @.NroTicket VARCHAR(12) = NULL,
> @.IDAgencia VARCHAR(12) = NULL,
>AS
>SET NOCOUNT ON
>DECLARE @.Sqlstring VARCHAR(3000)
>SELECT @.Sqlstring = 'UPDATE Ventas SET Status = ''A'''
>SELECT @.Sqlstring = @.Sqlstring + ' WHERE DateDiff(n, FechaHora, GetDate())
><= 5'
>SELECT @.Sqlstring = @.Sqlstring + ' AND NroTicket = ''' +
>CONVERT(VARCHAR,@.NroTicket) + ''''
>SELECT @.Sqlstring = @.Sqlstring + ' AND ID = ''' +
>CONVERT(VARCHAR,@.IDAgencia) + ''''
>EXECUTE (@.Sqlstring)
>|||umm ok I got it, thks
"Steve Kass" wrote:

> Kenny,
> The procedure isn't just an UPDATE statement - it's an EXECUTE statement
.
> And while you didn't provide the command text of myCommand, it's not an
> UPDATE statement either - it's a procedure call. I suspect you will
> only get
> the rowcount returned if the command text of myCommand is 'UPDATE ...'
> SK
>
> Kenny M. wrote:
>
>

I can not return the rows affected

I can not return the rows affected by just using
int a = myCommand.ExecuteNonQuery(); because the return is always -1
I have to use an Output parameter in my SP all the time.. anybody knows WHY?
'Do you have 'SET NOCOUNT ON' somewhere in your stored procedure or as a
default setting? This can cause the behavior you're seeing.