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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/20/2001 :  11:23:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
One of the most common questions we get involves Dynamic SQL. We have some articles that cover it but none that really start with the basics. So Merkin sat down and wrote this introduction to dynamic SQL. Using dynamic SQL you can put a SQL statement inside a variable and execute that statement. It's what you have to do when you're trying to run Select * from @TableName. Thanks Merkin!

Article Link.

Teroman
Posting Yak Master

United Kingdom
115 Posts

Posted - 06/22/2001 :  13:03:07  Show Profile  Send Teroman an AOL message  Reply with Quote
Another problem with dynamic SQL is permissions, you need select permission on the base data table you query as opposed to procedure level permissions if the proc is non dynamic.

May be a problem for some, i know it is for me ;)

Could be something to do with SQL Server working out permissions at compliation time, and not knowing whats going to be needed, but thats a bit of a stab in the dark to be honest.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/22/2001 :  19:58:47  Show Profile  Visit nr's Homepage  Reply with Quote
what about sp_executesql?
what about dynamic calls to SPs and returning output params?

==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/22/2001 :  21:47:15  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hey guys

Wait a few days for Part 2. I spilt this up into two articles. The second one covers permissions, scope and sp_executesql.



Damian
Go to Top of Page

KnooKie
Aged Yak Warrior

United Kingdom
623 Posts

Posted - 06/26/2001 :  08:12:41  Show Profile  Visit KnooKie's Homepage  Reply with Quote
Do you know when part 2'll be ready ?

I'm not rushing you or anything ! Just wondering.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/26/2001 :  08:56:12  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hey

Anytime soon. Probably in a day or two.



Damian
Go to Top of Page

karnel
Starting Member

Singapore
4 Posts

Posted - 06/27/2001 :  22:28:26  Show Profile  Reply with Quote
quote:

Hey guys

Wait a few days for Part 2. I spilt this up into two articles. The second one covers permissions, scope and sp_executesql.



Damian



Go to Top of Page

karnel
Starting Member

Singapore
4 Posts

Posted - 06/27/2001 :  22:32:39  Show Profile  Reply with Quote
Select field1, field2 from Table2
group by (select groupfield from Table1 where fieldkey = 'A')

Groupfield will return a fieldname of Table2.
Can this be done with dynamic SQL and how?

Karnelia

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/28/2001 :  12:14:43  Show Profile  Visit nr's Homepage  Reply with Quote
?

declare @cmd varchar(1000)
select @cmd = 'Select field1, field2 from Table2 group by ' + groupfield
from Table1 where fieldkey = 'A'
exec (@cmd)



==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

karnel
Starting Member

Singapore
4 Posts

Posted - 06/28/2001 :  22:28:17  Show Profile  Reply with Quote
quote:

?

declare @cmd varchar(1000)
select @cmd = 'Select field1, field2 from Table2 group by ' + groupfield
from Table1 where fieldkey = 'A'
exec (@cmd)



==========================================
Cursors are useful if you don't know sql.



Go to Top of Page

karnel
Starting Member

Singapore
4 Posts

Posted - 06/28/2001 :  22:31:52  Show Profile  Reply with Quote
Sorry, I don't understand.
Select groupfield from Table1 where fieldkey = 'A' might return more than 1 result(record).

So,
Select field1, field2 from Table2
group by (Select groupfield from Table1 where fieldkey = 'A')

can be grouped by different field for each record in Table2.
Can it be done without cursor ?

Thanks,
Nel


quote:

?

declare @cmd varchar(1000)
select @cmd = 'Select field1, field2 from Table2 group by ' + groupfield
from Table1 where fieldkey = 'A'
exec (@cmd)



==========================================
Cursors are useful if you don't know sql.



Go to Top of Page

liya_kats
Starting Member

1 Posts

Posted - 07/05/2001 :  11:03:31  Show Profile  Reply with Quote
[Very often I have to create tables on the fly for surveys having special format. To achieve this I am using dynamic SQL. The following will demostrate it.
CREATE PROC Survey_352
@SurveyID int,
@CompID int
AS

--find all employees
SELECT DISTINCT r.CompID,r.EmpID, EmpFN, EmpLN
INTO #temp1
FROM tblReply r
JOIN main..tblEmployee e
ON r.EmpID = e.EmpID
AND r.CompID = e.CompID
WHERE SurveyID = @SurveyID
AND r.CompID = @CompID
DECLARE @counter int, @SQL varchar(350), @PredefAnswerID int, @QuestionCounter int

SELECT @QuestionCounter = COUNT(QuestionID) FROM tblQuestions
WHERE SurveyID = @SurveyID
SET @counter = 1
WHILE @counter <= @QuestionCounter
BEGIN
SET @SQL = 'ALTER TABLE #temp1 ADD Question' + CAST(@counter as varchar) + ' text'
EXEC (@SQL)
SET @SQL = 'UPDATE t SET Question' + CAST(@counter AS varchar) + ' = DisplayText FROM #temp1 t JOIN tblReply r ON t.EmpID = r.EmpID and t.CompID = r.CompID JOIN tblPredefAnswers a on r.Reply_PAID = a.PredefAnswerID where SUrveyID = @SurveyID and QuestionID = ' + cast(@counter as varchar)
EXEC (@SQL)
IF (@@ROWCOUNT = 0)
BEGIN
SET @SQL = 'UPDATE t SET Question' + CAST(@counter AS varchar) + ' = Reply_Text FROM #temp1 t JOIN tblReply r ON t.EmpID = r.EmpID and t.CompID = r.CompID where SUrveyID = @SurveyID and QuestionID = ' + cast(@counter as varchar)
EXEC(@SQL)
END
SET @counter = @Counter + 1
END

SELECT * FROM #temp1
]
Hey guys

Wait a few days for Part 2. I spilt this up into two articles. The second one covers permissions, scope and sp_executesql.



Damian
[/quote]
 


Go to Top of Page

cdharma
Starting Member

USA
2 Posts

Posted - 08/15/2001 :  11:57:28  Show Profile  Visit cdharma's Homepage  Reply with Quote
[quote]
what about sp_executesql?
what about dynamic calls to SPs and returning output params?

sp_executesql will render you a temporary execution plan, and use tempdb for the processing of the sp. Also, I do not think you can create a sp from within an sp. What else, the biggest downfall of dynamic SQL is that it is breeding sloppy DBA's.

I still recommend that you spend the time upfront to write the query, and as many permutations as needed, without using Dyanmic SQL to build your query. That is, afterall, the benefit of using Stored Procedures.


That being said, you can build some powerful queries using dynamic SQL:

declare @cmd nvarchar(5000)

set @cmd = 'Select * from table1 t1'
if @variable1 is not null
BEGIN
Set @cmd = @cmd + 'join table2 t2 on t1.field1 = t2.field2'
END
if IsNumeric(@variable2)
BEGIN
Set @cmd = @cmd + ' AND t1.field2 = ' + cast(@variable2 as int)
END
etc...
etc...

That is some powerful boys. Powerful stuff.

Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 08/15/2001 :  13:38:01  Show Profile  Reply with Quote
Does anyone have any time to get there heads around this one...

The main purpose of this post is for information - a fairly complex use of dynamic SQL (building dynamic SQL within dynamic SQL!).

I also do have a question (in bold below!).

This sp creates a view of all tables with a particular prefix, building the statement up from sysobjects and syscolumns - it has been written so that it can be run from any database and the view is created in the database passed as a parameter. The two main problems were:-

1) CREATE VIEW database_name.dbo.myview is not valid - you cannot specify the database name

I have resorted to using sp_executesql which can be forced to run in a different database by calling sp_executesql with database_name.dbo.sp_executesql.

2) A create view statement must be first in the batch and I want to pass parameters to the statement via dynamic sql

I put the parameters into tables which I can then access from a separate batch...

The final (unsolved) problem is that the maximum string you can send to sp_executesql is 4000 characters which is sometimes not long enough. Any ideas for a way around this? I can't even find the underlying code for sp_executesql?!

Here is the sp...

CREATE procedure sp_0200FinalView
(
@database varchar(100), -- name of database to create table in
@tablename varchar(100) -- name of table to be created
)
as

set nocount on

-- =============================================
-- Variable declarations
-- =============================================

declare @sql nvarchar(4000)
declare @fieldlist nvarchar(4000)
declare @tablelist nvarchar(4000)
declare @maxfield int, @maxtable int, @i int
set @i=0
set @sql=''
set @fieldlist=''
set @tablelist=''

-- =============================================
-- Store databasename and tablename in tables
-- =============================================

if exists (select name from sysobjects where name='databasename' and type ='U')
drop table databasename
if exists (select name from sysobjects where name='tablename' and type ='U')
drop table tablename
select @database as databasename into databasename
select @tablename as tablename into tablename

-- drop view if exists

set @sql = 'if exists (select name from '+@database+'.dbo.sysobjects '
set @sql = @sql+'where name='''+@tablename+'_DB_VIEW_ALL'' and type =''V'') '
set @sql = @sql+'begin use '+@database+' drop view '+@tablename+'_DB_VIEW_ALL end '

exec (@sql)

-- create table of variable names and tables

set @sql='create table cview (fieldname varchar(100),tablename varchar(100),id_num int identity(1,1)) '
set @sql=@sql+'insert into cview (fieldname,tablename) '
set @sql=@sql+'select fieldname=a.name, tablename=b.name '
set @sql=@sql+'from '+@database+'.dbo.syscolumns a '
set @sql=@sql+'inner join '+@database+'.dbo.sysobjects b on a.id=b.id '
set @sql=@sql+'left outer join '+@database+'.dbo.FileFormat c on c.fieldname=a.name '
set @sql=@sql+'where b.type=''U'' and substring(b.name,len('''+@tablename+''')+2,2)=''DB'' and '
set @sql=@sql+' not (transformation<>''C'' and b.name='''+@tablename+'_DB'') and '
set @sql=@sql+' a.name<>''cis_id'' '
set @sql=@sql+'order by a.name '
exec (@sql)

-- create table of table names and asign table id
create table tablenames (tablename varchar(100),table_id int identity(1,1))
insert into tablenames (tablename,table_id)
select tablename
from cview group by tablename

-- merge table and variable id information
select a.tablename,fieldname,table_id=cast(table_id as varchar(10)),id_num
into newcview
from cview a left join tablenames b on a.tablename=b.tablename

-- build sql code
set @maxfield=(select max(id_num) from cview)
set @maxtable=(select max(table_id) from tablenames)

while (@i<@maxfield) begin
set @i=@i+1
if (@i<@maxfield) set @fieldlist=@fieldlist+'a'+(select table_id+'.'+fieldname
from newcview where id_num=@i)+','
else set @fieldlist=@fieldlist+'a'+(select table_id+'.'+fieldname
from newcview where id_num=@i)
end

set @i=0
while (@i<@maxtable) begin
set @i=@i+1
if @i=1 set @tablelist=@tablelist+(select tablename+' a'+cast(table_id as varchar(10)) from tablenames where table_id=@i)
else set @tablelist=@tablelist+' left join '+
(select tablename+' a'+cast(table_id as varchar(10))+
' on a1.cis_id=a'+cast(table_id as varchar(10))+'.cis_id'
from tablenames where table_id=@i)
end

print @tablelist

-- store sql statement in a table

if exists (select name from sysobjects where name='fieldlist' and type ='U')
drop table fieldlist
select @fieldlist as fieldlist into fieldlist

if exists (select name from sysobjects where name='tablelist' and type ='U')
drop table tablelist
select @tablelist as tablelist into tablelist

-- wrap sql in dynamic sql so that sp_executesql can be used to run the sql in the selected database
select @sql = 'declare @test nvarchar(4000) '
select @sql = @sql + 'set @test='''' '
select @sql = @sql + 'select @test = ''create view ''+tablename+ ''_DB_VIEW_ALL as select a1.cis_id,'' from tablename '
select @sql = @sql + 'select @test = @test+fieldlist from fieldlist '
select @sql = @sql + 'select @test = @test+'' from '' + tablelist from tablelist '
select @sql = @sql + 'exec ' + databasename + '.dbo.sp_executesql @test print @test' from databasename

print @sql
exec sp_executesql @sql

drop table cview
drop table tablenames
drop table newcview
drop table databasename
drop table tablename
drop table fieldlist
drop table tablelist

go


Go to Top of Page

cdharma
Starting Member

USA
2 Posts

Posted - 08/15/2001 :  15:46:17  Show Profile  Visit cdharma's Homepage  Reply with Quote
I might not have understood the question correctly, but I typed this, so I am going to post it ;-)

declare @cmd nvarchar(2000)
declare @group_by sysname

Select @group_by=groupfiled from Table1 where fieldkey = 'A'
set @cmd = 'Select field1, field2 from Table2 group by ' + @group_by
exec sp_executesql @cmd


Go to Top of Page

martin
Starting Member

3 Posts

Posted - 09/30/2001 :  13:42:31  Show Profile  Reply with Quote
quote:

One of the most common questions we get involves Dynamic SQL. We have some articles that cover it but none that really start with the basics. So Merkin sat down and wrote this introduction to dynamic SQL. Using dynamic SQL you can put a SQL statement inside a variable and execute that statement. It's what you have to do when you're trying to run Select * from @TableName. Thanks Merkin!

Article Link.



Go to Top of Page

martin
Starting Member

3 Posts

Posted - 09/30/2001 :  13:43:02  Show Profile  Reply with Quote
quote:

nicely put cyril


Go to Top of Page

martin
Starting Member

3 Posts

Posted - 09/30/2001 :  13:45:18  Show Profile  Reply with Quote
nicely put cyril, most helpful.

Go to Top of Page

nedhale
Starting Member

1 Posts

Posted - 02/08/2007 :  21:52:33  Show Profile  Reply with Quote
PLEASE do a web search on SQL injection before using dynamic SQL like this. The first example given where the ArticleID is read from the query string is a great example of an ASP page with a huge security hole. If I typed in the URL "http://server/search.asp?ArticleID=1;Delete * From Users" your DB has just been compromised. There are lots of good tutorials available so please take a look. Wikipedia is a good place to start.

http://en.wikipedia.org/wiki/Sql_injection
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.17 seconds. Powered By: Snitz Forums 2000