SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to Dynamic SQL (Part 2)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/27/2001 :  08:47:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
In a follow up to the Introduction to Dynamic SQL we take you through some of the tricks and pitfalls of this technique. We'll cover Create Table, sp_executesql, permissions, scope and more.

Article Link.

jxbrad
Starting Member

1 Posts

Posted - 07/16/2001 :  09:00:31  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 07/16/2001 :  09:09:33  Show Profile  Visit robvolk's Homepage  Reply with Quote
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).

Go to Top of Page

Paul Thomas
Starting Member

USA
1 Posts

Posted - 09/20/2001 :  12:16:05  Show Profile  Reply with Quote
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?

Go to Top of Page

MarkyBoy
Starting Member

1 Posts

Posted - 01/07/2004 :  12:17:30  Show Profile  Reply with Quote
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

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/07/2004 :  13:22:39  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

yaaadman
Starting Member

4 Posts

Posted - 06/27/2007 :  09:25:39  Show Profile  Reply with Quote
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'
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 06/27/2007 :  09:37:45  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Don't piggyback on existing threads. Create a new thread with your question.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

misscrf
Starting Member

10 Posts

Posted - 08/31/2010 :  13:34:07  Show Profile  Send misscrf a Yahoo! Message  Reply with Quote
The work around to create a table with a table name variable is just what I was looking for. Thanks!

Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000