Build SQL statement - Quotation Mark dilemma 3

[ISPrincess]

I am having problems with placemen t of quotation marks when attempting to build a sql statement in stored proc.

At least I think that is my problem.

declare @sql varchar(2000)
declare @fulldbname varchar(50)
declare @intTheID integer

set @fulldbname = 'ServerA.DB1.dbo.Tablename'
set @intTheID = 183254219

set @sql = 'select top 100 * from ' + @fulldbname + ' where TheID = "' + @intTheID + '"'


on exec i get the following error:

Syntax error converting the varchar value 'select top 100 * from 'ServerA.DB1.dbo.Tablename where TheID = "' to a column of data type int.


I know the variable fulldbname is working because I can execute without the 'Where' clause.

Quotation mark help would be Extremely appreciated.

Thank you!

PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[psprague]

If the column TheID is of type int then the extra quotes are not required.


Try:
set @sql = 'select top 100 * from ' + @fulldbname + ' where TheID = ' + @intTheID

Instead of:

set @sql = 'select top 100 * from ' + @fulldbname + ' where TheID = ''' + @intTheID + ''''

 

[royjimenez]

u can use

set quoted_identifier on/off

or try this

select 'hi!'
select '''hi!'''
select '''''hi!'''''

to see the difference

regards



The life is too short to cry and long enough to try it... God bless us.

 

[ISPrincess]

Thanks! I converted the int to a string and just tacked that on at the end w/o quotes and it works.

Plus, I set quoted ident off.

You both sent me in the right direction (as usual)!

Greatly appreciated!



PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[Lekar]

As psprague says, the quotes are not required but the @IntTheID variable should be enclosed within the Str() function.


Set @SQL = 'Select Top 100 * From ' + @FullDBName + ' Where TheID = ' + Str(@IntTheID)

 

[ISPrincess]

Since you were so great in helping the first time, I have moved on to another dilemma.

My real @Sql statement is actually an update (not select) and
Since I need to build the @sql as above then exec it, how can I find out if the Update affected any records?


PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[SQLSister]

Look at @@rowcount in Books Online

 

[Lekar]

Checking the value od the @@RowCount variable.

 

[Lekar]

I need to write faster. :-(

 

[SQLSister]

I think we pretty much posted simultaneously.
At least we both agreed!

 

[ISPrincess]

Thank you SQLSis and Lekar - this looks like that will do it - once I can get back to programming!


Happy New Year.


PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips