CASE statement usage?

I am trying to do this inside a stored procedure: Select list of ids which will use conditions, When a id is in another list of ids which retrieved from a table and limited by an dynamically chosen WHERE condition using CASE statement.

I do realize I can not use CASE statement because after keyword THEN, it must be a value, can not be a condition statement.

My code having syntax error are:

SELECT ...

FROM ...

WHERE ...

AND lav.ListingAttributeId IN (
SELECT listingAttributeId
FROM @.TempListingAttributeValuesTable
WHERE
CASE comparision
WHEN 'Between' THEN
lav.Value BETWEEN CAST(attributeValue1 AS FLOAT) AND CAST(attributeValue2 AS FLOAT)
WHEN '=' THEN
lav.Value = CAST(attributeValue1 AS FLOAT)
WHEN '>' THEN
lav.Value > CAST(attributeValue1 AS FLOAT)
WHEN '<' THEN
lav.Value < CAST(attributeValue1 AS FLOAT)
WHEN '>=' THEN
lav.Value >= CAST(attributeValue1 AS FLOAT)
WHEN '<=' THEN
lav.Value <= CAST(attributeValue1 AS FLOAT)
END
)

Is there any other way I can select the search condition instead of using CASE?

Thank you.

WHERE
CASE
WHEN comparision = 'Between' AND lav.Value BETWEEN CAST(attributeValue1 AS FLOAT) AND CAST(attributeValue2 AS FLOAT) THEN 1
WHEN comparision='=' AND lav.Value = CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision='>' AND
lav.Value > CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision = '<' AND lav.Value < CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision='>=' AND lav.Value >= CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparison ='<=' AND lav.Value <= CAST(attributeValue1 AS FLOAT) THEN 1
ELSE 0
END = 1

|||Thank you. It solved my question.

I am wondering the possibility of building WHERE condition dynamically?

I think it is impossible, but my mate told me I could do it in other ways, but it needs to restructure the query.

Anyone got idea?

Use the code I posted as an example, is it possible if I want to do something like:
SELECT *
FROM TableName
WHERE condition1 or condition 2 or condition 3 etc.

The number of condition is not fixed.

Thank you.
sql

CASE statement in WHERE clause problem

Hello,
I would like to achieve the following within an stored procedure.
SELECT * FROM TableX WHERE ID = 1
OR
SELECT * FROM TableX WHERE ID IS NOT NULL
How can I solve this by using a condition in my WHERE clause?
eg.
//////
CREATE PROCEDURE TestID
@.ID INT
AS
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE NOT NULL
END
////
The problem is the ' NOT ' NULL in the ELSE Path
If I skip the ELSE Path then it will be implicitly NULL
Thanks for any help,
RemcoHi,
Try this
SELECT * FROM TableX WHERE ID = 1 OR ID IS NOT NULL
Hth
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Hello Remco,

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
if you want criteria:
1. @.ID != null --> ID = @.ID
2. @.ID IS NULL --> ID IS NOT NULL
then:
WHERE
(@.ID IS NOT NULL AND ID = @.ID)
OR (@.ID IS NULL AND ID IS NOT NULL)
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B|||this should get you started on how to do that.
http://www.aspfaq.com/show.asp?id=2501
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||Try,
SELECT * FROM TableX
WHERE [ID] = @.id or (@.id is null and [id] is null)
AMB
"Remco" wrote:

> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>
>|||
SET ANSI_NULLS ON
SELECT * FROM TableX
WHERE ID =
CASE
WHEN @.ID IS NOT NULL THEN @.ID
ELSE ID
END
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
> How can I solve this by using a condition in my WHERE clause?
>
> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
> The problem is the ' NOT ' NULL in the ELSE Path
> If I skip the ELSE Path then it will be implicitly NULL
> Thanks for any help,
> Remco
>|||Reading your post again, I realized that what you want is:
select * from tablex
where ([id] = @.id) or (@.id is null and [id] is not null)
AMB
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM TableX
> WHERE [ID] = @.id or (@.id is null and [id] is null)
>
> AMB
> "Remco" wrote:
>|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...

> eg.
> //////
> CREATE PROCEDURE TestID
> @.ID INT
> AS
> SELECT * FROM TableX
> WHERE ID =
> CASE
> WHEN @.ID IS NOT NULL THEN @.ID
> ELSE NOT NULL
> END
> ////
Possibly (untested):
SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
Good Luck,
Jim|||"James Goodwin" <jim.goodwin@.midmichigan.org> wrote in message
news:1fdb1$420b726d$432498ca$16254@.allth
enewsgroups.com...
> SELECT * FROM TableX where ID = COALESCE(@.ID,ID)
I think that will fail if ID is null. Better is
SELECT * FROM TableX where ISNULL(ID, '') = ISNULL(@.ID,'')|||"Remco" <rembo_r@.hotmail.com> wrote in message
news:eGlfZa3DFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I would like to achieve the following within an stored procedure.
> SELECT * FROM TableX WHERE ID = 1
> OR
> SELECT * FROM TableX WHERE ID IS NOT NULL
SELECT * FROM TableX WHERE ID IS NOT NULL
satisfies this condition, but I'm gathering that's not what you want. :)

Case Statement in Stored Procedure ?

I am converting IIF statements from an Access Query into a Stored Procedure
and was doing ok until I got to this one which is a bit more complicated:
CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] / [Field5]
END
All fields are valid boleen fields
I have tried putting parens around the equation part but still get errors.
How can I get the data into this field? Thanks.CAST as the bit columns as INT such as CAST([Field2] AS INT)
I think that [Field2] + [Field3] -[Field4] / [Field5]
should be ([Field2] + [Field3] -[Field4]) / [Field5]
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:F23EB696-E2B4-43EA-AC88-664FB8B2221E@.microsoft.com...
>I am converting IIF statements from an Access Query into a Stored
>Procedure
> and was doing ok until I got to this one which is a bit more complicated:
> CASE WHEN [Field1] = 1 THEN 0 ELSE [Field2] + [Field3] -[Field4] /
> [Field5]
> END
> All fields are valid boleen fields
> I have tried putting parens around the equation part but still get errors.
> How can I get the data into this field? Thanks.
>|||Please explain what "All fields are valid boleen fields" means.
Also post the error message that you get.
ML|||The Fields I'm trying to perform math on are boolean fields and are already
in place in the stored procedure.
The error says:
ADO Error:Invalid operator for data type. Operator equals add, type equals
bit.
Thanks for helping.
"ML" wrote:

> Please explain what "All fields are valid boleen fields" means.
> Also post the error message that you get.
>
> ML|||>> All fields are valid Boolean fields <<
UNH' Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files.
Where is the DDL? SQL has no Boolean data types and good programemrs
do not use the proprietary BIT data type.
There is no CASE statement in SQL, either; there is a CASE expression.
So what "field" are you trying to assign this exprsssion to?|||First of all, bit is not boolean, and second - the error message is pretty
much self-explanatory. Why are you adding, subtracting and dividing values
that can either be 1 or 0?
E.g.:
what does this mean to you:
1 + 0 - 1 / 1
or:
1 + 1 - 0 / 1
or worse:
1 + 1 -1 / 0
Please describe what you're trying to achieve. There must be some reason...?
ML|||
"ML" wrote:

> First of all, bit is not boolean, and second - the error message is pretty
> much self-explanatory. Why are you adding, subtracting and dividing values
> that can either be 1 or 0?
> E.g.:
> what does this mean to you:
> 1 + 0 - 1 / 1...this means 0%
> 1 + 0 / 1 + 1 ...this would be 50%
> 1 + 1 / 1 + 1 ...100%
> or:
> 1 + 1 - 0 / 1
> or worse:
> 1 + 1 -1 / 0...checked for and not allowed through business rules
> Please describe what you're trying to achieve. There must be some reason..
.?
>
> ML
I needed to be able to do math on the fields like I can in MS
Access...Changing the bits to integers worked.|||> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
Good programmers make proper use of approriate dtaa types in any given
situation.
Similary, good programmers don't just follow along with the comment "all
GOTO statements or CURSORs are bad", but take the time to understand why
other programmers prefer to use better alternatives. Cursors can also be
very powerful, but only when used in an appropriate situation where the same
result cannot be equally-well achieved via set-based operations.
If you were defining a table ApplicationUsers, and needed to store (in a
SQL2000 database) a field indicating whether the user was enabled, ( a field
that could only ever have a value of Yes/No), what field would YOU use ...

> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
Whilst I agree with you that the snippet is technically an expression, not a
statement, this value doesn't necessarily need to be assigned to a field.|||I guess if I were an SQL expert I wouldn't need to use this web site and
expose my ignorance of SQL terminology to arrogant know-it-alls who find it
easier to berate my lack of knowledge than just simply give me the answer I
was looking for like Raymond D'Anjou so kindly did. I would appreciate you
not anwering any of my posts in the future.
"--CELKO--" wrote:

> UNH' Let's get back to the basics of an RDBMS. Rows are not records;
> fields are not columns; tables are not files.
> Where is the DDL? SQL has no Boolean data types and good programemrs
> do not use the proprietary BIT data type.
> There is no CASE statement in SQL, either; there is a CASE expression.
> So what "field" are you trying to assign this exprsssion to?
>|||AkAlan,
Don't let Celko intimidate you, and as for him being a SQL expert, well,
perhaps an ANSI 92 expert and thats about as far as it goes.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:0A58D8F7-6E39-4072-9634-2082745BA421@.microsoft.com...
>I guess if I were an SQL expert I wouldn't need to use this web site and
> expose my ignorance of SQL terminology to arrogant know-it-alls who find
> it
> easier to berate my lack of knowledge than just simply give me the answer
> I
> was looking for like Raymond D'Anjou so kindly did. I would appreciate you
> not anwering any of my posts in the future.
> "--CELKO--" wrote:
>

Case Statement in a Stored Procedure

Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure?
I'm trying to do the following:
CASE
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
ELSE 'no room number'
END
Is there another way of doing this?
Any help would be appreciated.
Thanks>> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
statements in a stored Procedure? <<
Yes, you can do it. It is well documented in SQL Server Books Online with
examples too. Did you come across any problems using it?
--
- Anith
( Please reply to newsgroups only )|||HI Berny,
This looks fine, but it's hard to tell if there's an alternative without
knowing what the business rules are, what the tables look like, what the
data within those tables looks like, and what the expected output is
supposed to be. A case statement with multiple WHEN statements will work
fine in a stored procedure, but you might be able to accomplish the same
thing using a UNION and specific WHERE Clauses for each UNION. It might be
better, but then again, it might not be...without knowing all of the
details, who knows...
HTH
--
Regards,
Don R. Watters
Data Group Manager
PhotoWorks, Inc.
"Berny" <BlancoB at msn Dot com> wrote in message
news:OrJK7f4uDHA.2148@.TK2MSFTNGP12.phx.gbl...
> Can anyone tell me if I can use the CASE statement with multiple 'WHEN'
> statements in a stored Procedure?
> I'm trying to do the following:
> CASE
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> cast(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) + '-' +
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) > 0 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) < 1 THEN cast('BLDG: ' +
> RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR) AS char(20))
> WHEN len(RTRIM(dbo.Deficiencies_Tmp.BLDG_NBR)) < 1 AND
> len(RTRIM(dbo.Deficiencies_Tmp.room_nbr)) > 0 THEN
> RTRIM(dbo.Deficiencies_Tmp.room_nbr) AS char(20))
> ELSE 'no room number'
> END
> Is there another way of doing this?
> Any help would be appreciated.
> Thanks
>

Case Statement

hi,
my procedure is
create procedure ct_tpin(@.Start_Date datetime,@.End_Date datetime,@.Rpt_Name
varchar(50))
as
begin
case when @.Rpt_Name = 'TpinGenerated' then
select gr_tpin_flag_t.KEY1, gr_cust_m.Name,gr_tpin_flag_t.dateandtime FROM
gr_tpin_flag_t, gr_cust_m
WHERE (gr_tpin_flag_t.tpinflag = 'G') AND (gr_tpin_flag_t.key1 =
gr_cust_m.key1) and
(dateandtime between convert(datetime, @.Start_Date ,3)
and convert(datetime,@.End_Date,3)
end
end
its throwing error near case and end, pls give me a solution.
thanks
vanithaHi Vanitha,
What about
IF @.Rpt_Name = 'TpinGenerated' THEN
BEGIN
<YourCodeor Selectstatement)
END
HTH, Jens SUessmeyer.|||its the alternate method.
but i want to know how i can handle this in case statement
"Jens" wrote:

> Hi Vanitha,
>
> What about
> IF @.Rpt_Name = 'TpinGenerated' THEN
> BEGIN
> <YourCodeor Selectstatement)
> END
> HTH, Jens SUessmeyer.
>|||CASE is an expression, not a statement. You cannot use CASE the way you trie
d. Use IF instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:18033B4C-6327-4E07-BD91-F5DC81DD5C32@.microsoft.com...
> hi,
> my procedure is
> create procedure ct_tpin(@.Start_Date datetime,@.End_Date datetime,@.Rpt_Name
> varchar(50))
> as
> begin
> case when @.Rpt_Name = 'TpinGenerated' then
> select gr_tpin_flag_t.KEY1, gr_cust_m.Name,gr_tpin_flag_t.dateandtime FROM
> gr_tpin_flag_t, gr_cust_m
> WHERE (gr_tpin_flag_t.tpinflag = 'G') AND (gr_tpin_flag_t.key1 =
> gr_cust_m.key1) and
> (dateandtime between convert(datetime, @.Start_Date ,3)
> and convert(datetime,@.End_Date,3)
> end
> end
> its throwing error near case and end, pls give me a solution.
> thanks
> vanitha|||There is no Case "Statement" in TSQL.
Case is an Expression that can be used as part of a query not a control flow
statement like in other languages.
Please read the syntax for it in BOL
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:449441A9-BDA1-43AD-B49E-866EE67EE89D@.microsoft.com...
> its the alternate method.
> but i want to know how i can handle this in case statement
> "Jens" wrote:
>sql

Case Sensitivity

I am wondering if T SQL in SQL Server 2005 is case sensitive.I running am running a query in a stored procedure whchi compares a passed in value with that in a field in the database, as such, is their a need to do this

SELECT * FROM table WHERE UPPER(column_name) = UPPER(@.var)

or will this return the same results

SELECT * FROM table WHERE column_name = @.var

1) A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters

2) T-SQL in also case insensitive.

|||

To see which type your database is, go into SQL Server Management Studio, right-click your database and choose Properties. Select 'General' on the left-hand side, and look at the Collation property (under the 'Maintenance' heading). Somewhere in the name of the property, you'll have either a CS or a CI, standing for Case Sensitive or Case Insensitive, respectively.

I.e., mine is "SQL_Latin1_General_CP1_CI_AS" (SQL Server 2000-compatible). The _CI_ denotes the database is case insensitive.

sql

Case sensitive problem

Hi,
I'm working on a SQL 2000 server that was not set up by me. I tried creating
stored procedure on it and found all variables are case-sensitive. How do I
change it to case insensitive?
For example, if I use QueryAnalyzer and enter in the following two lines:
DECLARE @.VAR1 int
SET @.var1 = 3
I would get the following error,
Must declare the variable '@.var1'.
Any help would be greatly appreciatedYou would change the database's collation to a case-insensitive collation.
You may want to find out why it's using a case-sensitive collation before
changing it though; there could be a method to the madness.
"Ming" wrote:

> Hi,
> I'm working on a SQL 2000 server that was not set up by me. I tried creati
ng
> stored procedure on it and found all variables are case-sensitive. How do
I
> change it to case insensitive?
> For example, if I use QueryAnalyzer and enter in the following two lines:
> DECLARE @.VAR1 int
> SET @.var1 = 3
> I would get the following error,
> Must declare the variable '@.var1'.
> Any help would be greatly appreciated|||I used EM and right clicked on that database and clicked the property page,
the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I believ
e
is case insensitive. Any idea?
"KH" wrote:
> You would change the database's collation to a case-insensitive collation.
> You may want to find out why it's using a case-sensitive collation before
> changing it though; there could be a method to the madness.
>
> "Ming" wrote:
>|||I think that variable name sensitivity is determined by the server's
collation, not the local database's collation.
The server's collation is chosen in the installation, and is the collation
set for all system databases.
You can check the server's collation by running sp_helpsort, or select
serverproperty ('collation').
If the server's collation is case sensitive, that would explain your issues.
Changing the server's collation is not a simple task, even if you don't have
any applications that rely on or require case sensitivity.
You can try to enforce a policy where all variable names are, say, always
lower case.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ming" <Ming@.discussions.microsoft.com> wrote in message
news:7B241490-F50A-41C1-B269-AB4F18EA778F@.microsoft.com...
>I used EM and right clicked on that database and clicked the property page,
> the database collation name is: SQL_Latin1_General_CP1_CI_AS, which I
> believe
> is case insensitive. Any idea?
> "KH" wrote:
>

Case Problem!

Hi all,
I am trying to create the following stored procedure..
I am still not all that familiar with the Case statement so any help would
be appreciated...!!!
I am getting a syntax errors...
Is there a better way to do this?
CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
@.area VARCHAR(120)
AS
BEGIN
Declare @.last_induction INT
SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
CASE
WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
VALUES(@.area)
WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
VALUES(@.area)
WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
VALUES(@.area)
WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
VALUES(@.area)
END
Cheers,
AdamCASE is an expression, not a statement; it is not used as a
control-of-flow element. To do what you're attempting to do, you'll
need a series of IF.. ELSE statements.
Of course, just from a cursory glance at your code, it appears that
your data model needs work; what do asmt_1, asmt_2... represent? Are
they different attributes of your entity, or are they simply holders
for value?
Stu|||The data model sucks i know,...
But the request from the powers that be, is that is must be that way..
Thanks for the info..
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>|||without nagging you about the schema and all...here is the insert without
if/else.
insert asmt_v1_cards(asmt_1,asmt_2,asmt_3,asmt_
4)
select case when @.last_induction is null then @.area end,
case when @.last_induction=1 then @.area end,
case when @.last_induction=2 then @.area end,
case when @.last_induction=3 then @.area end
-oj
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>

CASE Problem in Stored Procedure

Can anyone see a problem with this stored procedure.
When I do this one it works fine.

CREATE Procedure SS_SoftList
(
@.CompanyID nvarchar(10),
@.Order varchar(20)
)
As

SELECT
SS_Soft_deploy.Softwarename,
( COUNT(SS_Soft_deploy.Softwarename)) as recordcount

FROM
SS_Soft_deploy

WHERE
SS_Soft_deploy.CompanyID = @.CompanyID

GROUP BY
SS_Soft_deploy.Softwarename

ORDER BY recordcount
--CASE WHEN @.Order = 'softname' THEN Softwarename END,
--CASE WHEN @.Order = 'count' THEN recordcount END
GO

However when I un-rem the CASE statement
ORDER BY
CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN recordcount END
GO

It reports an error that says "Invalid Column Name recordcount"
Any Ideas??Recordcount is an alias. You cannot sort on that using the alias.

CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN ( COUNT(SS_Soft_deploy.Softwarename)) END

|||Why does it work when I do the
ORDER BY recordcount
??|||Hmm. I did not realize that would work using the alias in the ORDER BY, but apparently it does. It does not work inside the case statement because that is a different expression, and that expression is unaware of the alias. Repeating the expression, however, works fine. If the expression were terribly complex, you could alternately create a user defined function and use that for the row and the ORDER BY (knowing that performance would likely be less than great).

case expression stor proc, need some help

ALTER PROCEDURE dbo.TEST_TOTALCALLS
(
@.varDate as varchar (255),
@.StartDate as datetime,
@.EndDate as datetime
)
AS

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <--this part gave me an error, because of the comma,
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END

The month case is giving me an error. I think it has to do with two expressions in one line.
Anyone know how to combine that into 1 expression? or is there away to work around it?
As I would like to display the month as Name, but group and sort by number.
Thx!~Are you saying you don't get the data you want if you remove the datename part from your group by clause? You are getting the month name out by the select part of your procedure and I don't see the need to also group by it if you only want to group by number.|||well, I am trying to get data displayed in the name of the month, but not in ABC order.
i.e.

January
Feb
March

instead of ABC order,
April
December
Febuary

I did the sql before grouping them together.
And this worked,

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)

Yet, the GROUP BY clause consist oftwoexpressions for it to function, (from my understanding)
and I don't know how to make that clause work in a CASE expression.

Thx in advance~|||oh yea, this doesn't work from my understanding:

SELECT
DATENAME(mm, CALLSTARTTIME),
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)|||How about this:-

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME)
END,
CASE @.varDate
WHEN 'Year' Then ??
WHEN 'Quarter' Then ??
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME)
END

I think it would only work if you could put something in for the Year and Quarter too (where the ?? are). Might not work at all.
The only other thing would be to perhaps use a sql if to have 2 different selects, one for month with case no longer needed and one for the other 2 using case:-

if @.varDate='Month'
begin
SELECT DATENAME(mm, CALLSTARTTIME), COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)
GROUP BY
DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME)
ORDER BY
DATEPART(mm, CALLSTARTTIME)
end
else
begin
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END,
COUNT(*) as 'Total Calls'
FROM CALLMASTER
WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @.StartDate) AND (CALLENDTIME <= @.EndDate)

GROUP BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
ORDER BY
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME)
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)
END
end|||Thank you Brian. That will work.

Side Question: is there a way to name the heading by case?

e.g.
SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END,|||Yes there is:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname|||SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) <--This will have As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME)<--This will have As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) <--This will have as 'Month'
END as myheadingname

Can myheadingname varies by case? like can it be a parameter/variable,and it will display different headings depending on the case selected.

e.g.
When @.varDate = 'Year', the column header will be 'Year'.
When @.varDate = 'Month', the column header will be 'Month'.
When @.varDate = 'Quarter', the column header will be 'Quarter'

the above example, as myheadingname is a generalized header. It will not differ no matter what @.varDate is.|||I don't think you can but no harm in trying something like that:-

SELECT
CASE @.varDate
WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) As 'Year'
WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) As 'Quarter'
WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) as 'Month'
END

You will probably get an error if it doesn't work.|||yup, got an error.|||Well other than that you can expand the if for each possibility and do without the case.

Cascade 2 strings in stored procedure for MS SQL

What is the code to cascade two strings

SET @.string1 = 'aa'
SET @.string2 = 'bb'

How can I get a string 'aabb' in stored procedure in SQL SERVER 2000?

thanksAs I understand ur question u can show such

Print @.string1 + @.string2

Capturing the output from store procedure and use it

How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?

For example,

CREATE PROCEDURE dbo.sp_test AS
-- returns all words not in Mastery Level 0

EXEC sp_anothertest

-- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults

SELECT * tblFinalResults
GO

Thanks!I do not think you can do this exactly as you would like. You may need to resort to either a UDF which returns a table, using table variables within the stored procedure, or using temporary tables. Functions are the most flexiable and temporary tables are the slowest. You can also use table variables as output parameters of the stored procedures. Below is an example of using table variables.
begin
DECLARE @.Result1 table (key1 int, foo varchar(32) )
insert into @.Result1 select 1, 'This is Table 1'

DECLARE @.Result2 table ( key2 int, foo varchar(32) )
insert into @.Result2 select 1, 'This is Table 2'

select * from @.Result1 inner join @.Result2 on( key1 = key2 )
end

capturing the output from a stored procedure into a report

I have a stored procedure that takes some parameters. I can execute it
in the data tab in vb.net and get my results. How do i take these
results and form a report? Is there a way to capture the fields that
are returned in order to drop them into a report?
I'm doing all this in vb.net.
Thanks.Are you using the report designer and the data tab? Does the stored
procedure execute and return data from the data tab? If so, sometimes
executing the stored procedure does not fill the field list. Try clicking on
the refresh fields button (look to the right of the ... , it looks like the
fresh button for IE. Hover over it and it will tell you what the button is
for). If this doesn't cause the field list to fill in then you can put in
the fields manually in the list. Right mouse click in the field list, add
field and give it the name of the field name.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"muris" <rmuris@.hotmail.com> wrote in message
news:1112374203.464263.41490@.z14g2000cwz.googlegroups.com...
> I have a stored procedure that takes some parameters. I can execute it
> in the data tab in vb.net and get my results. How do i take these
> results and form a report? Is there a way to capture the fields that
> are returned in order to drop them into a report?
> I'm doing all this in vb.net.
> Thanks.
>|||hitting the refresh button worked!! Thank you.

Capturing the Error Description in a stored procedure

Hi,

I have a few stored procedure which will be executed one after another. If any error occurs, i need capture the Error number and ERROR DESCRIPTION and SAVE it into a table within stored procedure itself. Any idea how to do it?

I saw a similar problem from http://www.sqlservercentral.com/columnists/ajethva/capturingtheerrordescriptioninastoredprocedure.asp but i cannot download the sample code.

i want to CAPTURE the following msg :

e.g. Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

It would be great if you could send sample code.

thanks.

rama

If you use SQL server 2000, you can use the following batch. But you can’t suppers the error message.

Code Snippet

Create table ErrorLog

(

Source nvarchar(100),

CalledUser varchar(100),

ErrorNumber int,

ErrorDescription nvarchar(2000),

ErrorDatetime datetime

)

Go

Create proc MyProc

@.I as int

as

Declare @.Error as int

Select 1/@.I

Set @.Error = @.@.ERROR

If @.Error <> 0

Begin

Insert Into ErrorLog

select

object_name(@.@.PROCID),

suser_sname(),

@.Error,

description,

getdate()

from

Sysmessages

Where

error=@.Error

and msglangid = (select msglangid from syslanguages where name='us_english') -- You can change to your local language

End

Go

Exec MyProc 1

Go

Exec MyProc 0

Go

select * from ErrorLog

Capturing stored procedures parameters

Is it possible to capture, via trace or other means, the value of the
parameters passed to a stored procedure? There is a stored procedure in the
SharePoint database I want to monitor, but I don't want to change it. I want
to know when it's called and what parameters were passed to it.
Thank you in advance,
Daniel
This can easily be done using SQL Profiler. Just establish a new trace
using one of the SQLProfilerTSQL_xxxx templates. If you are only looking to
trace a single procedure, then you should probably play with the filter
criteria to eliminate some of the background noise otherwise you'll need to
wade through all of the TSQL commands being executed.
--Brian
(Please reply to the newsgroups only.)
"Daniel Corra" <daniel.correa@.e-component.com> wrote in message
news:esZKlWeoFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Is it possible to capture, via trace or other means, the value of the
> parameters passed to a stored procedure? There is a stored procedure in
> the SharePoint database I want to monitor, but I don't want to change it.
> I want to know when it's called and what parameters were passed to it.
> Thank you in advance,
> Daniel
>

Capturing Data Type Mismatch

Hi,
Create Table tb_mismatch
(x int)
Create Procedure proc_mismatch
as
begin
insert into tb_mismatch values('s')
if @.@.error<>0
begin
print ' entered error loop'
end
print 'successfully exited'
end
exec proc_mismatch --executing the proc
Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
any ideas are highly appreciated.
Thanks & regards,
Pavan.It looks like a little data checking is needed somewhere. Here is one example:

Create Table dbo.tb_mismatch
(x int)


Create Procedure dbo.proc_mismatch @.var varchar(50), @.err int OUTPUT
as

if isnumeric(@.var) = 1
BEGIN
insert into dbo.tb_mismatch (x) values(@.Var)
END
ELSE
BEGIN
set @.err = -1
END


if @.err<>0
begin
print 'encountered error'
end
else
begin
print 'successfully exited'
end



declare @.var varchar(50), @.errreturn int
set @.var = 's'
set @.errreturn = 0

exec dbo.proc_mismatch @.var, @.errreturn OUTPUT --executing the proc
select @.errreturn

drop proc dbo.proc_mismatch
drop table dbo.tb_mismatch|||Thanks for your quick response but my requirement is:
I have several update and insert statements in my actual procedure which fetches the data from an oracle DB and updates the sql database.. during these updates and inserts Business wants me to capture all the system related errors and when i am trying to capture the data mismatch error(manually placing a varchar value in a float field) the cursor is directly moving to the end of procedure,instead of populating the log file.
I dont think placing isnumeric for all int and float fields is the feasible solution,
any other ways??

Many Thanks
Pavan.|||No

And this sounds like a batch process...

I woul unload the data from oracle, bcp the data in to sql server, perform my audits, then load the data in|||Sounds good but it doesn't help my requirement as i have lots of validations to be done before performing any transactions and even need to Rollback transactions in some cases..
Do we have any exception handling mechanism to handle this ..other than raiseerror as it didn't worked out.. or is this a bug in sqlserver?? like we have when VALUE_ERROR exception in oracle|||SQL Server error handling is kludgey in 2000. SQL 2005, takes for steps to address that, but I haven't looked in to it.

So why can't you do basic aduitng in batches in a set based manner? What's the difficulty. You will need some staging tables, bit so what?

You need to divorce yourself from sequential cursor processing that you're accostomed too in Oracle...even in Oracle, it is over used a lot of times.

Good Luck.

If you continue to do it this way, create a second stored procedure that gets called...like a nested stored procedure...when the nested proc fails, it will rais out to the calling stored procedure, and the driver can then handle the error...but that's the long way around the mountain|||Brett,
thanks for ur concern.
I have tried the second option but it hasn't helped me out.
My code goes something like this..
gets the jobnumber and its related info from the oracle job master table..checks for its existance in sql db and then creating 2 cursors for diff tables checks and then lots of if's and else's,calculations..and once it goes through all the validations we will start inserting the details into some tables,move data to history and then update the main job table..if it fails in any of the case just rollback the whole operations..now the turn of next job comes into picture..
As of now it works fine until we dont get varied data from oracledb which has the similar db structure of sql server.
My scope is till its developed.|||I still don't know why you can't do something like this

USE Northwind
GO

-- Set up the situation
SET NOCOUNT ON
CREATE TABLE ORACLE_TABLE(Col1 varchar(10))
CREATE TABLE SQL_TABLE(Col1 int)
GO

-- Create some sample Data

INSERT INTO ORACLE_TABLE(Col1)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd'
GO

-- Report On Bad Data

SELECT * FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 0

-- Place the good data in to SQL
INSERT INTO SQL_TABLE(Col1)
SELECT (Col1) FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 1
GO

SELECT * FROM SQL_TABLE
GO

SET NOCOUNT OFF
DROP TABLE ORACLE_TABLE, SQL_TABLE
GO|||Try doing the same with a small change,changing the datatype from varchar to int,as this is my current structure,without using isnumeric option as my table has lots of columns and there are lots of insert and update statements.
CREATE TABLE ORACLE_TABLE(Col1 int)

Came to know that this error cannot be captured by sqlserver 2000 which is resolved in the next version sqlserver 2005 using the try catch block.

capture sql output parameters

When I use a stored procedure for a dataset, is there anyway to capture
output parameter values when the stored procedure executes?
--
Thanks,
CGWSorry, the current limitation is one recordset. The Microsoft folks may
clarify future plans to support multiple recordsets or output parameters.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"CGW" <CGW@.discussions.microsoft.com> wrote in message
news:96E5024E-A545-4D13-A49D-79ECAC33AD36@.microsoft.com...
> When I use a stored procedure for a dataset, is there anyway to capture
> output parameter values when the stored procedure executes?
> --
> Thanks,
> CGW

Capture Return Value from Stored Procedure, Use Same in Code Behind Page

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@.myERROR" then display in the label I have that a duplicate entry was attempted.

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50)

AS

DECLARE @.myERROR int -- local @.@.ERROR
, @.myRowCount int --local @.@.rowcount
BEGIN
-- See if a contact with the same name and zip code exists
IF EXISTS (Select * FROM Info
WHERE ID = @.ID)

BEGIN
RETURN 1
END
ELSE
BEGIN TRAN

INSERT INTO Info(IDNumber, ID, LName,
FName) VALUES (@.IDNumber, @.ID, @.LName,
@.FName)
SELECT @.myERROR = @.@.ERROR, @.myRowCount = @.@.ROWCOUNT
If @.myERROR !=0 GOTO HANDLE_ERROR

COMMIT TRAN
RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @.myERROR

END
GO

asp.net page
<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"
SelectCommand="SELECT * FROM TABLE_One"

UpdateCommand="UPDATE TABLE_One WHERE ID = @.ID"

InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />
</SelectParameters>

</asp:SqlDataSource>

You have to declare it as output parameter in your stored procedure

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50),
@.myERROR int OUTPUT

and just read value of this parameter after you close connection in which you call you SP.

remember to set parameter type as INPUTOUTPUT or OUTPUT when you define parameter in you VB code.

|||

Hi,

I included "@.myERROR" in the stored procedure, but have no idea where or how to read its value. As far as closing the connection in which I call the sp; my sp is called within the SqlDataSource, so how would I read/write the Return Value.

Thank you.

Ayomide

Capture Result of Update?

I have an Update statement in a stored procedure, and I want to capture
the number of rows affected for returning to the caller. If this was a
simple Insert, I'd use Scope_Identity() to get the Identity. But in
this case I want the count of all updated rows.
Thanks.
You want to use either @.@.RowCount (or RowCount_Big() if there is any
possibility that more than 2 billion rows may be affected by the Update
statement.
Something like:
Declare @.UpdateCount int
Update Mytable Set ... Where ...
Select @.UpdateCount = @.@.RowCount
Tom
Then @.UpdateCount will contain the number of rows which matched the Where
clause in your Update statement.
<bradwiseathome@.hotmail.com> wrote in message
news:1143750464.490863.149600@.v46g2000cwv.googlegr oups.com...
>I have an Update statement in a stored procedure, and I want to capture
> the number of rows affected for returning to the caller. If this was a
> simple Insert, I'd use Scope_Identity() to get the Identity. But in
> this case I want the count of all updated rows.
> Thanks.
>

Capture name of stored procedure within itself?

Is there any way of capturing the name of a stored procedure during
execution?
I've created a generic stored procedure to handle much of my error
handling. I would like to be able to have the calling stored
procedure to pass its name as one of the parameters to the error
handling proc to be logged on certain error messages events.
I could easily hard code the stored procedure name on all of the
calls, but I am trying to create a series of generic code blocks to
add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
ETC), so I was hoping to find a function or create a function that
would give the name of the proc.
Any thoughts would be appreciated.
SELECT OBJECT_NAME(@.@.PROCID) AS ProcName
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0408300912.2d9c1f2c@.posting.google.c om...
> Is there any way of capturing the name of a stored procedure during
> execution?
> I've created a generic stored procedure to handle much of my error
> handling. I would like to be able to have the calling stored
> procedure to pass its name as one of the parameters to the error
> handling proc to be logged on certain error messages events.
> I could easily hard code the stored procedure name on all of the
> calls, but I am trying to create a series of generic code blocks to
> add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
> ETC), so I was hoping to find a function or create a function that
> would give the name of the proc.
> Any thoughts would be appreciated.
|||Great, thanks