Exec Stored Procedure

Hello

Which is faster :

to write a a big stored procedure with if conditions, or to separate them and call them using exec??

i.e:

if @.id=1
insert into ...
else if @.id=2
update...
--------
or
if @.id=1
exec InsertProcedure
else if @.id=2
exec UpdateProcedure

Please help

Its very difficult to jugde that which is faster inLine Query or Store procedure

Read What One of Microsoft Press books says about Store procedure

"

Stored procedures are executable server-side routines. They give you great power and performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures are allowed to have side effects. That is, they are allowed to change data in tables, and even the schema of objects. Stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects. You can perform input validation in stored procedures, and you can use stored procedures to allow activities only if they make sense as a whole unit, as opposed to allowing users to perform activities directly against objects.

Stored procedures also give you the benefits of encapsulation; if you need to change the implementation of a stored procedure because you developed a more efficient way to achieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure's interface remains the same, the users and the applications are not affected. On the other hand, if you implement your business logic in the client application, the impact of a change can be very painful.

Stored procedures also provide many important performance benefits. By default, a stored procedure will reuse a previously cached execution plan, saving the CPU resources and the time it takes to parse, resolve, and optimize your code. Network traffic is minimized by shortening the code strings that the client submits to Microsoft SQL Server—the client submits only the stored procedure's name and its arguments, as opposed to the full code. Moreover, all the activity is performed at the server, avoiding multiple roundtrips between the client and the server. The stored procedure will pass only the final result to the client through the network"

And About User defined Funciton Or Say inline Query Statement

User-defined functions (UDFs) are routines that perform calculations/computations and return a value—scalar (singular) or table. Microsoft SQL Server 2000 introduced UDFs, which you developed with T-SQL and could incorporate in queries, computed columns, and constraints.

|||

probably the big stored procedure with everything in it will be faster but only by a matter of milliseconds. I would choose which ever option you will find easier to maintain and develop.

|||

Are there any clues or proves ??

Please I need that urgenlty

|||

It depends on what you are doing in the IF Loops. If you are just doing INSERTs one big proc should be fine, as far as performance is concerned. If you split it into multiple smaller procs, you have the ability to call individual proc if you need to reuse the INSERT to one of the tables rather than call the giant proc.

|||

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Thank you

|||

In case of splitting sp , will SQL server builds and stores execution plans for exec called procedures??

Plz Advice

|||

JRICE:

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Please re-read my earlier reply.

|||

Thanks for your reply,

I need an advice if Im concern about performance and I need reusability of my stored procedures, will it differ that much?

thank you in advance

Exec not returning a result

Hi all,
How can I tell if a executed dynamically created sql is returning no rows.
As in:
select @.sql= 'insert into PLImport2 select * from
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
exec (@.sql)
Which whould normally retun and insert 5000 rows. I want to this in a loop,
but am afraid to because as soon as it does not find any rows, I want it to
stop. At the momen it can carry on for ever
Thanks
RobertDoes it make a difference that you are INSERTing into [PLImport2] and you
are SELECTing from [Import1]?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robert Bravery" <me@.u.com> wrote in message
news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> How can I tell if a executed dynamically created sql is returning no rows.
> As in:
> select @.sql= 'insert into PLImport2 select * from
> OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
> 8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
> FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
> exec (@.sql)
> Which whould normally retun and insert 5000 rows. I want to this in a
> loop,
> but am afraid to because as soon as it does not find any rows, I want it
> to
> stop. At the momen it can carry on for ever
> Thanks
> Robert
>|||HI,
Yes, PLImport2 is a SQL Table that I am inserting into, and Import1 is
range in an excel File. I am selecting data from the Excel file into the SQL
table
Thanks
Robert
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eWIQ2VunGHA.4104@.TK2MSFTNGP04.phx.gbl...
> Does it make a difference that you are INSERTing into [PLImport2] and you
> are SELECTing from [Import1]?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
rows.
*
>

EXEC Command in all existing connections

If there any way to execute t-sql command in all connections in one time.
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>

EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql

Exec @string

I am trying to do an insert statement utilizing a variable string.

something like:

Set @.cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @.lServer + ';User ID=' + @.user + ';Password=' + @.pword + char(39) + '
).myServer..

Insert into #Temp_table (field1, field2)
select exec @.cString

--What is the syntax for this?as answered in previous post. you can like so:

insert tb
exec(@.sql)|||When I do something like:

Insert into #tACCOUNT (ACCOUNTID, ACCOUNT, TYPE)
exec (@.cString)

I get:

MSDTC on server 'myServer' is unavailable.|||dtc is needed to ensure data integrity between 2 sites. when you do any dml, the transaction is implicitly promoted to a distributed transaction.

Exclusive Insert Lock on a Table

Hello All!

I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.

So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?

Thanks!

David

You can open an transaction explicitly so other transactions are placed in a queue. Use BEGIN TRAN /COMMIT TRAN. Read up books on line for more info on transactions.

|||

Yup, I could read about this as you suggest, but it's certainly pretty complex so I was hoping someone experienced and knowleadgable would give me the answer.

Your post but it doesn't answer my question. I know how to create an explicit transaction.

My question is how do I lock a table during a transaction so that no one else inserts or deletes records from it.

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

|||

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

|||

Thanks. This tells me how to lock a table by creating a transaction and doing and insert statement. However, in my scenario I need to lock the table innitially before doing any inserts to it and I want it to remain locked until the transaction ends (even if I don't do any inserts). I don't want any other transaction to be able to insert any rows until my transaction finishes.

I think that what I need is to set the transaction isolation level to SERIALIZABLE.

I found this: http://msdn2.microsoft.com/en-us/library/ms173763.aspx

I'll be trying it later and will post results.

David

|||

I do find your query analyzer excercise extremelly useful. I will use it to test my locking 'theories". Thanks!

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

(1) In a query analyzer window run this script

BEGIN TRAN

INSERT INTO TheTable ... <complete the rest of the INSERT>

(2) Open another query analyzer and do a SELECT * From TheTable. You will see that your query will be in "suspended" status waiting for the Insert to finish since you opened a Transaction explicitly.

(3) Now go back to the first window and run this:

Commit

(4) Check the second window and you will see results for your SELECT *...

Basically your BEGIN TRAN has opened a transaction --> locked the table for any other commited transactions (Even reads). As soon as you commit the transaction the lock on the table is released and others can read from the table. You can by pass this and do a dirty read by using NOLOCK hint. Not always suggested unless your business requirements allow you to.

Actually, that doesn't lock the whole table. It locks a portion of the table, but your SELECT requires access to the entire table (including the locked portion), so it has to wait. If you have the table indexed, and the SELECT can use the index to determine that it doesn't need the locked portion then it won't delay the SELECT. In addition, a second INSERT should complete without being delayed.

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

|||

Motley:

To the original poster, what you are asking for is rather uncommon, and you are best to try and avoid doing what you are asking to do. Perhaps you need to rethink why you want the table locked, and what you are trying to accomplish by doing so. Usually there is a much better way of achieving that.

You are totally right. This was actually what I did. I re-thought the process and found a better way that doesn't require the table lock.

But anyways it was a great learning experience.

Thanks all for your support.

David

|||

ndinakar:

neutrino:

I don't think that by just creating an explicit transaction and reading a row from MyTable this will lock the complete table.

Yes it does. And thats all you have to do. Try this scenario:

It actually doesn't but the rest of your post was really helpful. Thank you.

excluding timestamp field in insert

I need to create a lot of simply queries that copy records from one table to
another. The queries are like the one below:
INSERT INTO tblEmp
SELECT tblEmp2003.*
FROM tblEmp2003
WHERE tblEmp2003.EmployeeId='001'
My problem is that all the tables contain a timestamp so the querys fail
because the timestamp is not uptable. Is there an exclusion verb that I can
use to exclude the timestamp field. I know I could simply list all the
fields and not include the timestamp column, but given all the queries I nee
d
to setup that would take forever.
Thanks"MarkT" <MarkT@.discussions.microsoft.com> wrote:

>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
>Thanks
No, there is no way around listing the columns. You might make the
job less difficult if you wrote a query to generate the text of the
INSERT commands though. You might get some ideas from the proc below,
which generates a SELECT for a table.
Roy
CREATE proc dbo.sp__select
(@.tblname varchar(50),
@.alias varchar(50) = NULL)
AS
select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE ' '
END +
CASE WHEN @.alias IS NOT NULL
THEN @.alias + '.'
ELSE ''
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + ' FROM ' + O.name +
CASE WHEN @.alias IS NOT NULL THEN ' as ' + @.alias
ELSE ''
END
END
from sysobjects O, syscolumns C
where O.id = C.id
and O.name = @.tblname
order by C.id, C.colid
GO|||On Mon, 14 Nov 2005 19:31:01 -0800, "MarkT"
<MarkT@.discussions.microsoft.com> wrote:
>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
The query analyzer will generate the insert and select skeletons for
you. It is a pity SQLServer can't be smarter about the *.
J.

exclude names that have numbers

Eg:
Create table test
(col1 char(20))
insert test values ('test')
insert test values ('test1')
insert test values ('te1st')
insert test values ('tes')
Basically I want the output to return those values that do not contain a
number in the entire value..
So the output from above should only include
test
tes
How can I do so ?
ThanksTry
select * from test
where col1 not like '%[0-9]%'
Ben Nevarez, MCDBA, OCP
Database Administrator
"Hassan" wrote:

> Eg:
> Create table test
> (col1 char(20))
> insert test values ('test')
> insert test values ('test1')
> insert test values ('te1st')
> insert test values ('tes')
> Basically I want the output to return those values that do not contain a
> number in the entire value..
> So the output from above should only include
> test
> tes
> How can I do so ?
> Thanks
>
>

EXCEPTION_ACCESS_VIOLATION

I got this error when i try to insert a data in a table
with a Text Column in a distributed application (MSTDC
COM+ ASP), somebody have some one idea about this
error ? more descriptions from event viewer :
Error : 0 Severity : 19 State : 0
SqlDumpExceptionHandler: Process 61 generated fatal
exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.Check your SQL Server error log for the exact stack dump that goes with the
Access Violation (AV) message. Pull out the keywords from the first couple
of lines of the dump and search for them in the online Knowledge Base.
Also, use profiler to find out what the exact command is that results in
the AV and see if the same thing happens when you run that command from
Query Analyzer.
Are you on the latest service pack for SQL Server?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Exception - Insert Record into DB

Hello

I'm using express edition to create my trail testapplication. Below is the code that I have and I'm trying to insert data to thedatabase table named "Coin".

ProtectedSubbtnSave_Click(ByVal senderAsObject,ByVal eAsSystem.EventArgs)Handles btnview.Click

Dim sAsString = txtCname.Text

'Dim myConnection As NewSqlConnection(myConnString)

Dim descAsString = txtCDesc.Text

Dim ConStrAsNew SqlClient.SqlConnection

ConStr.ConnectionString ="server=test\sqlinstance;Integrated Security=True"

Response.Write("Connection string: " & ConStr.ConnectionString)

Try

Dim SelectQueryAsString ="SELECTmax(coinid) from coin"

Dim idvalAsInteger = 0

Dim commandAsNew SqlCommand(SelectQuery, ConStr)

ConStr.Open()

idval = command.ExecuteScalar()

Console.WriteLine(idval)

idval = idval + 1

Dim InsertQueryAsString ="INSERTINTO COIN(coinname, coinid, ebayid, ebaymember, ebaymemid , amount , coindesc)VALUES('1992-Proof'," & idval &",'eewerwer','sp6937','serwryana',67.70,'MattProff of 1992- Mint Set')"

Dim command1AsNew SqlCommand(InsertQuery, ConStr)

command1.ExecuteScalar()

Dim S1AsString ="Recordinsert - Successful!"

Console.WriteLine(S1)

Catch exAsException

Label2.Text = ex.ToString

Finally

ConStr.Close()

EndTry

End Sub

This program isthrowing an exception (mentioned below)

Exception --> System.Data.SqlClient.SqlException:Invalid object name 'coin'. atSystem.Data.SqlClient.SqlConnection.OnError(SqlException exception, BooleanbreakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSetbulkCopyHandler, TdsParserStateObject stateObj) atSystem.Data.SqlClient.SqlDataReader.ConsumeMetaData() atSystem.Data.SqlClient.SqlDataReader.get_MetaData() atSystem.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResultresult) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) atSystem.Data.SqlClient.SqlCommand.ExecuteScalar() at _Default.btnview_Click(Objectsender, EventArgs e) in C:\Documents and Settings\arsha\My Documents\VisualStudio 2005\WebSites\WebSite1\Default.aspx.vb:line 92

"Coin" is the table name – which is in SQL server. Kindlyhelp me to handle and overcome this exception.

Thanks

perhaps you have set up your database with a case sensitive collation?

Try altering your sql statement so it's case exactly matches your table.

also, i would strongly recommend that you let sql server take care of incrementing the CoinId data by using an identity column. By trying to do it yourself, if 2 people run your page simultaneously, you could end up with a problem.

|||

Try using ExecuteNonQuery instead ofExecuteScalar

Excel to SQl

Hi All

Thnks for the time

I was trying to figure out how to insert the data that I read from an excel sheet into multiple tables in SQL Database 2005.

// Connection string to the excel file

string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source= D:\\UserData.xls;" +

"Extended Properties=Excel 8.0;";

//Command to read data is

SELECT ID,ProductName,ProductDesc,CategoryName FROM [Products$]

//Connection string for sql 2005 database

string sqlConnectionString = "DataSource=MyServer\\MSSQLEXPRESS;Initial Catalog=TestExcel;Integrated Security=True";

And what is your question ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de