dynamic sql

Question

dynamic sql

CooLDBA

Hall of Fame

Points: 3301
More actions
October 11, 2006 at 5:57 pm

#174385

In sql2000

set @DataItem = 'line'
set @DataElement = 'On'
set @sqlstmt = 'declare @cnt int'


   set @sqlstmt = @sqlstmt + ' SELECT @cnt = count(1) FROM #temp T
                   INNER JOIN dbo.VALIDATION_RULE R
                   on T.tmpPackageName = R. BCPackageName
                   and T.tmpPkgElementName = R.BCPkgElementName '

   set @sqlstmt = @sqlstmt + ' where R.BCPackageName = ''' + @DataItem + ''' '

   set @sqlstmt = @sqlstmt + ' and R.BCPkgElementName = ''' + @DataElement + ''' '

   print(@sqlstmt)
   exec(@sqlstmt)

print output - declare @cnt int SELECT @cnt = count(1) FROM #temp T
                   INNER JOIN dbo.BC_VALIDATION_RULE R
                   on T.tmpPackageName = R. BCPackageName
                   and T.tmpPkgElementName = R.BCPkgElementName where R.BCPackageName = 'line' and R.BCPkgElementName = 'On'

no output for exec(@sqlstmt), anyone know why?

If I run this declare @cnt int SELECT @cnt = count(1) FROM #temp T
                   INNER JOIN dbo.BC_VALIDATION_RULE R
                   on T.tmpPackageName = R. BCPackageName
                   and T.tmpPkgElementName = R.BCPkgElementName where R.BCPackageName = 'line' and R.BCPkgElementName = 'Ont'

print(@cnt)

output = 3

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

I think the reason is #temp table. Temp tables are created in tempdb not in the user db(with a long Name ..eg :#temp_______________6788) and when ur query tried to look for #TEMP in the current DB it doesn't find any.

It runs manually cos the temp table is still available to the connection.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Where do you create and populate #Temp?

Read about scope of # tables in BOL.

_____________
Code for TallyGenerator

CooLDBA Hall of Fame Points: 3301 More actions · Answer 3

your question not related to my question 'coz

I'm asking why if I run exec(@sqlstmt) there is no output

instead of running the stmt:declare @cnt int SELECT @cnt = count(1) FROM #temp T INNER JOIN dbo.BC_VALIDATION_RULE R on T.tmpPackageName = R. BCPackageName and T.tmpPkgElementName = R.BCPkgElementName where R.BCPackageName = 'line' and R.BCPkgElementName = 'On'

I get an output of 3 the value of the @cnt

something wrong with the dynamic sql...

Sergiy SSC Guru Points: 110208 More actions · Answer 4

If it would not be related I would not ask.

If you would read topic in BOL about Temporary Tables you would not say my question is not related.

_____________
Code for TallyGenerator

CooLDBA Hall of Fame Points: 3301 More actions · Answer 5

ok, I created and populated the #temp table before the stmt I provided above.

they all in a script I run in Query Analyzer.

IN BOL, Temporary Tables

SQL Server supports temporary tables. These tables have names that start with a number sign (#).

If a temporary table is not dropped when a user disconnects, SQL Server automatically drops

the temporary table. Temporary tables are not stored in the current database; they are stored in the

tempdb system database.

Local temporary tables

The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

what's your point? are you saying was drop? not possible...

Sergiy SSC Guru Points: 110208 More actions · Answer 6

You did not asked the question.

Point is: #Tables are limited to the scope.

If you created and/or populated #Table in another dynamic statement then this table was dropped as soon as that statement was copleted.

So, the way you populate #Table is crytical.

_____________
Code for TallyGenerator

CooLDBA Hall of Fame Points: 3301 More actions · Answer 7

what's crytical?

You mean critical?

Nope, I created #temp from different table using into stmt.

e.g. select * into #temp from basis

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Was it

select * into #temp from basis

or

SET @sql = 'select * into #temp from basis'

EXEC (@SQL)

?

_____________
Code for TallyGenerator

CooLDBA Hall of Fame Points: 3301 More actions · Answer 9

YES, just plain sql stmt...

select * into #temp from basis

Sergiy SSC Guru Points: 110208 More actions · Answer 10

You must use

exec sp_executesql @sqlstmt, N'@cnt int OUTPUT', @cnt = @cnt OUTPUT

PRINT @cnt

_____________
Code for TallyGenerator

Loner SSC-Insane Points: 21279 More actions · Answer 11

You cannot use #temp in dynamic SQL. Temp table only exists in current session. If will automatically delete after you finish the session. So if you use dynamic sql, it doess not recognize #temp because it is a different session. If you really need to use temp table in dynamic sql, you need to create global temp table ##Temp. Then ##Temp will stay in tempdb until you delete it manually.

If you use global temp table, be sure to delete it after you are done, otherwise it will stay in tempdb. You DBA will not be too happy.

David Burrows SSC Guru Points: 65144 More actions · Answer 12

You cannot use #temp in dynamic SQL

Yes you can

Provided it is created in the same procedure before using it in dynamic sql

and not created within dynamic sql (other than the dynamic sql itself)

Sergiy's last post is the answer

Far away is close at hand in the images of elsewhere.
Anon.

CooLDBA Hall of Fame Points: 3301 More actions · Answer 13

CooLDBA

Hall of Fame

Points: 3301

October 12, 2006 at 10:59 am

#665558

Thanks for the help Sergiy! You're the MAN!!!