How to catch a value returned by a Dynamic Sql 1

[tumtoo10]

Well I have a specific dynamic sql statement , which is made at run time. Now i execute this statement and want to catch the result of this dynamic sql inside a procedure and want this procedure to return that value to my form in VB. How can i move about ?

 

[jhall156]

There's nothing wrong with building a SQL String dynamically but if you execute() the string in SQL Server I don't think you can return a local variable from inside the SQL string to the connection. Are you executing a SQL Server sp from a vb client or executing a SQL statement directly from a SQLServer OLE DB connection? JHall

 

[tlbroadbent]

Use sp_executesql to execute your dynamic query. You can return a value (output parameter) when you use this system stored procedure. See the following article.

http://support.microsoft.com/support/kb/articles/Q262/4/99.ASP

Here is an example:

Declare @EmpNo nchar(6), @EmpName nvarchar(30), @sqlstring nvarchar(200)

Set @EmpNo=''
Set @EmpName='berlioz'

SET
@SQLString = N'Select @EmpNo = LifeNumber From ' +
'Employees Where EmpName Like @EmpName + ''%'''

EXEC sp_executesql
@SQLString,
N'@EmpName nvarchar(30), @EmpNo nchar(6) Output',
@EmpName=@EmpName,
@EmpNo=@EmpNo Output

Select @EmpNo -- Show the result Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[jhall156]

Once again Terry gives an answer that allows me to simplify a number of my existing procedures. I have to genuflect every time I read one of your posts. Many thanks! (Again)
-John JHall