Output in local variable from dyanamic sql execution in sqlserver 2000 1

[spaliwal]

I am facing a problem while using dynamic sql , i want the result back in local variable from dynamic sql execution but sql server 2000 not able to given results in local variable b'coz of out of scope(context of dynamic sql execution is sepetate) , alternativly i am using SELECT INTO clause and taking output in a temporary table.But its a costly operation , since these dyanmic statement i am using lacs of times in data migration from dbf to sql server 2000.
I will be greatful if anybody suggest any other cheaper method of taking output into local variable from dynamic sql.
Sample SP which i am executing is as given below -

----------------------------------------------------------

CREATE PROCEDURE dbo.GetForeignOID
@PKTableName varchar(30),
@PKBKName varchar(30),
@PKBKValue varchar(30),
@ForeignOID varchar(32)
OUTPUT AS
Declare @CmdString nvarchar(500)
Declare @ReturnVal bit

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RETOID' and XTYPE = 'U' )
DROP TABLE RETOID

SET @CmdString = N'SELECT OID INTO RETOID FROM '+@PKTableName + ' WITH (NOLOCK) WHERE ' + @PKBKName + ' = ''' + @PKBKValue + ''' AND DELETEOID = ''0'''

EXEC sp_executesql @CmdString

SET @ForeignOID = 0

SELECT @ForeignOID = ISNULL(OID,0) FROM RETOID

IF @ForeignOID = 0
SET @ForeignOID = null

RETURN

--------------------------------------------------------

 

[chelandrew]

Try using the Virtual Tables feature in sql server 2000. They act like temp tables but are faster and more efficient.

 

[tlbroadbent]

sp_executesql can return output parameter values.

SET @ForeignOID = ''

SET @CmdString =
N'SELECT @OID=Isnull(OID,'') FROM ' +
@PKTableName + '
WHERE ' +
@PKBKName + ' = ''' + @PKBKValue + '''
AND DELETEOID = ''0'''

EXEC sp_executesql @CmdString,
N'@OID varchar(32)',
@OID=@ForeignOID Output

Print @ForeignOID Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[spaliwal]

Dear tlbroadbent

Thanks a lot man. It tried your suggestgion to use parameter with sp_executesql, it worked alright , and I got 40 times performance improvement.

Again Thx,

Shailesh

 

[jromero63]

Thank you for all of your guidance and examples on using sp_executesql. I've applied the changes as detailed below and I do get a resultset back. However, I can NOT execute any other commands after my call to EXEC sp_executesql. Am I missing something?

ALTER PROCEDURE sp_JLR
@Period_ID int,
@Rider_ID int,
@Field_To_Query varchar(50),
@Override numeric(25,15) OUTPUT

AS

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @SQLString = N'SELECT ' + @Field_To_Query + ' AS [value]
FROM tblRidersByPeriod
WHERE period_id=@Period_ID
AND rider_id=@Rider_ID'

SET @ParmDefinition = N'@Field_To_Query varchar(100),
@Period_ID int,
@Rider_ID int,
@Override numeric(25,15) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@Field_To_Query=@Field_To_Query,@Period_ID=@Period_ID,@Rider_ID=@Rider_ID,@Override=@Override OUTPUT

PRINT 'I am back, or am I!?'

RETURN (@@ERROR)

My calling stored procedure:

DECLARE @period_id int
DECLARE @rider_id int
DECLARE @field_to_query varchar(100)
DECLARE @return_value numeric(25,15)
DECLARE @Error int

SELECT @field_to_query = 'Fixed_Retention_Base_Amt'
SELECT @period_id = 1
SELECT @rider_id = 1

EXEC @Error = sp_JLR
@Period_ID = @period_id,
@Rider_ID = @rider_id,
@Field_To_Query = @field_to_query,
@Override = @return_value OUTPUT

PRINT 'Override = ' + CONVERT(varchar(100),@return_value)



Regards,
Juan