[dba-SQLServer] Can't get resuts from sproc

Try this:

ALTER PROCEDURE usp_MyStoredProcedure  (@nvcPayroll  [nvarchar](50),
@dtiJobDate  [datetime], @nvcCode  [nvarchar](1))AS
BEGIN
 DECLARE @intUpdateFailed INT

SET NOCOUNT ON
 UPDATE dbo.MyTable
 SET AvailabilityCode=@nvcCode
 WHERE
    PayrollNo =  @nvcPayroll AND
     JobDate  =  @dtiJobDate AND
      (@nvcCode =  'A'  OR @nvcCode =  'N') AND
      (AvailabilityCode=  'A'  OR AvailabilityCode=  'N')

 SET @intUpdateFailed=@@ROWCOUNT

 DELETE FROM dbo.MyTable2
 WHERE PayrollNo=@nvcPayroll

 INSERT INTO MyTable2 (WebResult, PayrollNo)
 SELECT CASE WHEN @intUpdateFailed>0 THEN 'Success' ELSE 'Failed' END,
@nvcPayroll

SET NOCOUNT OFF
 SELECT WebResult, PayrollNo
 FROM MyTable2
 WHERE PayrollNo=@nvcPayroll
END


I added the NOCOUNT statements and also selected the actual field names from
the final select.

David

On Wed, Mar 4, 2009 at 10:16 AM, David Lewis <David at sierranevada.com> wrote:

>
>
> You might try the following kludge:
>
> At the beginning of the sproc, before all other code, have a dummy select
> statement that mirrors the final select that you want to execute:
> pseudo code:  SELECT 'Success' as Result,
>        ,'Some text' as nvcPayroll
>        ,[Column 1]
>        ,[Column 2]
>        ...
>        FROM MyTable2
>        WHERE 1=0
> Obviously this will not return any rows, but it will let the front end know
> what kind of result set to expect.  Then have the rest of the sproc as
> below.  I'm not certain this will solve your problem, but I have used in in
> other instances successfully.
>
> Regards,  d lewis
>
>
>
> To all,
>
> Sorry about cross posting this but really having a nightmare, I have the
> following store procedure in SQL Server 2005 :
>
> usp_MyStoredProcedure
>  (
>  @nvcPayroll  [nvarchar](50),
>  @dtiJobDate  [datetime],
>  @nvcCode  [nvarchar](1)
>  )
> AS
> BEGIN
>  DECLARE @intUpdateFailed INT
>  UPDATE dbo.MyTable
>  SET AvailabilityCode=@nvcCode
>  WHERE PayrollNo =  @nvcPayroll AND
>  JobDate  =  @dtiJobDate AND
>   (
>  @nvcCode =  'A'  OR
>  @nvcCode =  'N'
>   )                AND
>   (
>  AvailabilityCode=  'A'  OR
>  AvailabilityCode=  'N'
>   )
>
>  SET @intUpdateFailed=@@ROWCOUNT
>  DELETE FROM dbo.MyTable2
>  WHERE PayrollNo=@nvcPayroll
>  INSERT INTO MyTable2 (WebResult, PayrollNo)
>  SELECT CASE WHEN @intUpdateFailed>0 THEN 'Success' ELSE 'Failed' END,
> @nvcPayroll
>  SELECT *
>  FROM MyTable2
>  WHERE PayrollNo=@nvcPayroll
> END
>
> If I run this in SQL Server seems to work fine, and the SELECT * FROM
> MyTable2 WHERE PayrollNo=@nvcPayroll returns all the fields.  However I
> have
> a simple piece of VB code as below:
>
> Dim rs As ADODB.Recordset
> If DE.Conn.Start = adStateClosed Then
>    DE.Conn.Open
> Endif
> Set rs = DE.Conn.Execute(usp_MyStoredProcedure '999999', '" &
> format(Date,"mm/dd/yy") & "','N'")
> msgbox rs.fields(0)
>
> DE.Conn.Close
>
> ' DE.Conn is a DataEnvironment connection which links to the SQL Server
>
> The vb code returns Item cannot be found in the collection corresponding to
> the requested name or ordinal.
>
> However if I run the SP, then comment out everything except the SELECT
> statement it returns the resultset.
>
> Can anyone tell me what is happening or what I am doing wrong please.
>
> Thanks in advance for any help on this....
>
>
>
>
>
> The contents of this e-mail message and its attachments are covered by the
> Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended
> solely for the addressee(s) hereof. If you are not the named recipient, or
> the employee or agent responsible for delivering the message to the intended
> recipient, or if this message has been addressed to you in error, you are
> directed not to read, disclose, reproduce, distribute, disseminate or
> otherwise use this transmission.  If you have received this communication in
> error, please notify us immediately by return e-mail or by telephone,
> 530-893-3520, and delete and/or destroy all copies of the message
> immediately.
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>