Re: not sure of advantages of functions over stored procedures - Database Discussion Boards

Eric Dahlvang23-Jun-06 6:26

23-Jun-06 6:26

ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(25)

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Alter column?

Kschuler23-Jun-06 8:57

Kschuler

23-Jun-06 8:57

And here I thought it would be complicated. Smile | :)

Thanks for your help!

not sure of advantages of functions over stored procedures

sishya23-Jun-06 2:33

sishya

23-Jun-06 2:33

Hi,
I am asked to convert stored procedures to functions .Can anyone tell me the advantages of functions over stored procedures and vice versa and i am not shoor if i can use system ftions like getdate() in functions
pls help me out
thnks in advance.

Re: not sure of advantages of functions over stored procedures

J4amieC23-Jun-06 3:11

J4amieC

23-Jun-06 3:11

Functions are useful when you only need to return a single value from a procedure, rather than a set of data.

You can use all built in functions as you would in a SP, the only limitation AFAIK is that you cannot insert/update from a function.

Current blacklist
svmilky - Extremely rude | FeRtoll - Rude personal emails | ironstrike1 - Rude & Obnoxious behaviour

Re: not sure of advantages of functions over stored procedures

Colin Angus Mackay23-Jun-06 3:42

Colin Angus Mackay

23-Jun-06 3:42

J4amieC wrote:
You can use all built in functions as you would in a SP

You can't use functions such as GETDATE() because they are non-deterministic.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: not sure of advantages of functions over stored procedures

J4amieC26-Jun-06 2:49

J4amieC

26-Jun-06 2:49

Live and learn, of the countless times ive used UDF's ive never come across this!

Current blacklist
svmilky - Extremely rude | FeRtoll - Rude personal emails | ironstrike1 - Rude & Obnoxious behaviour

Re: not sure of advantages of functions over stored procedures

Colin Angus Mackay26-Jun-06 8:25

Colin Angus Mackay

26-Jun-06 8:25

J4amieC wrote:
Live and learn

Absolutely - the day we stop learning is the day we die.

Re: not sure of advantages of functions over stored procedures

Colin Angus Mackay23-Jun-06 3:48

Colin Angus Mackay

23-Jun-06 3:48

sishya wrote:
Can anyone tell me the advantages of functions over stored procedures and vice versa and i am not shoor if i can use system ftions like getdate() in functions
pls help me out

You cannot use functions such as GETDATE() because they are non-deterministic. A function must have a deterministic operation. i.e. Given the same inputs it will always produce the same output.

I wouldn't go around and convert stored procedures to functions just because.

I wouldn't say functions or stored procedures have any avantages over each other. They are designed for different uses.

A function can return data to calling code more easily that a stored procedure can. A stored procedure can perform data modification actions (INSERT/UPDATE/DELETE) that a function can't.

checking for existence of server and SQL instance

Talal Sultan23-Jun-06 0:27

Talal Sultan

23-Jun-06 0:27

I'm writing a small C# windows application that will create a new database and populate it with tables, views and stored procedures. Of course I am using SQL scripts that I'm launching from the windows application. I ask the user to enter 2 fields, the server name and the instance name of the SQL server.

My question is: is there a way to check if the entered server name and instance exist?

Since I launch the scripts by starting a process to run osql, I don't know if I get some return value to tell me that the server was not found. So basically even if the server name is wrong, the application says that the operation is completed, although nothing has been done.

Thanks,
Talal

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Re: checking for existence of server and SQL instance

Colin Angus Mackay23-Jun-06 1:08

Colin Angus Mackay

23-Jun-06 1:08

Talal Sultan wrote:
My question is: is there a way to check if the entered server name and instance exist?

You could always create a SqlConnection and attempt to Open the master database on the connection. If it fails you get an exception (although it may also fail for insufficient rights, but if you're creating a new database you're going to need them anyway). If it works you can Close the connection in the knowledge that the server exists.

Re: checking for existence of server and SQL instance

Talal Sultan23-Jun-06 3:15

Talal Sultan

23-Jun-06 3:15

Thanks, I'll try that out Smile | :)

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Using Centralized Database MS SQL Server2000

Varun Arora22-Jun-06 23:20

Varun Arora

22-Jun-06 23:20

Hi All,

I have setup of my vb application running on a computer with backend sqlserver2000 on same computer. I want that I can run my application on many computers which are networked with the same central database. Can any one please suggest me what parameters should I have to keep in my mind or simply how can I achieve this?

I will be very thankful.

Varun Arora,
Software Engineer,
Knowledge Calls,
Gurgaon, India

Re: Using Centralized Database MS SQL Server2000

Colin Angus Mackay23-Jun-06 1:04

Colin Angus Mackay

23-Jun-06 1:04

Varun_soft11 wrote:
Can any one please suggest me what parameters should I have to keep in my mind or simply how can I achieve this?

Change your connection string to point to the central database rather than the local one. There isn't much else to it.

data from database

rcshek22-Jun-06 15:12

rcshek

22-Jun-06 15:12

It might be a silly question
I want to retrieve the data when some action is performed
Actually,when the page is loaded default data loads in to the data grid
when the combo box Item is selected then I want to retreive the data related to that particular condition
Do i need to create one more data adapter and data set
If not How can i do that
can any one of u guys tell me

Thanks

Re: data from database

VK-Cadec23-Jun-06 8:25

VK-Cadec

23-Jun-06 8:25

Store all the information in one dataset. When the combo box is created you can select out of this existing dataset based in conditions given - maybe selecting only one type of products codes based on selected value.

query

Tara1422-Jun-06 10:57

Tara14

22-Jun-06 10:57

Hi,
I have a table like this:

Param    Cat    Pay
----    ----    ---------
bugj    home    rent
bugj    home    repair
budj    home    something1
budj    car     something2
budj    car     somethg3
budj    tax     somethg4

My progam has to search the field catand list all the categories. i.e home,car,tax

Plese can you tell me if there is any particular sql which eliminates repititions?

Thanks

Fortitudine Vinsinues!

Re: query

Eric Dahlvang22-Jun-06 11:35

Eric Dahlvang

22-Jun-06 11:35

SELECT DISTINCT Cat FROM TableName

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: query

Tara1422-Jun-06 19:11

Tara14

22-Jun-06 19:11

Thank you.

Fortitudine Vinsinues!

Stored procedure

Nayota22-Jun-06 8:29

Nayota

22-Jun-06 8:29

Hello All,

I have a table that has 4 coloums of which one is primary key and is set to auto increment.
Is it possible to write a stored procedure that inserts value in the rest of the three coloumns and returns the value of the primary key that was generated automatically.

Re: Stored procedure

Dustin Metzgar22-Jun-06 9:23

Dustin Metzgar

22-Jun-06 9:23

I'm not a database expert, but nobody else answered your question so I'll give it a shot. It sounds like you're using Oracle or something because you said auto increment instead of index. But, in SQL Server, you can write an insert statement in your stored procedure like this:

insert into [table] 
(
	column1,
	column2,
	column3
)
values
( 
	@column1,
	@column2,
	@column3,
)
return SCOPE_IDENTITY()

That will return the value in the identity column.

In Oracle, you do it two ways. The first way is to have a sequence and get the next value of the sequence first using:

SELECT table_seq.NEXTVAL AS ID from DUAL

Then when you do your insert, use that ID in your insert.

The other way is to have a sequence and a trigger. Do your insert and then select the max value for that column out of the table and return it. The first way is probably better.

Any of you db gurus out there, please correct me if I'm wrong.

Logifusion[^]

Re: Stored procedure

Nayota22-Jun-06 10:22

Nayota

22-Jun-06 10:22

Thanks Dustin, that was fast. I am actually using SQL Server 2000 and I am sorry I forgot to mention.

Re: Stored procedure

jonathan1522-Jun-06 23:08

jonathan15

22-Jun-06 23:08