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


bobbysmarinaAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:

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
All Courses

From novice to tech pro — start learning today.