Dynamic SQL and Exec returning a value

[DaveBo]

Is it possible to Exec a dsql statement and return a simple value to a local var? e.g

Set @RecCnt = Exec('Select Count(ID) from tblContacts Where FirstName=''David''')

This doesn't work, and note, I need to use DSQL here because I'm actually building up that Select statement each time.

Right now I do this:

Declare @RecCnt Int
Create Table #tmp1 ( RecCnt Int )
Insert into #tmp1 Exec('Select Count(ID) from tblContacts Where FirstName=''David''')
Select @RecCnt = RecCnt From #tmp1
Print @RecCnt
Drop table #tmp1

But, I'd rather not have to deal with the temp table.

Thanks, DaveBo

[salvelinus]

Not familiar w/T-SQL syntax, but why can't you just SELECT the Count into a recordset that will only have one record? No temp table.
In DAO (stop booing! It's what I'm stuck with at work!):

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("SELECT Count(ID) from tblContacts Where FirstName='David'")
MsgBox rs.Fields("ID")

I think, but not sure, that in VB.Net you also have an option to return a scalar value, which with the same caveat, think only returns one value. Been a few years since I coded in .Net, so can't be sure there.

[MaximilianMayrhofer]

You could also do the same thing with ADO using the following:

Public Class YourForm
 
    Dim ds As New DataSet()
    Dim da As New OleDb.OleDbDataAdapter()
    Dim conn As New OleDb.OleDbConnection()
 
    Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourPath\Yourfilename.mdb; User Id=admin;Password=;"
        
        da.SelectCommand = New OleDb.OleDbCommand("SELECT Count(ID) from tblContacts Where FirstName='David'")
        da.SelectCommand.Connection = conn
        conn.Open()
        da.Fill(ds)
 
        MsgBox(ds.Tables(0).Rows(0).Item("ID").ToString)
 
        conn.close()
 
    End Sub
 
End Class

[brucevde]

Is it possible to Exec a dsql statement and return a simple value to a local var? e.g

Using Temp tables is the only way. The dynamic statement is "Executed" in a separate batch and batches cannot access each other's local variables.

Edit: Temp tables is not the only way. Apparently you can accomplish this using sp_ExecuteSQL and Exec.

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @MaxCustomer varchar(30);

SET @SQLString = N'SELECT @Output = Max(CustomerId) FROM Customers'
SET @ParmDefinition = N'@Output varchar(30) OUTPUT'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @Output = @MaxCustomer OUTPUT

SELECT @MaxCustomer

[szlamany]

@brucevde - nice find!

I'm sure it's just a wrapper for doing it the old fashion way - but it certainly does make it look neater.

After looking into the MASTER DB I found that it's an EXTENDED SP - probably meaning it's written in a higher level language - compiled down to a DLL - and made to look like a normal T-SQL sproc.

I would imagine - and I've not tested this - that it might be slower.

[szlamany]

Actually the sp_executesql is incredibly faster.

I did this as a test

set nocount on
create table #result (val int)
declare @i int
declare @val int
set @i=1
While @i<25000
Begin
	insert into #result exec('select 1')
	set @val=(select val from #result)
	truncate table #result
	Set @i=@i+1
End
drop table #result

That 25000 iterations took 4 seconds.

I tried this - it was so fast I had to change the iterations to 250,000!

set nocount on

declare @i int
declare @val int

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
SET @SQLString = N'SELECT @Output = 1'
SET @ParmDefinition = N'@Output int OUTPUT'

set @i=1
While @i<250000
Begin
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @Output = @Val OUTPUT
	
	Set @i=@i+1
End

The 250,000 took 4 seconds!

New respect for extended stored procedures!

[DaveBo]

Brucevde, excellent. Just what I always wanted (sort of)

I'm going to have some fun, and maybe even do something useful, with this.

Thanks, DaveBo