ELSEIF in Stored Procedures?

I have found some documentation regarding the use of IF...ELSE statements in stored procedures, but what about multiple condition statements? For example, say I need 3 unique fields in my table. If my application passes a value that is a duplicate in one of the columns, the stored proc will fail, but it is difficult to know which item caused the failure and therefore difficult for the user to get a meaningful error message in order to correct their input. I am thinking I could just make a conditional statement that applies a code to an OUTPUT parameter in order to clarify the error:

(pseudo code)

if @.field1 already exists then @.output = '1';terminate stored procedure

elseif @.field2 already exists then @.output = '2';terminate stored procedure

elseif @.field3 already exists then @.output = '3';terminate stored procedure

else finish the insert

(end pseudo code)

Are 'elseif' statements allowed in SQL Server? Am I going about this in the wrong way?

Jungalist wrote:

Are 'elseif' statements allowed in SQL Server?

Sort of. You can impletemt the logic as follows :

IF @.field1 already exists

BEGIN

SET @.OUTPUT = 1

END

ELSE

IF @.field2 already exists

BEGIN

SET @.OUTPUT=2

END

ELSE

IF @.field3 already exists

BEGIN

SET @.OUTPUT=3

END

check out books on line for "IF ELSE"

|||

Thank-you. I was searching for the wrong terms. I appreciate the help.

Eliminating Dynamic SQL

I am refactoring stored procedures that use dynamic sql.
The reason the store procedures use dynamic sql is because
the data that is need comes from another MS SQL database
that resides on the same server instance.
The following is a code example from a stored procedure:
declare @.theDatabase sysname;
set @.theDatabase = 'SomeDatabase';
declare @.thePrimaryKey int;
set @.thePrimaryKey = 2;
declare @.theSqlString varchar(8000);
set @.theSqlString = 'select r.Feild from ' +
@.theDatabase +
'..TableName r ' +
'where r.ID = '
+ rtrim( str( @.thePrimaryKey ) )
exec sp_executesql @.theSqlString;
The problem is that dynamic sql is EXTREMELY slow! I have
been doing some research and have found a little bit on
sp_linkedservers and OPENQUERY but I have not figured out
how to accomplish what is done above.
Can anyone give me an example of how I can use
sp_linkedservers and OPENQUERY to accomplish this query?
Would it be better, performance wise, to just continue
using dynamic sql?
Any help would be greatly appreciated.
Sincerely,
John Dickey
jpd0861@.msn.comJohn Dickey wrote:
> I am refactoring stored procedures that use dynamic sql.
> The reason the store procedures use dynamic sql is because
> the data that is need comes from another MS SQL database
> that resides on the same server instance.
> The following is a code example from a stored procedure:
> declare @.theDatabase sysname;
> set @.theDatabase = 'SomeDatabase';
> declare @.thePrimaryKey int;
> set @.thePrimaryKey = 2;
> declare @.theSqlString varchar(8000);
> set @.theSqlString = 'select r.Feild from ' +
> @.theDatabase +
> '..TableName r ' +
> 'where r.ID = '
> + rtrim( str( @.thePrimaryKey ) )
> exec sp_executesql @.theSqlString;
> The problem is that dynamic sql is EXTREMELY slow! I have
> been doing some research and have found a little bit on
> sp_linkedservers and OPENQUERY but I have not figured out
> how to accomplish what is done above.
> Can anyone give me an example of how I can use
> sp_linkedservers and OPENQUERY to accomplish this query?
> Would it be better, performance wise, to just continue
> using dynamic sql?
If you code the dynamic SQL properly, you can eliminate much of the
performance overhead. In your case, you're using sp_executesql, but are
not taking advantage of its main benefit over EXEC. That is, parameter
binding.
Instead of how it's currently coded, you could use:
set @.theSqlString = 'select r.Feild from ' +
'[' + @.theDatabase + ']' +
'..TableName r ' +
'where r.ID = @.thePrimaryKey'
Exec sp_executesql @.theSqlString, N'thePrimaryKey INT', @.thePrimaryKey
I'm not sure why it's necessary, though, given your example. You have a
hard-coded database name on the same server. If you know the database,
you can just fully-qualify the object in the SQL:
Select * from [AnotherDatabase].dbo.[MyTable]
David Gugick
Imceda Software
www.imceda.com|||Why do you want to parameterize the name of the target database?
If the name may change and/or you prefer not to hard-code it in SPs
then just create a view or views to reference the other database and
reference only the views in your SPs. That way the database name is
coded only in a few places instead of every SP.
David Portas
SQL Server MVP
--

Eliminating Dynamic SQL

I am refactoring stored procedures that use dynamic sql.
The reason the store procedures use dynamic sql is because
the data that is need comes from another MS SQL database
that resides on the same server instance.
The following is a code example from a stored procedure:
declare @.theDatabase sysname;
set @.theDatabase = 'A database that I get based on
critera at runtime.';
declare @.thePrimaryKey int;
set @.thePrimaryKey = 2;
declare @.theSqlString varchar(8000);
set @.theSqlString = 'select r.Feild from ' +
@.theDatabase +
'..TableName r ' +
'where r.ID = '
+ rtrim( str( @.thePrimaryKey ) )
exec sp_executesql @.theSqlString;
The problem is that dynamic sql is EXTREMELY slow! I have
been doing some research and have found a little bit on
sp_linkedservers and OPENQUERY but I have not figured out
how to accomplish what is done above.
Can anyone give me an example of how I can use
sp_linkedservers and OPENQUERY to accomplish this query?
Would it be better, performance wise, to just continue
using dynamic sql?
Any help would be greatly appreciated.
Sincerely,
John Dickey
jpd0861@.msn.comIf you have the same table structures in each database then you can
create a partitioned view across them and reference the partitioned
view instead. Whether this is the right solution though may depend on
just why you have the data split across multiple DBs in the first
place. It doesn't seem like a very practical architecture if it forces
you to write dynamic SQL in all your production code.
David Portas
SQL Server MVP
--|||Thank you for your reply David.
The reason that we have different databases is because the
databases are for different applications that my
application interfaces with. They happen to be Great Plains
accounting databases that I am trying to retrive data from.
Can you give me more information about the partitioned view
and how I can set that up?
John Dickey

>--Original Message--
>If you have the same table structures in each database
then you can
>create a partitioned view across them and reference the
partitioned
>view instead. Whether this is the right solution though
may depend on
>just why you have the data split across multiple DBs in
the first
>place. It doesn't seem like a very practical architecture
if it forces
>you to write dynamic SQL in all your production code.
>--
>David Portas
>SQL Server MVP
>--
>.
>|||It's all in Books Online:
http://msdn.microsoft.com/library/e...des_06_17zr.asp
David Portas
SQL Server MVP
--

Effects of changing a table name

If I rename a table on my SQL Server 2005 database say from Carriers to
Partners, will it update the stored procedures that reference the old table
name "carriers" to the new "partners" name? I am talking about using the
rename function in the rename function in the micosoft SQL Server managment
studio program. Or will I have to edit my 1,351 stored procedures manually?
thanks!> If I rename a table on my SQL Server 2005 database say from Carriers to
> Partners, will it update the stored procedures that reference the old
> table name "carriers" to the new "partners" name?
No, SQL Server is not going to go and edit your code for you. It would be
hard enough to find all the places it is referenced in vanilla T-SQL (and
this would rely on a correct sysdepends, rather than deferred name
resolution), what about all the places where the table name could be
referenced dynamically (e.g. EXEC('SELECT * INTO t1 FROM Carriers', or
EXEC('SELECT * INTO t1 FROM Car'+'riers'), or EXEC('SELECT * FROM
'+@.TableName))? Even if you took your procedure code offline and did a
grep/replace, you're still not guaranteed to find every single location.

> I am talking about using the rename function in the rename function in the
> micosoft SQL Server managment studio program. Or will I have to edit my
> 1,351 stored procedures manually?
An alternative would be to create a synonym.
A

Effect of a SELECT Stored Procedure on tables or database

Hi all,

I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.

For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?

No, SELECT statements don't cause any kind of locking.|||

And if I don't talk about locking, can I be rest assured that there won't be anything else that can have an effect?

|||

Selects are pretty unobtrusive. I can't think of anything you'd need to worry about.

|||

gt1329a:

No, SELECT statements don't cause any kind of locking.

If your isolation level is read committed, SELECTs do put a shared resource lock but it doesnt block any UPDATEs. Locking is different from Blocking. If reading to-the-minute committed data is not important you can explicitly use NOLOCK.

Effect of "Do not recompute statistics" option

I'm looking into the automatic recompilation of stored procedures and
I have been reading up on the "Do not recompute statistics" option on
indexes.

Am I correct in concluding that disabling the "Do not recompute
statistics" option for an index, will ensure that no automatic
recompilations will occur as a result of updates to data in that
index?

Am I also correct in understanding that the "Update Statistics" will
still update statistics for the index even if the "Do not recompute
statistics" option is disabled?

Regards

BjrnBjrn (bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I'm looking into the automatic recompilation of stored procedures and
I have been reading up on the "Do not recompute statistics" option on
indexes.
>
Am I correct in concluding that disabling the "Do not recompute
statistics" option for an index, will ensure that no automatic
recompilations will occur as a result of updates to data in that
index?
>
Am I also correct in understanding that the "Update Statistics" will
still update statistics for the index even if the "Do not recompute
statistics" option is disabled?

That is how I would read it to. I would still prefer to use sp_autostats
turn autostats off/on.

What sort of table are you considering to turn off autostats for? It seems
to me that this mainly is useful with tables that are modest in size, but
which are updated frequently.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Editing stored procedures...

I'm not a "real" programmer. Excuse my ignorance.

I need to edit a stored procedure. I simply want to change the text that it displays. I do not intend to change its function.

I am using SQL Express and SQL Server Management Studio Express. I can select Modify for my stored procedure, but when I save it, it saves it as an SQL file to my hard disk. How do I affect the edits to the actual stored procedure?

Hi,

When you save the stored procedure you just save the sql statement (.sql file).
You have to execute the statement.

So : go to your Stored procedure
Select 'Modify'
Make the necessary changes
Execute the code ( press F5)

Succes,

Jef

Editing Stored Procedures

Probably the wrong NG, but I can't find one for Visual Studio.
How are you supposed to edit SP's in VS 2003? I cannot find the option
anywhere. I can do it from Query Analyzer simply enough, but flipping back
and forth is a pain.
Hi Rock,
In Visual Studio 2003, you can create and edit a stored procedure via the
following steps:
1. Click View menu and select "Server Explorer";
2. Right click "Data Connections", click "Add Connection...", in the "Data
Link Properties" window, input the server name and select the database;
3. Expand the database connection "<server name>.<database name>.dbo",
expand "Stored Procedures", double click the stored procedure for browsing
or editing. If you want to create a new stored procedure, just right click
"Stored Procedures" and click "New Stored Procedure".
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Editing Stored Procedures

Probably the wrong NG, but I can't find one for Visual Studio.
How are you supposed to edit SP's in VS 2003? I cannot find the option
anywhere. I can do it from Query Analyzer simply enough, but flipping back
and forth is a pain.Hi Rock,
In Visual Studio 2003, you can create and edit a stored procedure via the
following steps:
1. Click View menu and select "Server Explorer";
2. Right click "Data Connections", click "Add Connection...", in the "Data
Link Properties" window, input the server name and select the database;
3. Expand the database connection "<server name>.<database name>.dbo",
expand "Stored Procedures", double click the stored procedure for browsing
or editing. If you want to create a new stored procedure, just right click
"Stored Procedures" and click "New Stored Procedure".
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Editing Stored Procedures

Probably the wrong NG, but I can't find one for Visual Studio.
How are you supposed to edit SP's in VS 2003? I cannot find the option
anywhere. I can do it from Query Analyzer simply enough, but flipping back
and forth is a pain.Hi Rock,
In Visual Studio 2003, you can create and edit a stored procedure via the
following steps:
1. Click View menu and select "Server Explorer";
2. Right click "Data Connections", click "Add Connection...", in the "Data
Link Properties" window, input the server name and select the database;
3. Expand the database connection "<server name>.<database name>.dbo",
expand "Stored Procedures", double click the stored procedure for browsing
or editing. If you want to create a new stored procedure, just right click
"Stored Procedures" and click "New Stored Procedure".
Hope this helps. If you have any other questions or concerns, please feel
free to let me know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================