stored procedure question

[nightfox02]

I am currently successfully running a stored procedure in sql server 2000 query analyzer, by inputting the proper parameters. However, when I try to run it with the vb data environment, it bugs out telling me there is no recordset. I am supplying the exact same parameters in vb which work in sql?


I have also triple checked that the stored procedure return recordset checkbox is set to true, in the data environment


Has anyone ever had this seemingly simple problem before. My stored procedure contains dynamic SQL that is the only hangup that I can see....

CREATE PROCEDURE sp_GetOverseastoOverseasCost @Region1 char(10), @Region2 char(10), @Rank char(20)
AS
declare @SQLStatement varchar(255)

set @SQLStatement= 'select [' + @Rank + '] from tblIICT where R1= (' + '''' + @Region1 + '''' + ') AND R2=( ' + '''' + @Region2 + '''' +' )'

print @sqlstatement
exec(@SQLStatement)
GO

[Mr.No]

Add these lines after the declare statement:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF


And then may be you should comment the print statement when you run it through VB.

At the end of the stored procedure add

SET NOCOUNT OFF

[buddu]

try to avoid the Dynamic Sql Statements in Stored Procedure. It will reduce the Peroformence.


for more information

http://builder.com.com/article_guest...20206kev01.htm