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.
| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-01-04 : 15:27:18
|
| If we don t have the right to create an SP, can we create a dynamic in memory kind of SP what s the syntax pls?Thanks a lot |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-04 : 15:36:17
|
| That isn't possible.Tara Kizer |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-04 : 15:58:13
|
You can create a temporary stored procedure.declare @procname varchar(40)set @procname = '#temp'exec('create proc '+@procname+' as select One = 1 ')exec @procnameexec ( 'drop proc '+@procname)Results:One ----------- 1(1 row(s) affected) CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-04 : 16:01:29
|
| But how does that work for someone who can't create stored procedures?Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-04 : 16:06:26
|
| I tested that code with an unprivledged sql login, and it worked fine. You don't have to grant permission for someone to create temp tables either, so I guess it works the same.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-04 : 16:11:26
|
| Ok cool. Learned something new today.I can't imagine why anyone would want to do this though.Tara Kizer |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-01-04 : 17:10:37
|
| i don t need to create a temporary SP any more. But yes we learned a lot. thanks guys |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-04 : 17:24:54
|
quote:
Originally posted by tkizer
Ok cool. Learned something new today.I can't imagine why anyone would want to do this though.Tara Kizer
I have done it within complex reporting stored procedures for a data warehouse. It had to support many slightly different versions of the same reports grouped at different levels.It was easier to have the main stored procedure generate a dynamically created stored procedure than to create and test 50 slightly different versions of the same report procedure.The reporting application supported about 5,000 different reports, but most were just variations within main types, so creating a new proc for each one was just too much work. Especially if you found a bug and had to fix it in 50 different procedures.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-04 : 19:44:15
|
quote:
Originally posted by rtutus
i don t need to create a temporary SP any more.
Really? Why did you need it on first place with hurry?Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
can we create a kind of temporary SP