Re: Help with Cursors in Stored Procedures [long reply] - Database Discussion Boards

Re: SQL Performance Question

jgallen236-Oct-05 9:17

jgallen23

6-Oct-05 9:17

yeah, that does help. Thanks!

Executing SQL Script File

KragAapie5-Oct-05 19:32

KragAapie

5-Oct-05 19:32

Hey,
I have sql script files that when loaded into sql query analizer work as expected.

How do I load and execute the entire contents of the file from with in a C# windows
app.

I tried

m_cConn = new SqlConnection();
m_cConn.ConnectionString = sConnStr;
m_cConn.Open();
m_cCmd = new SqlCommand(sSQL);
m_cCmd.Connection = m_cConn;
m_cCmd.ExecuteNonQuery();
m_cConn.Close();

etc..

The sSQL is a string with the contents of the sql file loaded into it.
The file contains DB create / drop, table create / drop, procedure create / drop ,
use, update and insert statements.

Thanks in advance.

For Example:

--create the database
use master
declare @DBName varchar(50)
set @DBName = '[NetPortalDB]'
if exists(select * from sysdatabases where name=@DBName)
drop database [NetPortalDB]

create database [NetPortalDB]
GO

use [NetPortalDB]

-- Adding downtime login
if not exists (select * from master.dbo.syslogins where loginname = N'downtime')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'downtime', N'downtime', @logindb, @loginlang
END
GO

-- Adding processmore login
if not exists (select * from master.dbo.syslogins where loginname = N'processmore')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'processmore', null, @logindb, @loginlang
END
GO

if not exists (select * from dbo.sysusers where name = N'downtime' and uid < 16382)
EXEC sp_grantdbaccess N'downtime', N'downtime'
GO

if not exists (select * from dbo.sysusers where name = N'processmore' and uid < 16382)
EXEC sp_grantdbaccess N'ProcessMORe', N'processmore'
GO

then goes on to create tables and populate them

Re: Executing SQL Script File

Colin Angus Mackay5-Oct-05 21:59

Colin Angus Mackay

5-Oct-05 21:59

The GO is not part of SQL. It is used within Query Analyser to delimit batches.

I put together a little application that will allow you to process files with GO delimiters in them within .NET. You can find it on my blog[^].

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

-- modified at 4:03 Thursday 6th October, 2005

How to call stored procedure from update statement

iluha5-Oct-05 15:54

iluha

5-Oct-05 15:54

Hello,

I have a stored procedure called P_DATE_TO_MS that takes 2 parameters DATE & MILLISECOND and I need to call this procedure from an update statement:

conConnection.Execute ("UPDATE table SET CURRENT_TIME_IN_MS = P_DATE_TO_MS(getDate()) WHERE id=123")

It looks like the stored procedure returns the information in the second variable (ByRef?) and not as a return value. I'm calling the statement through ADODB.Connection object
and not in the SQL directly.

Please correct me if my assumption is incorrect, I'm not an SQL guy and I'm just getting into SQL

Can anyone tell me if this is possible or how to do it.

here is the store procedure:
CREATE PROCEDURE P_DATE_TO_MS

@DATE DATETIME,
@MILLISECOND DECIMAL OUTPUT

SET @MILLISECOND = TIME_IN_MS
END

GO

Thank you in advance
ilya

Re: How to call stored procedure from update statement (edit)

Nic Rowan6-Oct-05 1:55

Nic Rowan

6-Oct-05 1:55

Hi,

I think a user defined function would work better for what you are trying to do (a SCALAR FUNCTION in this case)

CREATE FUNCTION dbo.udfGetSeconds (@Days INT)
	RETURNS INT

AS

BEGIN
	DECLARE @Seconds INT
	
	SET @Seconds = @Days * 24 * 60 * 60
	
	RETURN @Seconds
END

Then use it as follows

SELECT dbo.udfGetSeconds(5)

(edit)

or in an UPDATE statement

UPDATE [Table] SET [column] = dbo.udfGetSeconds(5)

(edit)

The man who smiles when things go wrong has thought of someone he can blame it on.

If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.

-- modified at 8:03 Thursday 6th October, 2005

Import/Export in MS Access

suzie1005-Oct-05 8:38

suzie100

5-Oct-05 8:38

I need to write a script so that I will be able to import and export data from a MS Access database.

What I really need to do is export the exisiting data from the table, re create the table with a few added columns and export the data back.

Any help and suggestions would be greatyl appreciated.

Thanks,
Susmita

Help with Cursors in Stored Procedures

jszpila5-Oct-05 6:33

jszpila

5-Oct-05 6:33

Hello everyone,
I've just written my first stored procedure and maybe gotten in a little over my head. It's not exactly working out the way I thought; essentially, what I want to do is select a few fields from one table set of rows that meets the SELECT criteria and also where the identifying number is not already in the second table, and then perform a few operations on some of the fields and subsequently insert everything into the second table. I've been told a cursor would assist in this, but I have yet to find a good and clear tutorial/example on T-SQL cursors. Just so you can get a better idea of what I'm talking about, here is my stored procedure thus far:

<br />
CREATE PROCEDURE sp_UpdateLeaders<br />
AS<br />
DECLARE<br />
-- prep all variables<br />
@YspCalc money, <br />
@FeeCalc money, <br />
@Fees money, <br />
@Amount money, <br />
@Ysp money,<br />
@Fee1 money, <br />
@Fee2 money, <br />
@Fee3 money, <br />
@Fee4 money, <br />
@Fee5 money, <br />
@Fee6 money,<br />
@Number varchar(50), <br />
@McName varchar(50), <br />
@Client varchar(50), <br />
@Officer varchar(50),<br />
@FName varchar(25), <br />
@LName varchar(25),<br />
@Date datetime,<br />
@Points integer, <br />
@LoanType integer, <br />
@Mortgage integer<br />
<br />
-- get info from bors<br />
SELECT <br />
TLH_1_68=@FName, TLH_1_69 = @LName, TLH_1_19 = @LoanType, <br />
TLH_1_748 = @Date, TLH_1_2 = @Amount, TLH_2_58 = @McName, <br />
TLH_1_420 = @Mortgage, TLH_1_869 = @Ysp, TLH_1_2 = @Amount,  <br />
GRICA_1_2 = @Fee1, GRICA_1_4 = @Fee2, GRICA_1_6 = @Fee3,  <br />
GRICA_1_10 = @Fee4, GRICA_1_12 = @Fee5, GRICA_1_8 = @Fee6,<br />
TLH_1_317 = @Officer, TLH_1_876 = @Number<br />
FROM MakeDaily53.DBO.bors<br />
WHERE TLH_1_749 Is NULL <br />
AND Filename NOT LIKE '%.2'<br />
-- only for the following branch codes<br />
AND TLH_1_1007 IN ('645','644','642','638','636','599','598','592','589','587','586','582','581','580','579','565','112')<br />
<br />
Print('Before')<br />
<br />
-- check for existing loan in leaderboard<br />
IF NOT EXISTS(SELECT * FROM LeaderBoard WHERE LoanNumber = @Number)<br />
BEGIN<br />
<br />
Print(@Number)<br />
<br />
-- check for nulls<br />
IF @Fee1 = Null<br />
SET @Fee1 = 0<br />
IF @Fee2 = Null<br />
SET @Fee2 = 0<br />
IF @Fee3 = Null<br />
SET @Fee3 = 0<br />
IF @Fee4 = Null<br />
SET @Fee4 = 0<br />
IF @Fee5 = Null<br />
SET @Fee5 = 0<br />
IF @Fee6 = Null<br />
SET @Fee6 = 0<br />
<br />
-- concatenate client name<br />
SET @Client = @FName + ' ' + @LName<br />
-- ysp+rsp caluclation<br />
SET @YspCalc = (((@Ysp - 100)/100)*@Amount)<br />
-- additional fees are calulated differently for 1st and 2nd mortgages<br />
IF (@Mortgage = 1)<br />
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-1425)<br />
IF (@Mortgage = 2)<br />
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-275)<br />
<br />
-- insert into leaderboard<br />
INSERT INTO LeaderBoard (ClientName, LoanType, LoanNumber, ClosedDate, LoanAmount, YspSrp, Points, AddFees, McName, MortgageType, LoanOfficer)<br />
VALUES (@Client, @LoanType, @Number, @Date, @Amount, @YspCalc, @Points, @FeeCalc, @McName, @Mortgage, @Officer)<br />
<br />
END<br />
<br />
-- go!<br />
GO

Any help is greatly appreciated. Thanks in advance!

-------------------
abort, retry, fail?

Re: Help with Cursors in Stored Procedures [long reply]

Colin Angus Mackay5-Oct-05 11:12

Colin Angus Mackay

5-Oct-05 11:12

I recon that you could do all of this in one INSERT statement - it would be a gargantuan INSERT statement, but after spending the last few minutes reading the code I think it could be done. That would be the most efficient option - however trying to explain how to go from what you have here - a very procedural step-by-step approach to a more database oriented set-based approach would take a lot of time. So, I'll just show you how to add a cursor to the existing code. But, please note that cursors should only be used as a last resort - or where such a small amount of data is being processed that the performance is not an issue. Cursors are notoriously slow because database systems are optimised for set-based operations (i.e. They like to operate on many rows of data all at once, rather than stepping through one row at at time)

See the comments in you're modified source code to see what is going on.

CREATE PROCEDURE sp_UpdateLeaders
AS
DECLARE
-- prep all variables
@YspCalc money, 
@FeeCalc money, 
@Fees money, 
@Amount money, 
@Ysp money,
@Fee1 money, 
@Fee2 money, 
@Fee3 money, 
@Fee4 money, 
@Fee5 money, 
@Fee6 money,
@Number varchar(50), 
@McName varchar(50), 
@Client varchar(50), 
@Officer varchar(50),
@FName varchar(25), 
@LName varchar(25),
@Date datetime,
@Points integer, 
@LoanType integer, 
@Mortgage integer

-- Declare the cursor. This sets up the select statement that contains 
-- the rows of data we want to iterate over. (Note: I've taken out your
-- assignments to the local variables - you'll see later why)
DECLARE bors_cursor CURSOR FOR
SELECT 
TLH_1_68, TLH_1_69, TLH_1_19, 
TLH_1_748, TLH_1_2, TLH_2_58, 
TLH_1_420, TLH_1_869, TLH_1_2,
GRICA_1_2, GRICA_1_4, GRICA_1_6, 
GRICA_1_10, GRICA_1_12, GRICA_1_8,
TLH_1_317, TLH_1_876
FROM MakeDaily53.DBO.bors
WHERE TLH_1_749 Is NULL 
AND Filename NOT LIKE '%.2'
AND TLH_1_1007 IN ('645','644','642','638','636','599','598','592',
    '589','587','586','582','581','580','579','565','112')
-- Now open the cursor
OPEN bors_cursor

-- Collect the first row of data into the variables that will be used
-- Note: You had two different columns being assigned to @Amount, I guess one
-- of these is incorrect.
FETCH NEXT FROM bors_cursor 
INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
     @Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
     @Fee5, @Fee6, @Officer, @Number

-- @@FETCH_STATUS tells us if the previous fetch operation went okay
-- Note: This is regardless of the number of cursors you have. It always
-- corresponds to the most recent FETCH command that was executed so make
-- sure you check it after each fetch operation. A status of 0 means
-- it went okay. So here, while we keep getting rows then go into the loop.
WHILE @@FETCH_STATUS = 0
BEGIN


-- check for existing loan in leaderboard
IF NOT EXISTS(SELECT * FROM LeaderBoard WHERE LoanNumber = @Number)
BEGIN

-- You'll recognise this code - I copied it unchanged from your post.
Print(@Number)

-- check for nulls
IF @Fee1 = Null
SET @Fee1 = 0
IF @Fee2 = Null
SET @Fee2 = 0
IF @Fee3 = Null
SET @Fee3 = 0
IF @Fee4 = Null
SET @Fee4 = 0
IF @Fee5 = Null
SET @Fee5 = 0
IF @Fee6 = Null
SET @Fee6 = 0

-- concatenate client name
SET @Client = @FName + ' ' + @LName
-- ysp+rsp caluclation
SET @YspCalc = (((@Ysp - 100)/100)*@Amount)
-- additional fees are calulated differently for 1st and 2nd mortgages
IF (@Mortgage = 1)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-1425)
IF (@Mortgage = 2)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-275)

-- insert into leaderboard
INSERT INTO LeaderBoard (ClientName, LoanType, LoanNumber, ClosedDate, LoanAmount, YspSrp, Points, AddFees, McName, MortgageType, LoanOfficer)
VALUES (@Client, @LoanType, @Number, @Date, @Amount, @YspCalc, @Points, @FeeCalc, @McName, @Mortgage, @Officer)

END



    -- Note: This fetch statement is exactly like the one just before the
    -- loop starts.
    FETCH NEXT FROM bors_cursor
    INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
        @Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
        @Fee5, @Fee6, @Officer, @Number
END

-- Clean up. Cursors are terribly messy
CLOSE bors_cursor
DEALLOCATE bors_cursor

-- We're done!

-- go!
GO

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: Help with Cursors in Stored Procedures [long reply]

jszpila5-Oct-05 11:54

jszpila

5-Oct-05 11:54

Thanks! After ironing out a few type mismatches, that worked like a dream!
I was a hesitant to use a cursor because I've heard bad things about them, but this technique was ordained by those higher up the ladder than myself, so I figured now is as good of a time as any to learn.

Thanks again!

-------------------
abort, retry, fail?

Re: Help with Cursors in Stored Procedures [long reply]

Colin Angus Mackay5-Oct-05 12:16

Colin Angus Mackay

5-Oct-05 12:16

jszpila wrote:
I was a hesitant to use a cursor because I've heard bad things about them

Yes, I would say that cursors are useful in a small number of situations. It is something that once learned is a difficult habit to get out of because people are used to procedural approaches that are used in more traditional languages such as C/C++/C#/VB/Pascal/Java and so on. SQL is a set-based language and so it operates on sets of data more efficiently and if you can keep it that way then all the better.

Now that you know how to use cursors, my advice is to put that knowledge away and not touch it unless you really must use it as there is no other option. Just about every resource I've read on the subject has said how awful and inefficient they are - and I don't mean gaining a few milliseconds here or there. I mean stored procedures going from taking hours with cursors to a few seconds to do the same thing without.

Like I said, I think your problem can be solved without using cursors - but it would probably take a little bit longer to think through. However, the benefits at the other end are so much better.

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Looking for the right forum

Michael Hulthin4-Oct-05 21:42

Michael Hulthin

4-Oct-05 21:42

I'm searching for a good forum to ask questions, exchange ideas etc regarding the creation on data providers. Not using them, making/creating/developing them..

I'm responsible for creating the API-layers for my employers IMDBMS, so far we have written a JDBC-driver, OLEDB-driver and a .NET Data Provider for .NET 1.1.

Problem is that our .net driver that is working using VS2003 and .net 1.1, but will not work in VS2005 .net 2.0. Thanks Bill, appriciate it..not! Wink | ;)

So I'm desperatly looking for a nice place/forum to ask questions, getting ideas etc concerning this.

I've tried the ".NET Framework Data Access and Storage" forum at forums.microsoft.com/msdn, but they seem to think I'm trying to use the SQL Servers data provider, not my own. Wink | ;)

Has anyone seen such a place?

TIA/
Michael Hulthin

How to launch access database and view reports

dw19284-Oct-05 20:36

dw1928

4-Oct-05 20:36

Hi I have reports created in ms Access database. Do you know
how to use code in VB.NET so that I can launch the access database and then open up the reports?
Could you give me some sample codes please? thanks

dw1928

add stored procedures .sql to database

theStorminMormon4-Oct-05 10:32

theStorminMormon

4-Oct-05 10:32

I have what I hope is a simple question. I'm trying a product called RapTier from Sharp Power that generates a data access layer. One of the options it has is to produce stored procedures from my database. I've looked through the generated stored procedures, and they seem pretty good.

My problem is this, I have the stored procedures as a .sql file, and I have to figure out how to actually attach/add them to the database that I have (I'm using MSDE 2000).

Can anyone give me a hand on how to do this?

Thanks.

The ends can never justify the means. It is the means that determine the ends.

Re: add stored procedures .sql to database

Colin Angus Mackay4-Oct-05 12:44

Colin Angus Mackay

4-Oct-05 12:44

You can actually hurl any SQL (including a code-generated script) through a regular SqlCommand object.

A few months ago I wrote a simple application to demonstrate that - It picks up a file and dumps the contents to a SQL Server via the SqlCommand object in .NET

You can find the download here[^]

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: add stored procedures .sql to database

theStorminMormon5-Oct-05 2:57

theStorminMormon

5-Oct-05 2:57

OK, I don't really understand your response, to tell the truth. I'll go to your site and take a look at the utility, maybe it can help.

What I'm really looking for (I think) is just the command I need to feed OSQL to get it to go find my .sql file and add it to the database. I'm not sure if that is equivalent to "hurl[ing] any SQL through a regular SqlCommand object" or not.

Thanks for the reply though!

The ends can never justify the means. It is the means that determine the ends.

Re: add stored procedures .sql to database

Scott Serl6-Oct-05 11:14

Scott Serl

6-Oct-05 11:14

I think what he meant was "why don't you just go read the .sql file into the commandtext property of a sql command object and executenonquery". The only thing you need to be careful of is "go", but I also read in another post that colin wrote a utility to help with sql containing "go" in it.

Re: add stored procedures .sql to database

Luis Alonso Ramos6-Oct-05 18:45

Luis Alonso Ramos

6-Oct-05 18:45

If it's a local instance of MSDE, use the following command:

osql -E -i filename.sql

The -E switch indicates to use Windows authentication, and the -i filename.sql indicates which file contains the statements to run. Run osql /? to see other options.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

Using SqlConnection without Time out

thainam3-Oct-05 23:13

thainam

3-Oct-05 23:13

I use ADO.NET to execute sql. But it takes a lot of time, so after 30 seconds program show error message "Timeout expired!" (althrough I declare "Connect Timeout=600" in connect string). How to use SqlConnection without Timeout or with a long time?
Please help me!!!
Thanks!

Re: Using SqlConnection without Time out

Colin Angus Mackay4-Oct-05 1:19

Colin Angus Mackay

4-Oct-05 1:19

There are two timeouts. The one you are using is for the connection. In other words, how long should the .NET application wait to connect (not do any work, just connect) to the database.

The other timeout it on the command object, CommandTimeout. This timeout is the amount of time that the .NET application will wait for it to perform what ever command you sent it.

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

SQL SERVER

Tran Ngoc Minh3-Oct-05 18:15

Tran Ngoc Minh

3-Oct-05 18:15

Hi,
I want to learn SQL Server and use it in C# Application.
Who know about it,please help me install it.
Thanks.

Re: SQL SERVER

Colin Angus Mackay4-Oct-05 1:25

Colin Angus Mackay

4-Oct-05 1:25

Tran Ngoc Minh wrote:
Who know about it

Lots of people do.

Tran Ngoc Minh wrote:
please help me install it

What problems are you having while installing SQL Server?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Store procedure access in c++

Anonymous3-Oct-05 17:14

Anonymous

3-Oct-05 17:14

Hi,
I want to access sql store procedure.It has 3 input parameter and 8 output parameters. store procedure also retun the result set. Can you please give me reference of sample code to access store procedure. I am totally new to DB.

Thanks

Multiple Selects in SP

Russell Jones3-Oct-05 6:05

Russell Jones

3-Oct-05 6:05

Hi
I've got a login Proc that returns some data upon successful login.

I've just added some checking code before this data to verify that the user may log in etc, but the resultsets from these checks are being returned in the dataset. Is there some way to clear the results before doing my final select statement?

Thanks

Russell

Re: Multiple Selects in SP

afronaut3-Oct-05 6:45

afronaut

3-Oct-05 6:45

Not too sure if this will help but when I have a structure like that I use a reader. I can then write the following:

if(r.Read()){
// they are valid, get user info
if(r.NextResultSet()){
while(r.Read()){
// return additional data for that person
}
}
}
r.close();

*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";

-- modified at 12:45 Monday 3rd October, 2005

Re: Multiple Selects in SP

Anonymous3-Oct-05 6:54

Anonymous

3-Oct-05 6:54

I'm trying to get SQL to not return the recordsets that the SP uses internally and just return the data i really want.

There's quite a bit of branching in the checking code which means i don't know exactly how many select statements will be executed prior to the select that i want executed.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.