|
Hi,
I'd like to put all my database management in StoredProcedures or Functions, but I'm having a small problem:
1) I know I can't use INSERT, UPDATE or DELETE in UDFs, right?
2) So i created a SPROC that inserts a record to a simple table (id, name) and returns the ID in an output parameter. It's working fine.
3) I create a UDF that executes this SPROC and returns the ID... and here is my problem... looks like I can't execute my own SPROCs from a UDF. 
4) So.. how do I achieve this goal? Inserting a record and returning the auto generated id back to my web browser (asp.net application).
Here is some of my code:
CREATE PROCEDURE finsTeste
@snome varchar(50),
@nid int output
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO teste (nome) VALUES (@snome)
SELECT @nid = SCOPE_IDENTITY()
END
GO
CREATE FUNCTION insTeste
(
@snome varchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @nid int
EXECUTE finsTeste @snome, @nid output
RETURN @nid;
END
GO
And then in the asp.net page I run a ExecuteScalar(StoredProcedure, "insTeste", params);
Thanks,
Dirso
|
|
|
|
|
Why do you need to create a UDF? You can get the value that you need directly from the SP.
|
|
|
|
|
I didn't know that... I thought I had to execute. Thank you very much! I just tried and it worked great!
Thanks,
Dirso
|
|
|
|
|
hi
how to create foreign key in sql server 2005 using table designer
thanks
|
|
|
|
|
I'm assuming that you're using "Microsoft SQL Server Management Studio";
* Rightclick on the table, choose "design"
* Rightclick on any column in the newly opened window, choose "Relationships"
* Click "Add"
I are Troll
|
|
|
|
|
Hello everyone!
I wonder is it possible to create SQL querie using FULL JOIN in Access 2003? When i try it i just get syntax error. So does anyone have an idea how to simulate full join in access?
Cheers!
|
|
|
|
|
Branislav Vidovic wrote: When i try it i just get syntax error
Which error message do you get? Post your code to see where is wrong.
For more about FULL JOIN.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
There is no reason to simulate the Outer Join.
Please post your query along with the error you receive.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Possibly the union of a left join and a right join?
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Yes I actually tried that and made it. IT is actually union of left join, right join and inner join 
|
|
|
|
|
Branislav Vidovic wrote: and inner join
Of course - stupid of me to forget it.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
|
Hello,
I have two data tales in Access. One contais 2 million recors and the other contains 116000 records. The Customer name, street address, city and zip code fields need to be matched against each other across both tables in order to fin mismatches and exact matches.
Since one of the tales is very large, I am having a problem with the database program hanging every time I run the query. I tried doing this in SQL server 2005 as well, with no success.
Can anyone suggest a method for accomplishing this?
Regards.
|
|
|
|
|
Please post your query so that we might be able to understand why it refuses to dance.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
You can create a non-clustered index for every condition and If you optimize your query that also will help you. Please post your query.
Education is not a way to escape poverty — it is a way of fighting it.
|
|
|
|
|
2 million records is not large in SQL server (although it may be in Access) so it has got to be your query. As suggested by others add some indexes and try optimising your query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
how i can see if the server is working or not ?
in sql server 2000 there was tool that show it
if there Isn't any tool, how i can open and close the server ? (script ?...)
thank's in advance
|
|
|
|
|
E_Gold wrote: how i can see if the server is working or not ?
Just try to open a connection
E_Gold wrote: how i can open and close the server ?
Open the Control Panel, go to the Administrative Tools and open the "Services" applet. From there you can start and stop the service.
Wha?
|
|
|
|
|
is there any script that can open/close the server ?
thank's
|
|
|
|
|
No SQL script; if the server is closed, how would you execute that SQL-script?
You can use VBScript[^]
I are Troll
|
|
|
|
|
|
net start [Service] and
net stop [Service]
|
|
|
|
|
I learned here, we have to issue " netstat -an " to identify the port-1433 is configured or not..
I also checked and got the following screen,
Proto Local Address Foreign Address State
....
TCP 0.0.0.0:1433 0.0.0.0 Listening
TCP MY SQL SERVER INSTALLED
MACHINE IP : 139 0.0.0.0 Listening
...
and then for UDP.
And Iam not getting like the below,
TCP MY SQL SERVER INSTALLED
MACHINE IP : 1433 0.0.0.0 Listening
So what I have to do? From where I have to troubleshoot? Also getting confusion, my domain only have static ip, this Sql server installed machine ip is not a static ip.
Any ideas please? 
Thanks
|
|
|
|
|
hi
I want to learn sql joines can anyone provide me link for the best article that would help me.
Thanks
|
|
|
|
|
Here is one of the Best Join article link from Code Project, This is totally visual representation of Join in SQL.
Visual Representation of SQL Joins[^]
This will clear your doubts on joins
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
