Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-08-27
2
475 Views
Last Modified: 2012-05-10
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
Comment
Question by:bobbysmarina
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33546919

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
 

Author Comment

by:bobbysmarina
ID: 33547439
That works. Thank you very much.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question