Results 1 to 3 of 3

Thread: Problem with using EXECUTE/sp_executesql

  1. #1
    sudhakar Guest

    Problem with using EXECUTE/sp_executesql

    Hi... Everybody,

    I am new to using SQL Server and I present to you the following problem that I am facing:

    Can I use the 'EXECUTE' or 'EXECUTE sp_executesql' in a SELECT query that assigns a value to a declared variable ?

    To be more specific:
    I have the following set of SQL Statements that do not seem to work:

    ------------------------------------------------------------------------------
    DECLARE @CustomerID char(6)
    DECLARE @OfficeID char(3)
    DECLARE @DestinationAccountNo char(7)
    DECLARE @TableName char(30)

    SET @TableName = 'Users'
    SET @CustomerID = '001001'
    SET @OfficeID = '001'
    SET @DestinationAccountNo = '0001011'

    DECLARE @ExecuteString nvarchar(500)
    DECLARE @CurDestPatIDChar char(2)

    SET @ExecuteString = N'SELECT @CurDestPatIDChar = RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    EXECUTE SP_EXECUTESQL @ExecuteString
    PRINT @CurDestPatIDChar
    ------------------------------------------------------------------------------
    When I run this in the Query Ananlyzer I get the following error:
    Server: Msg 137, Level 15, State 1, Line 0
    Must declare the variable '@CurDestPatIDChar'.

    The above set of statements do not seems to work with EXECUTE either.

    Where as if I run the following query with the same variable declarations as above:

    -----------------------------------------------------------------------------
    SET @ExecuteString = N'SELECT @CurDestPatIDChar1 = RTRIM(CAST(Max(CAST([PatientID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    EXECUTE SP_EXECUTESQL @ExecuteString, N'@CurDestPatIDChar1 char(2)', @CurDestPatIDChar1 = @CurDestPatIDChar
    PRINT @CurDestPatIDChar
    -----------------------------------------------------------------------------

    I donot get any error messages but the variable '@CurDestPatIDChar' is not initialized.

    The problem seems to be that the execute statement interprets any variable assignments (here it is '@CurDestPatIDChar', defined as part of the execute string in quotes) as local to the execute statement.

    I shall be grateful if you can provide me with a solution for this,

    BR,

    Sudhakar

  2. #2
    Andrew Wiegand Guest

    Problem with using EXECUTE/sp_executesql (reply)

    Hi,
    You are facing a common problem. Which is "How do I get results back from an exec?"

    Make the following changes to your sql:
    DECLARE @CustomerID char(6)
    DECLARE @OfficeID char(3)
    DECLARE @DestinationAccountNo char(7)
    DECLARE @TableName char(30)

    SET @TableName = 'Users'
    SET @CustomerID = '001001'
    SET @OfficeID = '001'
    SET @DestinationAccountNo = '0001011'

    DECLARE @ExecuteString nvarchar(500)
    DECLARE @CurDestPatIDChar char(2)

    /* remove mention of CurDestPatIDChar variable. As you saw you won’t be able to reference this variable outside of the context of the exec statement
    */
    SET @ExecuteString = N'SELECT RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    /* NEW SQL */
    create table #holder (holder char(2))
    insert into #holder EXECUTE SP_EXECUTESQL @ExecuteString
    select @CurDestPatIDChar = holder from #holder
    drop table #holder
    PRINT @CurDestPatIDChar

    hth,
    andrew

    ------------
    sudhakar at 1/11/00 5:09:13 PM

    Hi... Everybody,

    I am new to using SQL Server and I present to you the following problem that I am facing:

    Can I use the 'EXECUTE' or 'EXECUTE sp_executesql' in a SELECT query that assigns a value to a declared variable ?

    To be more specific:
    I have the following set of SQL Statements that do not seem to work:

    ------------------------------------------------------------------------------
    DECLARE @CustomerID char(6)
    DECLARE @OfficeID char(3)
    DECLARE @DestinationAccountNo char(7)
    DECLARE @TableName char(30)

    SET @TableName = 'Users'
    SET @CustomerID = '001001'
    SET @OfficeID = '001'
    SET @DestinationAccountNo = '0001011'

    DECLARE @ExecuteString nvarchar(500)
    DECLARE @CurDestPatIDChar char(2)

    SET @ExecuteString = N'SELECT @CurDestPatIDChar = RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    EXECUTE SP_EXECUTESQL @ExecuteString
    PRINT @CurDestPatIDChar
    ------------------------------------------------------------------------------
    When I run this in the Query Ananlyzer I get the following error:
    Server: Msg 137, Level 15, State 1, Line 0
    Must declare the variable '@CurDestPatIDChar'.

    The above set of statements do not seems to work with EXECUTE either.

    Where as if I run the following query with the same variable declarations as above:

    -----------------------------------------------------------------------------
    SET @ExecuteString = N'SELECT @CurDestPatIDChar1 = RTRIM(CAST(Max(CAST([PatientID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    EXECUTE SP_EXECUTESQL @ExecuteString, N'@CurDestPatIDChar1 char(2)', @CurDestPatIDChar1 = @CurDestPatIDChar
    PRINT @CurDestPatIDChar
    -----------------------------------------------------------------------------

    I donot get any error messages but the variable '@CurDestPatIDChar' is not initialized.

    The problem seems to be that the execute statement interprets any variable assignments (here it is '@CurDestPatIDChar', defined as part of the execute string in quotes) as local to the execute statement.

    I shall be grateful if you can provide me with a solution for this,

    BR,

    Sudhakar

  3. #3
    sudhakar Guest

    Problem with using EXECUTE/sp_executesql (reply)

    Hi... Andrew,

    Thanks a lot.
    That really solved my problem.

    BR,

    Sudhakar


    ------------
    Andrew Wiegand at 1/11/00 5:40:10 PM

    Hi,
    You are facing a common problem. Which is "How do I get results back from an exec?"

    Make the following changes to your sql:
    DECLARE @CustomerID char(6)
    DECLARE @OfficeID char(3)
    DECLARE @DestinationAccountNo char(7)
    DECLARE @TableName char(30)

    SET @TableName = 'Users'
    SET @CustomerID = '001001'
    SET @OfficeID = '001'
    SET @DestinationAccountNo = '0001011'

    DECLARE @ExecuteString nvarchar(500)
    DECLARE @CurDestPatIDChar char(2)

    /* remove mention of CurDestPatIDChar variable. As you saw you won’t be able to reference this variable outside of the context of the exec statement
    */
    SET @ExecuteString = N'SELECT RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    /* NEW SQL */
    create table #holder (holder char(2))
    insert into #holder EXECUTE SP_EXECUTESQL @ExecuteString
    select @CurDestPatIDChar = holder from #holder
    drop table #holder
    PRINT @CurDestPatIDChar

    hth,
    andrew

    ------------
    sudhakar at 1/11/00 5:09:13 PM

    Hi... Everybody,

    I am new to using SQL Server and I present to you the following problem that I am facing:

    Can I use the 'EXECUTE' or 'EXECUTE sp_executesql' in a SELECT query that assigns a value to a declared variable ?

    To be more specific:
    I have the following set of SQL Statements that do not seem to work:

    ------------------------------------------------------------------------------
    DECLARE @CustomerID char(6)
    DECLARE @OfficeID char(3)
    DECLARE @DestinationAccountNo char(7)
    DECLARE @TableName char(30)

    SET @TableName = 'Users'
    SET @CustomerID = '001001'
    SET @OfficeID = '001'
    SET @DestinationAccountNo = '0001011'

    DECLARE @ExecuteString nvarchar(500)
    DECLARE @CurDestPatIDChar char(2)

    SET @ExecuteString = N'SELECT @CurDestPatIDChar = RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    EXECUTE SP_EXECUTESQL @ExecuteString
    PRINT @CurDestPatIDChar
    ------------------------------------------------------------------------------
    When I run this in the Query Ananlyzer I get the following error:
    Server: Msg 137, Level 15, State 1, Line 0
    Must declare the variable '@CurDestPatIDChar'.

    The above set of statements do not seems to work with EXECUTE either.

    Where as if I run the following query with the same variable declarations as above:

    -----------------------------------------------------------------------------
    SET @ExecuteString = N'SELECT @CurDestPatIDChar1 = RTRIM(CAST(Max(CAST([PatientID] AS decimal(2, 0))) AS char(2))) '

    SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)

    SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''

    EXECUTE SP_EXECUTESQL @ExecuteString, N'@CurDestPatIDChar1 char(2)', @CurDestPatIDChar1 = @CurDestPatIDChar
    PRINT @CurDestPatIDChar
    -----------------------------------------------------------------------------

    I donot get any error messages but the variable '@CurDestPatIDChar' is not initialized.

    The problem seems to be that the execute statement interprets any variable assignments (here it is '@CurDestPatIDChar', defined as part of the execute string in quotes) as local to the execute statement.

    I shall be grateful if you can provide me with a solution for this,

    BR,

    Sudhakar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •