Transact SQL Stored Procedure sub function 1

[DavidPike]

How does one construct a callable function within a stored proc? For instance, if I had a block of code that turned on the lights and there were three places in my proc that I wanted to turn the lights on I would not want to have the same code in three place. This is old programming 101 stuff but I don't know how to do it in a transact SQL proc. I can use GoTo with lables but it seems very clumsy...for instance, it does not handle going back to where you came from in any tidy way I can determine.

 

[SQLDenis]

>>I would not want to have the same code in three place.

create another proc and execute it 3 times with different params

set @val =1
exec proc @val

set @val =2
exec proc @val

set @val =3
exec proc @val


the bigger question is: are you sure you need this? Can't you use a CASE statement?

simple example

UPDATE table
SET Value = CASE flag when 'on' then 1 else 0 end
WHERE .......

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[DavidPike]

Hi Dennis, and thank you. This is my first post...in fact this is my first programming in quite a while.

The reason I did not want to call an outside proc was that the function I wanted to call from several places was a cursor loop and I did not want to cause the overhead of allocating and de-allocating over and over. I am using this instead of an array because I do not know and cannot find in SQL Books how to use a simple array in a proc. I have found a way around this in this instance but I wanted to know for future reference how to have sub functions in a proc.

 

[SQLDenis]

>> I do not know and cannot find in SQL Books how to use a simple array in a proc.

read this:

http://www.sommarskog.se/arrays-in-sql.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

For future refernce you should also know that cursors are a very bad thing for SQL server performance and most of them are unnecessary and can be redone using set-based logic.

"NOTHING is more important in a database than integrity." ESquared

 

[DavidPike]

SQLSister, Can you point me to any reference material on "set-based logic" to replace cursors?

 

[SQLSister]

suggest you look in BOL at the uinsert update and delte statements. They all have versions which use joins to get data sets to run against rather than looping through records. If this isn't enough, I'd need to know more about what you are doing to show you the techniques. But a good portion of the time people are using the values clause on an insert or parameters instead of a join on a delete or update.

"NOTHING is more important in a database than integrity." ESquared