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