Dynamic SQL Variable In SP 2

[BradF]

I'm building a dynamic SQL statement in a MSSQL SP. After it's built, I want to set a variable to the only record it returns. Something like this:

SET @strCountSQL = 'SELECT COUNT(*) FROM (' + @strSQL + ') cnt'

SET @intRecordCountQY = EXEC(@strCountSQL)

Of course, it doesn't work - Incorrect syntax near the keyword 'EXEC'. - Does anyone know of a way to do something similar?

Yes, the query has to be built dynamically and no I can't call another stored procedure.

 

[simonchristieis]

what about:

declare @intRecordCountQY int

SET @strCountSQL = 'SELECT @intRecordCountQY = COUNT(*) FROM (' + @strSQL + ') cnt'

EXEC(@strCountSQL)

print @intRecordCountQY

 

[BradF]

I tried that, because I thought it would work. However, I get the message that I must declare @intRecordCountQY even though it's already declared. I'm guessing that it's not passed to and returned from the EXEC function. I also tried this:

SET @strCountSQL = 'DECLARE @intRecordCountQY INT SELECT @intRecordCountQY = COUNT(*) FROM (' + @strSQL + ') cnt'

That gets rid of the error, but the variable is not seen in my SP after that EXEC statement runs.

 

[JamesLean]

You need to do something like this:

DECLARE @sql nvarchar(200),
  @params nvarchar(100),
  @cnt int

SET @sql = N'SELECT @c = COUNT(*) FROM (' + @strSQL + ') cnt'
SET @params = N'@c int OUTPUT'

EXEC sp_executesql @sql, @params, @cnt OUTPUT

PRINT @cnt

--James

 

[sunila7]

Try this:

Declare @SQL nvarchar(1000)
declare @intRecordCountQY int

SELECT @SQL = SELECT @intRecordCountQY = COUNT(*) FROM (' + @strSQL + ') cnt'

EXECUTE sp_executesql @SQL, N'@intRecordCountQY INT OUTPUT', @intRecordCountQY OUTPUT

Sunil

 

[BradF]

Thanks guys, I'll have to get with my database admins to make sure that is okay to run. Do you know anything about the overhead of such a statement so I can present an argument in case they don't want to allow me to run it. We have fairly strict coding standards. I had found that on the internet but wasn't sure if I could use it. Since you both seem to think it's the only/best way, it looks like I have no choice.

 

[JamesLean]

Well, if they are OK with you running dynamic SQL with EXEC then there should be no reason why they should have a problem using sp_executesql. There's certainly no real performance difference between the two.

If you had to use EXEC you would have to create a temp table, insert the results of the EXEC into it and then read it back from your main code - this would arguably not perform as well as a single call to sp_executesql.

--James

 

[BradF]

James, thanks a lot - we normally aren't allowed to run dynamic SQL, for obvious reasons. But we are allowed when making calls to the Oracle database which is what I'm doing (thus resulting in the dynamic SQL).

 

[roccorocks]

Just FYI, not sure if you understand Dynamic SQL or not. Just remmeber that when you are using

EXECUTE sp_executesql

you HAVE to use an NVarchar data type for the executed portion (as James and Sunilla showed above). Just a little heads up.

Rocco

 

[BradF]

Rocco: Yep, I knew that. Thanks though!