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 123»»»

I'm trying to create a proc that will drop and create a function Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 8:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 148, Visits: 363
To keep this simple i'm going to include an example however there are actually several scripts that i need to do this to.

I have a manual process where i have, say, 30 scripts. Each one is dropping and creating all sorts of different sql objects. functions, tables, indexes, etc.

I want to be able to execute these scripts as a chunk. Like wrap each script in a stored proc. My problem is i need the "GO" in each to separate the if exists drop and create. Is there another way for me to wrap these scripts so i can make one call and the scripts runs?

Please advise if u have any questions. For some reason i dont feel i'm explaining it very well.
For example. Is there something i can wrap the below in so i make one call?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_GetCleanUsername]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_GetCleanUsername]


go

CREATE FUNCTION [dbo].[udf_GetCleanUsername] (@Data VarChar(50))
Returns VarChar(50)
As
Begin
Declare @Temp VarChar(50)
Declare @i Integer
Declare @Output VarChar(50)

Set @i = 1
Set @Output = ''

While @i <= Len(@Data)
Begin
Set @Temp = SubString(@Data, @i, 1)
If @Temp IN ('0','1','2','3','4','5','6','7','8','9') OR
@Temp IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
Set @Output = @Output + @Temp

Set @i = @i + 1
End
Return (Select @Output)
End
Post #1407304
Posted Tuesday, January 15, 2013 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 13,294, Visits: 12,143
Why don't you just combine all the scripts into a single file? Then you just add GO where needed and it can all run at once.

_______________________________________________________________

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 #1407313
Posted Tuesday, January 15, 2013 9:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 148, Visits: 363
i suppose i could if i had to however some of them are dependent. So, I'd have to modify the contents or add some sort of try catch or error logic. These scripts are several thousand lines long and extremely complex. i was hoping i could just call each .sql file somehow and wrap the error logic around each call. Something along those lines. I don’t really have time to modify the contents of these scripts not the testing cycles. I would have thought that sql 2008 would have designed a way to wrap strips w/ GO in them.
Post #1407328
Posted Tuesday, January 15, 2013 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 148, Visits: 363
i suppose i could use sql cmdshell to call a .sql file and put them in sequencial steps in a job. That would also allow for me to track runtime and if it failed or succeded
Post #1407330
Posted Tuesday, January 15, 2013 10:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
GO is a batch separator, not a T-SQL command, so it really doesn't work that way. That's why you can change to something other than "GO" in your SSMS preferences, but you can't change something like "SELECT" there.

I'm used to combining scripts and building in the necessary error handling. Makes it a lot easier to manage the whole thing when I'm ready to migrate from my dev environment to my test environment, and then again to production, if it's one file. Can't accidentally run the third file second that way, or anything like that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1407355
Posted Tuesday, January 15, 2013 10:28 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
SSIS package? Have a table with list of .sql files to run and have a loop container to run each?

---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1407369
Posted Tuesday, January 15, 2013 11:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 1,945, Visits: 2,906
I have a manual process where I have, say, 30 scripts. Each one is dropping and creating all sorts of different SQL objects, functions, tables, indexes, etc.


This is the worst possible way to use SQL. The RDBMS model is based on logic; logic has “Universe of Discourse” (Google it!). But your mindset has a magical world in which things drop out of the sky.

You put the “udf_” prefix on function names! Back in the days of FORTRAN II, the compilers were simple one-pass tools that used a first letter between 'I' and 'N' to signal the software that the variable was an Integer! You even used “DECLARE @i INTEGER” and almost made me cry from nostalgia. I had 1960's FORTRAN flashbacks! Local variables are not used in declarative languages like SQL. We nest expressions.

Today, in 2013, we use ISO-11179 rules that give us “<verb>_<object>” names for procedures without compiler prefixes and the few (less than five in your entire career) functions.

We also hate UDFs! They are how an old 1970's procedural programmer would write his FORTRAN or COBOL code. An SQL programer knows that they do not perform well, they are not relational and they are a bitch to maintain.

Then you did the classic “VARCHAR(50)” (from ACCESS programming!) and local variables (from 1950's FORTRAN and Assembly language programming). And WHILE loops in a declarative language! Why do you think “@data” is a clear, precise data element name?

What you did write can be replaced by a simple predicate:

CHECK(something_name LIKE '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z] .. [0-9A-Z])

You do not know how to program in SQL to the point you are danger to your employer and the public. Please think about that.











Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1407390
Posted Tuesday, January 15, 2013 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 148, Visits: 363
lol. CELKO. Easy there. Not only is this not my code but there’s darn near 200,000 lines of it across 30 scripts that will have to run next week. I can’t make changes to the scripts themselves w/o having to send it though QA again. No Can Do. This code is a run once set that after completion will be trashed. It’s part of a process that will move data from one place to another. Actually it is one small step in a much larger process. All of which I will be managing. When I’m done the new db will be created and loaded w/ 4.7 terabytes of data. To be honest I just grabbed the first thing I saw in the first file I clicked on and put it in the ticket. I’ve never actually seen that function before. As I mentioned before I don’t have the time nor testing cycles to rewrite what was written. However I do find it compelling that u would make such assumption and feel it is you place in this world to spew that nonsense on others. As if we are privileged to hear that dribble. Perhaps I should have made myself more clear in my original post and then you wouldn’t have made so many illogical assumptions. I don’t know. What I do know is someone needs a girlfriend or woman rather. Hit me up. I’m buyin because u my friend are treading on a very fine line.

Please think about that.

Post #1407404
Posted Tuesday, January 15, 2013 11:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 1:16 PM
Points: 1,566, Visits: 2,390
CELKO
You do not know how to program in SQL to the point you are danger to your employer and the public. Please think about that.


Joe, you do not know how to read and interpret a question. The OP was not asking how to improve the function, nor was there anything in there remotely asking for your opinion. Do you really think the use of a UDF qualifies someone as a "danger" to the public at large? And if your nostalgia is so easily coerced by simple things like @i, why don't you find a FORTRAN or COBOL forum to annoy, where you can re-live your presently irrelevant glory days?


Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1407409
Posted Tuesday, January 15, 2013 12:45 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 4,188, Visits: 3,226
I agree with GSquared - combine things logically into groups. I've had deployment scripts of several thousand lines that I've run to release things to production before. When something gets really big, I have a master SQL file where I reference the other SQL files to run in order. When deployment time arrives, I simply follow the instructions I've written for myself. If I've practiced properly (run through it in order in test) then there shouldn't be any problems.

Good luck with your big deployment.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1407436
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse