Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

Exec(SQLString) does not return an Output value in SQL Server 2005

If have the following stored procedure called from a VB.Net program. The stored procedure should return @ColumnValue as Output to calling program, but it doesn't. If I execute the stored procedure in SQL Server Management Studio the value is NULL. If I change @SQLString to  .... SELECT ' + quotename(@ColumnName) + ' FROM ' .... I get the correct value. Btw T_NAM is a user-defined data type.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE [dbo].[WP_SP_GET_TEXT_CUSTOM_FIELD]
       @TableName T_NAM,
       @ColumnName T_NAM,
       @WhereColumn T_NAM,
         @Criteria T_NAM,
       @ColumnValue T_NAM Output

AS

BEGIN
      
      Declare @SqlString varchar(1000)
            
      Begin Try
      
            Set @SqlString = 'SET QUOTED_IDENTIFIER OFF
                                   SELECT ' + @ColumnValue + ' = '  + quotename(@ColumnName)
                                             + ' FROM ' + quotename(@TableName)
                                   + ' WHERE ' + quotename(@WhereColumn) + ' = "' + @Criteria + '"'
      
      Exec(@SqlString)
      
      Return 0

      End Try

      Begin Catch
            
            Return ERROR_NUMBER()

      End Catch

END

The program calls the stored procedure as follows:

dbEmployees.WP_SP_GET_TEXT_CUSTOM_FIELD("PAYROLL_EMPL_CUSTOM_INFO", _
sqlCustomControls.FIELD_NAME, "EMPLOYEE_NO", txtEmplNo.Text, strTextValue)
devCtrl.Text = strTextValue


0
bobbysmarina
Asked:
bobbysmarina
1 Solution
 
cyberkiwiCommented:

Alter PROCEDURE [dbo].[WP_SP_GET_TEXT_CUSTOM_FIELD]
       @TableName T_NAM,
       @ColumnName T_NAM,
       @WhereColumn T_NAM,
         @Criteria T_NAM,
       @ColumnValue T_NAM Output

AS

BEGIN
      
      Declare @SqlString varchar(1000)
      declare @tmp table (T T_NAM)
            
      Begin Try
      
            Set @SqlString = 'SET QUOTED_IDENTIFIER OFF
                                   SELECT ' + quotename(@ColumnName)
                                             + ' FROM ' + quotename(@TableName)
                                   + ' WHERE ' + quotename(@WhereColumn) + ' = "' + @Criteria + '"'
      INSERT @tmp
      Exec(@SqlString)
      
      SELECT @ColumnValue = T from @tmp
      Return 0

      End Try

      Begin Catch
            
            Return ERROR_NUMBER()

      End Catch

END

Open in new window

0
 
bobbysmarinaAuthor Commented:
That works. Thank you very much.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now