Article: Introduction to Dynamic SQL (Part 2)

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

Site Related Forums

Article Discussion

Article: Introduction to Dynamic SQL (Part 2)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-06-27 : 08:47:29

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.

MarkyBoy
Starting Member

1 Post

Posted - 2004-01-07 : 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

3246 Posts

Posted - 2004-01-07 : 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 [url]http://weblogs.sqlteam.com/markc[/url]

yaaadman
Starting Member

4 Posts

Posted - 2007-06-27 : 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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-27 : 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 - 2010-08-31 : 13:34:07

The work around to create a table with a table name variable is just what I was looking for. Thanks!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources