| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
jxbrad
Starting Member
1 Posts |
 Posted - 07/16/2001 : 09:00:31
|
Under the topic sp_executesql you have the letter 'N' preceding the parameter list as follows:
Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'
I'm not sure what the N means? Can you explain?
thanks |
 |
|
|
robvolk
Most Valuable Yak
USA
15568 Posts |
Posted - 07/16/2001 : 09:09:33
|
The N' prefix indicates national character, or nchar/nvarchar data. It tells SQL Server to convert a string ("ABCD") into nchar. The sp_executesql system stored procedure requires nchar data to be passed to it. Nchar is stored as two bytes per character instead of one, and allows all of the international characters to be stored correctly (except Chinese).
|
|
|
|
Paul Thomas
Starting Member
USA
1 Posts |
Posted - 09/20/2001 : 12:16:05
|
Hello... new guy here..
I was reading your article on dynamic SQL (part 2).
I am having problems with this code executing in a stored procedure on SQL2000. I am wondering if the reason is that it is out of scope also...
This is pseudo code... but I believe you will get the picture. No errors occur. Just the identity_insert isn't turned ON.
.....
Note: This is inside a cursor where @tables is obtained from:
SELECT @identity_str = 'SET IDENTITY_INSERT mydatabase.dbo.' + ltrim(rtrim(@tables)) + ' ON'
EXEC (@identity_srt)
Also, can you expand on what is meant by out of scope?
|
|
|
|
MarkyBoy
Starting Member
1 Posts |
Posted - 01/07/2004 : 12:17:30
|
The example of Dynamic SQL and creating a view is noit really dynamic at all.
Here is an example of some truely dynamic SQL to create a view:
DECLARE @DBSQL NVARCHAR (2000)
DECLARE @DB NVARCHAR (2000)
SET @DB = 'TargetDB'
SET @DBSQL = 'DECLARE @SQL NVARCHAR (2000)' + CHAR (13) + CHAR (10)
SET @DBSQL = @DBSQL + 'SET @SQL = ''CREATE PROCEDURE dbo.prFred as SELECT * FROM TABLENAME''' + CHAR (13) + CHAR (10)
SET @DBSQL = @DBSQL + 'EXEC ' + @DB + '.DBO.sp_EXECUTESQL @SQL'
EXEC sp_EXECUTESQL @DBSQL
|
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 01/07/2004 : 13:22:39
|
quote:
Originally posted by MarkyBoy
The example of Dynamic SQL and creating a view is noit really dynamic at all.
Here is an example of some truely dynamic SQL to create a view:
I don't know if there's such a thing as one example being more dynamic than another. You're quite right that the View example is not dynamic per se, it doesn't include any variables that are concatenated, or anything like that, but perhaps you overlooked the paragraph introducing his example of using dynamic SQL to create a view where he says, "sp_executesql is also useful when..."
--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc |
Edited by - AjarnMark on 01/07/2004 13:44:47 |
|
|
|
yaaadman
Starting Member
4 Posts |
Posted - 06/27/2007 : 09:25:39
|
DECLARE @Sql nvarchar(200)
SET @Sql = 'SELECT stag.id,
I can't get the below dynamic sql to work. I am getting this error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Any Help?
REPLACE(stag.broker,stag.broker,''Staging'' + stag.broker ) '
SET @Sql = @Sql + 'us.cocoid, ua.UserAccountID '
SET @Sql = @Sql + '(CASE WHEN UPPER(BuySell) = ''SELL'' THEN 0 ELSE 1 END) '
SET @Sql = @Sql + 'stag.tradeDate, CONVERT(INT, REPLACE(stag.quantity,''.000'','''')) '
SET @Sql = @Sql + 'stag.symbol '
SET @Sql = @Sql + 'FROM dbo.StagingSchwab stag, Users us, UserAccounts ua '
SET @Sql = @Sql + 'WHERE stag.cusip <> '''' AND us.cocoid = ua.cocoid '
SET @Sql = @Sql + 'AND REPLACE(ua.AccountNumber,''-'','''') = stag.AccountNumber' |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 06/27/2007 : 09:37:45
|
Don't piggyback on existing threads. Create a new thread with your question.
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
|
|
|
misscrf
Starting Member
10 Posts |
Posted - 08/31/2010 : 13:34:07
|
The work around to create a table with a table name variable is just what I was looking for. Thanks!
Thanks! |
|
|
| |
Topic |
|
Article: Introduction to Dynamic SQL (Part 2)
