Dynamic SQL with scalar variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL with scalar variable

Author

Topic

allend2010
Starting Member

28 Posts

Posted - 2003-06-05 : 10:33:36

Hello:

Does anybody no if it is possible at all to execute a dynamic sql statement with a scalar result and assign it to a variable.

For example:

DECLARE @cnt INT
DECLARE @tblName VARCHAR(50)
DECLARE @dynSQL VARCHAR(1024)

SET @tblName = 'Titles'
SET @dynSQL = 'SELECT COUNT(*) FROM ' + @tblName + ';'

--This will work
EXECUTE (@dynSQL)

--But this will not
EXECUTE @cnt = @dynSQL
--Neither will this
SET @cnt = EXECUTE(@dynSQL)
--Or this
EXECUTE(@cnt = @dynSQL)

The reason I want to do it like this is because I am using a user defined function that has to return a scalar value.

Thanks in advance,
Allen D.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 11:03:20

I thought something like this..but the temp table is referneced after the dynmaic sql...I'm assuming that's because the execute must be ist own "session"


USE Northwind
GO

DECLARE @SQL VARCHAR(8000), @x int, @TBNAME sysname

SELECT @TBNAME = 'Orders'

SELECT @SQL = 'SELECT COUNT(*) as x INTO #bkTemp FROM ' + @TBNAME

SELECT @SQL

EXEC (@SQL)

SELECT @x = x FROM #bkTemp
 
SELECT @x
GO

Drop TABLE #bkTemp
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 11:05:29

Ok, got it...but I really think you should revist your design..keep it simple..


USE Northwind
GO

DECLARE @SQL VARCHAR(8000), @x int, @TBNAME sysname

SELECT @TBNAME = 'Orders'

CREATE TABLE #bkTemp (x int)

SELECT @SQL = 'INSERT INTO #bkTemp(x) SELECT COUNT(*) FROM ' + @TBNAME

SELECT @SQL

EXEC (@SQL)

SELECT @x = x FROM #bkTemp
 
SELECT @x
GO

Drop TABLE #bkTemp
GO

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-05 : 12:00:20

Did you take a look at sp_executesql in the Books Online?
Owais

allend2010
Starting Member

28 Posts

Posted - 2003-06-05 : 12:03:30

Thanks for your suggestions. I got around using dynamic sql by using if statements for one of my variables. In other words I did something like

IF (@tblName = 'Titles')
SELECT @cnt = COUNT(*) FROM Titles
ELSE
IF (@tblName = 'Authors')
SELECT @cnt = COUNT(*) FROM Authors

etc...

It will be a little slower and is more tedious coding but I am able to get the result that I need in my calling sp.

Thanks,
Allen D.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 16:31:54

I doubt it will be slower...Dynamic sql will be almost always slower..

Also, I'm wondering, this isn't going to be a sproc, is it.

There are several articles (recently discussed) that talk about getting counts for all tables in a database.

do a forum search (the little menu bar at the top).

I'd try COUNT ROWS TABLES DATABASE

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources