Dynamic SQL - SQL Server Forums

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

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 10:11:07

Hey Guys,


SET @SQL = @SQL + ' IF (SELECT 1 FROM [' + @DATABASE + N']..SYSOBJECTS WHERE NAME = ''BASETABLE'') = 1 ' +
	N'AND ((SELECT 1 FROM ' + @DATABASE + N'..SYSOBJECTS WHERE NAME = ''UIDMAX'') = 1) ' +
	N'BEGIN SELECT 1 END ELSE BEGIN SELECT 0 END'
	
	--PRINT @SQL
	EXEC SP_EXECUTESQL @SQL, N'@OPENDB INT ', @OPENDB 
	SET @SQL = ''

I have the above code in a cursor. I expect the variable @OPENDB to be set to either 1 or 0 (which it does) but I don't want the result to be displayed on my screen until I specify a PRINT @OPENDB command. At the moment I am getting the following in the result window :

-----------
1

... and this is repeated (because of the cursor).

How do I turn this off? Or how do I just assign the result of the dynamic SQL statment to the variable without it being printed on the result screen?

Thanks.

BTW, I tried :


SET @SQL = @SQL + ' IF (SELECT 1 FROM [' + @DATABASE + N']..SYSOBJECTS WHERE NAME = ''BASETABLE'') = 1 ' +
	N'AND ((SELECT 1 FROM ' + @DATABASE + N'..SYSOBJECTS WHERE NAME = ''UIDMAX'') = 1) ' +
	N'BEGIN SET @OPENDB = 1 END ELSE BEGIN SET @OPENDB = 0 END'

... but the @OPENDB variable is always 0 as if the dynamic statment is not changing the value of the variable if the IF condition evaluates to TRUE.

----------------
Shadow to Light

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 10:15:59

I just posted that...where did I put it...

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 10:16:42

Here it is:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28286

Brett

8-)

SELECT POST=NewId()

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 10:21:45

quote:
Originally posted by X002548

Here it is:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28286

Brett

8-)

SELECT POST=NewId()

Ah... nicely done Brett, but is this efficient? This bit of code will be run on all our servers which hold live/installation databases. I will use your method to get the work going but... can you think of a better way?

Thanks.

----------------
Shadow to Light

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 10:27:21

Dynamic SQL is not efficient...

Why don't you "know" what sql needs to be called...

Seems like your trying to automate admin functions...

I'm guessing...

What are you trying to do?

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 10:28:00

Oh, and I saw your reply to Damain...why don't you come on out yourself?

Brett

8-)

SELECT POST=NewId()

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 10:39:17

quote:

Why don't you "know" what sql needs to be called...

Seems like your trying to automate admin functions...

I'm guessing...

What are you trying to do?

Good question and a good guess Brett.
I will try and be as brief as possible with this.

The current software that we use (like many other products) has a VB front end - SQL Server backend. The client migration involves the creation of 3 databases. Two that mirror the software database and a third specifically for the migration exercise.

Apparently, someone spotted some problems with the software database (sorry I can't give the name of it) and so I have to go through each server and find the software databases (not the migration databases or any other rubbish by another developer). This is essential as the server contains quite a large number of DBs at the moment.

Hence the :


IF (SELECT 1 FROM   OPEN_TEST..SYSOBJECTS WHERE NAME = 'BASETABLE') = 1  
AND (SELECT 1 FROM   OPEN_TEST..SYSOBJECTS WHERE NAME = 'UIDMAX') = 1

Once the database has been identified, I will then do some other work which I won't discuss on the forum!

----------------
Shadow to Light

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 10:42:51

quote:
Originally posted by X002548

Oh, and I saw your reply to Damain...why don't you come on out yourself?

Brett, not being rude or anything but I will give you 10 guesses, and while you're still guessing :

Music/SQL, SQL/Music, Ganja ...and women

'Exact order mind!'

I really don't want to talk about anything else to be honest.

..the rest is between me and Merkin. I think he caught my drift.

----------------
Shadow to Light

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources