Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Recursive Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 2:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
Not to mention that this is using global temp tables. What happens when you have two users using this proc? The first person's data will be truncated when the second person starts executing this. Add to that the sql injection vulnerability and it seems you need to revisit what you are trying to do here and find a more suitable approach.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1503302
Posted Wednesday, October 9, 2013 8:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:40 PM
Points: 10, Visits: 194
Thank you Sean Lange, I did not think about it. I liked the idea of "Jeff Moden" but I am not sure about how to create a partitioned view.

I would really appreciate if someone can help me or direct me to a forum having similar kind of solution.

Thanks again.
Post #1503353
Posted Wednesday, October 9, 2013 8:40 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 597, Visits: 937
Take a look at the following link on how to create, use and manage partitioned views:
http://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503357
Posted Wednesday, October 9, 2013 8:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:40 PM
Points: 10, Visits: 194
Thank you very much Keith for your concern, I will look into this.
I am also trying to do a "UNION ALL" but not sure how to do it dynamically like...if a user passes 2 states it should do union all between 2 result sets and if he passes 3 states then it should do union all between three result sets.

thanks again Keith.
Post #1503358
Posted Monday, October 14, 2013 2:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:40 PM
Points: 10, Visits: 194
Thanks to all, I got this solved. I have assigned a GUID to the temp table name and built/select/drop it dynamically. I have tested it and it is working perfect.

Once again thanks everyone.
Post #1504533
Posted Tuesday, October 15, 2013 8:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
sarafpradeep (10/9/2013)
Thank you Sean Lange, I did not think about it. I liked the idea of "Jeff Moden" but I am not sure about how to create a partitioned view.

I would really appreciate if someone can help me or direct me to a forum having similar kind of solution.

Thanks again.


Read about the "partitioned view" in Books Online. If you don't know what that is, it's the "help" system for SQL Server and you can usually get there by pressing the {f1} key in SSMS. You should read about it... it'll really simplify this problem for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1504779
Posted Tuesday, October 15, 2013 8:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
sarafpradeep (10/14/2013)
Thanks to all, I got this solved. I have assigned a GUID to the temp table name and built/select/drop it dynamically. I have tested it and it is working perfect.

Once again thanks everyone.


That's still a lot of overkill. Read about the partitioned views I told you about. Also, if you've gone to a GUID name for a Global Temp table to try to overcome the "problems" of using dynamic SQL, then you're asking for trouble in the future.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1504781
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse