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

Transact-SQL (2000)

SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-07 : 08:38:58

Maryam writes "Hi
Thanx for your useful comment about dynamic SQL. it works out for me, and gaint my attention.
Although it was complete, cause another question:
How I can make a long string. I mean I used sp_executesql with
a variable of nvarchar. but this data type has maximum length of 4,000 characters. And my Query string is much more..
I tried to Declare variable of ntext,text but SQL doesen't let for local variable.
Then I used char, varchar but again when I used the variable in front of sp_executesql, it caused error.
So Could you help me and say How to set a long string for Dynamic SQL?

Thanx

I look forward your answer"

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:56:17

Best bet is to concatonate strings. Something like

in a stored procedure it could be that these values are passed during the call.

Declare @@str1 nvarchar(4000), @@str2 nvarchar(4000) -- etc. as needed
-- set initial values for each string
Set @@str1 = ''
Set @@str2 = ''

-- Populate strings with actual values
set @@str1 = 'string values go here'
set @@str2 = 'string values go here'

if @@str2 is null

Raymond Laubert
MCDBA, MCITP:Administration, MCT

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:56:51

sorry.. here is the rest
IF @@str2 is null

Raymond Laubert
MCDBA, MCITP:Administration, MCT

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:56:51

sorry.. here is the rest
IF @@str2 is null

Raymond Laubert
MCDBA, MCITP:Administration, MCT

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:56:52

sorry.. here is the rest
IF @@str2 is null

Raymond Laubert
MCDBA, MCITP:Administration, MCT

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 11:58:20

Try again..
if @@str2 is null
exec sp_executesql @@str1
else
exec sp_executesql @@str1 + @@str2

Havnet tried this but should work.

Raymond Laubert
MCDBA, MCITP:Administration, MCT

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-08 : 13:24:49

quote:
Originally posted by rlaubert

Try again..
if @@str2 is null
exec sp_executesql @@str1
else
exec sp_executesql @@str1 + @@str2

Havnet tried this but should work.

Nope, that won't work, you'll need to use EXECUTE instead because you cannot give expressions to sp_executesql

if @str2 is null
exec (@str1)
else
exec (@str1 + @str2)

Raymond - Any reason why you use two @ signs for your variables? It seems confusing because @@ is used by the system for functions?

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-08 : 16:05:26

double @ is global... just got use to using them lately with stuff I am doing at work...You are right on using single though.

Raymond Laubert
MCDBA, MCITP:Administration, MCT

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-08 : 16:17:17

quote:
Originally posted by rlaubert

double @ is global... just got use to using them lately with stuff I am doing at work...You are right on using single though.

Raymond Laubert
MCDBA, MCITP:Administration, MCT

I thought you might think that - they aren't global, they used to be referred to as global variables (prior to version 7.0), but they were never global variables, they were global system values, and were renamed functions in version 7.0. So @@version and @@identity are now system functions. You can declare a variable with two @ signs in your code, but it's not global, it's no different to a variable one @ sign (in fact go ahead and put 15 @ signs if you like).
I'd say don't do it because if you do you run the risk of someone thinking it is something it isn't.
This is from the 2000 Books Online
Pre-SQL Server 7.0 SQL Server 7.0
Global variables were system-supplied, predeclared variables that were distinguished from local variables by having two at symbols (@@) preceding their names.
SQL Server 7.0
Transact-SQL global variables are a form of function and are now referred to as functions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources