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 Senstivity on SQL Server

I have written several stored procedures on my local SQL server database.
Through my coding i will occasionally change the case of a variable. For
example I will declare a variable @.SQL varchar(20) but later call it @.sql
(lower case).
My sql server isn't case sensitive, but other databases I have loaded the
stored procedure are. How can I change the SQL server settings to not be
case sensitive? Plus how I can I change it back if it screws something up
with other applications that use other databases on the server? Is it a
server setting or database setting?
Thanks in advance!
Matt,
You will be much better off correcting the uppercase/lowercase
inconsistencies in your stored procedures. The most reasonable
assumption is that the server and database collations were chosen for a
good reason, but it doesn't sound like there's any reason you need to
vary the case in your procedures. To answer your question, yes, it can
break things if you change the collation of a database. One of the more
likely things that can happen is that it will cause data type
conversions that make indices impossible to use, slowing down performance.
The collation of the server instance is set when the instance is
installed. You can change database collations with ALTER DATABASE, but
why risk breaking things because you don't want to take the time to be
more careful coding?
Steve Kass
Drew University
Matt Tapia wrote:

>I have written several stored procedures on my local SQL server database.
>Through my coding i will occasionally change the case of a variable. For
>example I will declare a variable @.SQL varchar(20) but later call it @.sql
>(lower case).
>My sql server isn't case sensitive, but other databases I have loaded the
>stored procedure are. How can I change the SQL server settings to not be
>case sensitive? Plus how I can I change it back if it screws something up
>with other applications that use other databases on the server? Is it a
>server setting or database setting?
>Thanks in advance!
>
>
>

Case sensitivity in SQL -- ignore

Hi,

I believe my SQL server was configured as Case sensitivity. I have a
number of stored procedures which were moved from a non-Case
sensitivity SQL server. Because of the Case sensitivity, I have to do
a lot of editing in those stored procedures. Is there a quick way to
avoid the editing?

Something like ignoring the case in one statement?

Thanks in advance, your advice will be greatly appreciated.On Mar 14, 11:59 pm, sweetpota...@.yahoo.com wrote:

Quote:

Originally Posted by

Hi,
>
I believe my SQL server was configured as Case sensitivity. I have a
number of stored procedures which were moved from a non-Case
sensitivity SQL server. Because of the Case sensitivity, I have to do
a lot of editing in those stored procedures. Is there a quick way to
avoid the editing?
>
Something like ignoring the case in one statement?
>
Thanks in advance, your advice will be greatly appreciated.

I think by changing the collation of your database to case
insenstitvity may help
but this may cause problems when you create #temp tables as tempdb
will have server collation

If your column and table names are in lower case , you can modify the
SP by selecting the SP and changing to lowercase (SHIFT+CTRL+L) . But
if you have string constants which need to be in uppercase , you need
to change it accordingly

M A Srinivas|||>I believe my SQL server was configured as Case sensitivity. <<

As it should be; that is how Standard SQL is defined

Quote:

Originally Posted by

Quote:

Originally Posted by

>Because of the Case sensitivity, I have to do a lot of editing in those stored procedures. <<

Life is tough when someone screws up. You need to just do it right
and stop looking for kludges. Oh, and if yuou can kill them guy that
did this, you will probably improve the quality of your company's
software in other places as well.

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 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 in where clause?

I want to create SQL (maybe stored proc) using a SELECT statement that can
add WHERE conditions if (and only if) values are sent to it. For example:
SELECT ID, Person
FROM People
WHERE LastName LIKE '%@.var1%' +
CASE WHEN @.var2 IS NOT NULL THEN ' AND TypeCode = ' + @.var2
ELSE ''
END
...etc
Is this possible? Thanks
David> SELECT ID, Person
> FROM People
> WHERE LastName LIKE '%@.var1%' +
> CASE WHEN @.var2 IS NOT NULL THEN ' AND TypeCode = ' + @.var2
> ELSE ''
> END
Did you mean:
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND TypeCode = COALESCE(@.TypeCode, TypeCode)
or more verbosely
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND TypeCode = CASE WHEN @.TypeCode IS NULL THEN TypeCode ELSE @.TypeCode END
CASE is an expression that returns a single value. It is *NOT* used for
flow control.|||could I suggest a slight amendment (and add a question):
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND (@.TypeCode is null OR TypeCode = @.TypeCode)
this way when @.typecode is null the lazy evaluation of SQL will mean it
won't even look at the second statement.
I've found if it's put the other way:
WHERE Typecode = @.TypeCode OR @.TypeCode is null
it will first evaluation Typecode = @.TypeCode, causing it to scan that
field, then it will evaluate the second statement.
I'm not sure but I suspect your suggestion of Typecode =
COALLESCE(@.TypeCode, TypeCode) will cause it to read the typecode field
even when a null value is passed to it.
Cheers
Will|||> I've found if it's put the other way:
> WHERE Typecode = @.TypeCode OR @.TypeCode is null
> it will first evaluation Typecode = @.TypeCode, causing it to scan that
> field, then it will evaluate the second statement.
There is no guarantee in the order of execution within a clause, it could go
left to right, it could go right to left.

> I'm not sure but I suspect your suggestion of Typecode =
> COALLESCE(@.TypeCode, TypeCode) will cause it to read the typecode field
> even when a null value is passed to it.
Really hard to say without table structure, sample data, row counts, and the
ability to actually test and examine query plans.|||"There is no guarantee in the order of execution within a clause, it
could go
left to right, it could go right to left. "
I know you're an MVP, and do indeed know SQL server way better than me,
but still I feel I have to ask - are you sure about this?
it's just that I've found on several occasions that if I switch the 2
statements around it changes the execution plan fundamentally, and
doesn't do any table scans (when the value is null of course). If the
order was not an issue then surely switching the order would not affect
the query?
Thanks
Will|||> it's just that I've found on several occasions that if I switch the 2
> statements around it changes the execution plan fundamentally, and
> doesn't do any table scans (when the value is null of course). If the
> order was not an issue then surely switching the order would not affect
> the query?
I didn't say order CAN'T be an issue. Just don't rely on this kind of
short-circuiting to work consistently.|||Fundamental mistake! There is no CASE **statement** in SQL. There is
a CASE **expression**; remember programming 101? Expressions return
scalar values, not control of execution flow.
SELECT person_id, person_name
FROM People
WHERE last_name LIKE '%' + @.var1 + '%'
AND foobar_code = COALESCE(@.my_code, foobar_code) ;
Even for an example, you had some pretty awful data element names.
Something can be a type of something or a code. It cannot be both.
There is no such thing as just an "id" -- it has to identify something
in particular.
You might want to get a book on SQL and data modeling.|||Will (william_pegg@.yahoo.co.uk) writes:
> could I suggest a slight amendment (and add a question):
> SELECT ID, Person
> FROM People
> WHERE LastName LIKE '%' + @.var1 + '%'
> AND (@.TypeCode is null OR TypeCode = @.TypeCode)
> this way when @.typecode is null the lazy evaluation of SQL will mean it
> won't even look at the second statement.
It's unclear here what you mean with "second statement".
@.TypeCode IS NULL OR TypeCode = @.TypeCode
would be the same as
TypeCode = @.TypeCode OR @.TypeCode IS NULL
when it comes to performance.
If you've seen something else, it might have been a mirage due to
caching.
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|||My Apologies,
I really should stop questioning MVPs, you guys don't ever seem to be
wrong. I tried to recreate the situation and couldn't. My reasoning was
based on several queries I've debugged in the past that were displaying
a strange execution plan, and then by switching the statements around
(the "or" statements), increased performance dramatically (from 4s to
50ms). I assumed at the time that it was just a simple grammar case
that you could apply lazy evaluation, and that was how SQL was
optimising it, however it appears that there's more stuff going on.
perhaps the optimiser was having an odd day, or more likely there were
incorrect index statistics monkeying things up, but anyway, thanks for
clearing it up.
Cheers
Will|||The problem is that SQL is set-oriented and not sequential. The THEN
clauses in a CASE expression (which includes COALESCE()) all have to be
evaluated to determine the data type of the whole expression. It does
not matter if some of them are unreachable.
COALESCE correctly promotes its arguments to the highest data type in
the expression:
13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5
The proprietary ISNULL() uses the first data type and gets things wrong
13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6
You would need to write:
13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)sql

Case Expression within a Stored Proc

Is it possible? I have a request to create a stored proc that will
dynamically add a range to a WHERE clause based on a numeric value of a
comment type. If the incoming comment type request is say 10, the
where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
a 20 is passed in the clause would read IN(20,21....)
So I was thinking that a CASE expression within the proc would be the
best way to go, but have had no luck in finding an example or any other
related information regarding CASE exp in a proc.
TIA
BillOn 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:

>Is it possible? I have a request to create a stored proc that will
>dynamically add a range to a WHERE clause based on a numeric value of a
>comment type. If the incoming comment type request is say 10, the
>where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
>a 20 is passed in the clause would read IN(20,21....)
>So I was thinking that a CASE expression within the proc would be the
>best way to go, but have had no luck in finding an example or any other
>related information regarding CASE exp in a proc.
>TIA
>Bill

Hi Bill,

In this case (no pun intended), I'd simply write it like this:

WHERE MyColumn BETWEEN @.parameter AND @.parameter + 9

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:

>Sorry I didn't add that if a request for specfic comment type comes in
>like 22 we only return the type 22's.
>I do like the BETWEEN stmt though I haven't seen that before, I will
>remember that one.
>Thx, Bill

Hi Bill,

Is the request for "a specific comment type" passed in through a seperate
parameter? And the other parameter is used to get a range of comment
types?

CREATE PROC MyProc @.Specific int,
@.RangeStart int
AS
IF (@.Specific IS NULL AND @.RangeStart IS NULL)
OR (@.Specific IS NOT NULL AND @.RangeStart IS NOT NULL)
RAISERROR ('Supply exactly one of the two parameters', 16, 1)
ELSE
IF @.Specific IS NOT NULL
SELECT Column List
FROM YourTable
WHERE MyColumn = @.Specific
ELSE
SELECT Column List
FROM YourTable
WHERE MyColumn BETWEE @.RangeStart AND @.RangeStart + 9
go
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:

>At present it comes as a single request.
(snip)

Hi Bill,

How do you know if the request is for a specific comment type or for a
range? Surely, there has to be some way to distinguish a request for
comment type '20' (meaning just 20) from a request for comment type '20'
(meaning all values 20 through 29).

Maybe this is a good time to explain what information should be included
in newsgroup postings to maximise the chance to get a useful reply:

* Table structure, posted as DDL (CREATE TABLE statements, omitting
irrelevant columns but including all constraints);
* Sample data, posted as INSERT statements (and please verify that the
CREATE TABLE and INSERT statements you post work properly in an empty test
database!);
* Expected output, based on sample data;
* The SQL code you already got (if any), plus the results these give you
and the reason why that is wrong. If you get an error message, copy and
paste the full message;
* A short, concise description of the business problem you're trying to
solve.

Check out these sites as well:
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Case conversion with SQL or Stored Proc

Hi experts,

I m new in SQL stuff. I have to work out a fucntion with ASP.net for CSV import to the DB in MSSQL.

I would like to know for Stored Proc, is there any way that I can do the case conversion?

e.g

the Full_Name read from CSV file: Lennon, John

Then for the family name I need to convert into uppercase so the converted one: LENNON, John

Is there any way I can check those words before the comma? The CSV file is delimited with | instead of , ?

Can I use substring for that? And also do you have any online tutorial for Stored Procedure recommended? Thanks a lot!!!

Cheers,

KNVB

Hi,

Here is an example:
declare @.fullName varchar(100)

set @.fullName = 'Lennon, John'

select upper(left(@.fullName, charindex(',', @.fullName) - 1)) + right(@.fullName, len(@.fullName) - charindex(',', @.fullName) + 1)

Note: The first two lines of code are just for the sample

It might be better to implement this as a FUNCTION in case of a STORED PROCEDURE since functions can be used in your select statement.

References:

Creating stored procedures: http://www.sql-server-performance.com/tn_stored_procedures.asp|||

Merci beaucoup, Geert!

By the way, have you heard of a company called i4net from Namur?

|||

No problem, glad to help.

I didn’t know i4net. Is this your company maybe?

Greetz,

Geert

Cascading Update And Delete

If we want to maintain the data in relationships.

There are two ways to do it.

1. Auto (Like Cascading Update And Delete)

2. Manually (Like In Stored Procedures)

I read an intresting article

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419

In this article Imar has choosen the second way (Manually).

And when I talk to Imar.

He said, "Cascading deletes would have worked equally well in this situation. However, I personally don't like them too much. I am much rather in control, enabling me to delete what I want and when I want it. I could, for example, keep certain data for "time travelling scenarios" (e.g. the state things were in some time ago) or I might want to keep it for other purposes."

Can any one help me to choose the better one.

Waiting for helpful replies.

Well, I can give you my opinion. I preferer cascading deletes to keep the integrity of my data. If it's something that I want to keep I make a structure for that data. Like a log table or some export function to retrieve the state of something.

To rely on stored procedures to maintain the integrity sounds to error prone for me.

|||

I need more views.

cascading parameters

I am a little confused on these cascading parameters.
Can I use them with stored procs and how?
I have one main stored proc and many smaller ones for the parameters.
I seem to be missing where to link them?Cascading parameters means one parameter is dependant on a different
parameter.
Like you have to choose a Country before a drop down list of Cities in that
country gets selectable.
You make cascading parameters by having the query for the second parameter
take the value of the selected item of the first parameter as a parameter.
Pseudocode:
Parameter 1 - Country
Select CountryID, CountryName from Country
- Parameter Value = CountryID, Parameter Label = CountryName
Parameter 2:
Select CityID, CityName from City where CountryID = Parameters!Country.Value
When you connect parameters, you won't be able to choose a city before
you've chosen a country.
You link the second parameter to the first just as you would link a data set
to the parameters.
Kaisa M. Lindahl Lervik
"ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
news:35B6AA90-3871-4DA5-A05D-B1BDD9E8EB33@.microsoft.com...
>I am a little confused on these cascading parameters.
> Can I use them with stored procs and how?
> I have one main stored proc and many smaller ones for the parameters.
> I seem to be missing where to link them?
>|||Thank you for the reply.
I am using stored procs everywhere and having a time trying to link them.
I can get part of the parameter to do what I want it's at execution it fails
to work.
Will try to make my parameters link, currently there is no relationship. If
you select a certain group you get a set of med conditions and they are not
tied to each other on the backend?
Will keep trying though...thx again..it was very helpful.
"Kaisa M. Lindahl Lervik" wrote:
> Cascading parameters means one parameter is dependant on a different
> parameter.
> Like you have to choose a Country before a drop down list of Cities in that
> country gets selectable.
> You make cascading parameters by having the query for the second parameter
> take the value of the selected item of the first parameter as a parameter.
> Pseudocode:
> Parameter 1 - Country
> Select CountryID, CountryName from Country
> - Parameter Value = CountryID, Parameter Label = CountryName
> Parameter 2:
> Select CityID, CityName from City where CountryID = Parameters!Country.Value
> When you connect parameters, you won't be able to choose a city before
> you've chosen a country.
> You link the second parameter to the first just as you would link a data set
> to the parameters.
> Kaisa M. Lindahl Lervik
>
> "ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
> news:35B6AA90-3871-4DA5-A05D-B1BDD9E8EB33@.microsoft.com...
> >I am a little confused on these cascading parameters.
> > Can I use them with stored procs and how?
> > I have one main stored proc and many smaller ones for the parameters.
> > I seem to be missing where to link them?
> >
>
>|||I have something working but what can I set the true property to?
=IIF(left(Parameters!MonGrp.Value,2) = "BB", Nothing,"exec p_GetConditions")
causing an error: ExecuteReader: CommandText property has not been initialized
This is exciting help pls on this last endeavor.
"ATB4U" wrote:
> Thank you for the reply.
> I am using stored procs everywhere and having a time trying to link them.
> I can get part of the parameter to do what I want it's at execution it fails
> to work.
> Will try to make my parameters link, currently there is no relationship. If
> you select a certain group you get a set of med conditions and they are not
> tied to each other on the backend?
> Will keep trying though...thx again..it was very helpful.
> "Kaisa M. Lindahl Lervik" wrote:
> > Cascading parameters means one parameter is dependant on a different
> > parameter.
> > Like you have to choose a Country before a drop down list of Cities in that
> > country gets selectable.
> > You make cascading parameters by having the query for the second parameter
> > take the value of the selected item of the first parameter as a parameter.
> >
> > Pseudocode:
> >
> > Parameter 1 - Country
> > Select CountryID, CountryName from Country
> > - Parameter Value = CountryID, Parameter Label = CountryName
> >
> > Parameter 2:
> > Select CityID, CityName from City where CountryID = Parameters!Country.Value
> >
> > When you connect parameters, you won't be able to choose a city before
> > you've chosen a country.
> > You link the second parameter to the first just as you would link a data set
> > to the parameters.
> >
> > Kaisa M. Lindahl Lervik
> >
> >
> > "ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
> > news:35B6AA90-3871-4DA5-A05D-B1BDD9E8EB33@.microsoft.com...
> > >I am a little confused on these cascading parameters.
> > > Can I use them with stored procs and how?
> > > I have one main stored proc and many smaller ones for the parameters.
> > > I seem to be missing where to link them?
> > >
> >
> >
> >|||You can only cascade (link) parameters that have a relation. That's sort of
the point. What you choose as the value in the first parameter, get sent to
the query for the next parameter as a parameter.
Microsoft has written a tutorial on "Adding cascading parameters to a
report" at
http://msdn2.microsoft.com/en-us/library/aa337426.aspx
You might want to read it to get a clear picture on what it is and how to
use it in your report. :)
Kaisa M. Lindahl Lervik
"ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
news:6FCEEC06-0591-4C4F-B10C-360DEE2B21CD@.microsoft.com...
>I have something working but what can I set the true property to?
> =IIF(left(Parameters!MonGrp.Value,2) = "BB", Nothing,"exec
> p_GetConditions")
> causing an error: ExecuteReader: CommandText property has not been
> initialized
> This is exciting help pls on this last endeavor.
> "ATB4U" wrote:
>> Thank you for the reply.
>> I am using stored procs everywhere and having a time trying to link them.
>> I can get part of the parameter to do what I want it's at execution it
>> fails
>> to work.
>> Will try to make my parameters link, currently there is no relationship.
>> If
>> you select a certain group you get a set of med conditions and they are
>> not
>> tied to each other on the backend?
>> Will keep trying though...thx again..it was very helpful.
>> "Kaisa M. Lindahl Lervik" wrote:
>> > Cascading parameters means one parameter is dependant on a different
>> > parameter.
>> > Like you have to choose a Country before a drop down list of Cities in
>> > that
>> > country gets selectable.
>> > You make cascading parameters by having the query for the second
>> > parameter
>> > take the value of the selected item of the first parameter as a
>> > parameter.
>> >
>> > Pseudocode:
>> >
>> > Parameter 1 - Country
>> > Select CountryID, CountryName from Country
>> > - Parameter Value = CountryID, Parameter Label = CountryName
>> >
>> > Parameter 2:
>> > Select CityID, CityName from City where CountryID =>> > Parameters!Country.Value
>> >
>> > When you connect parameters, you won't be able to choose a city before
>> > you've chosen a country.
>> > You link the second parameter to the first just as you would link a
>> > data set
>> > to the parameters.
>> >
>> > Kaisa M. Lindahl Lervik
>> >
>> >
>> > "ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
>> > news:35B6AA90-3871-4DA5-A05D-B1BDD9E8EB33@.microsoft.com...
>> > >I am a little confused on these cascading parameters.
>> > > Can I use them with stored procs and how?
>> > > I have one main stored proc and many smaller ones for the parameters.
>> > > I seem to be missing where to link them?
>> > >
>> >
>> >
>> >|||Here's the great thing!
I got this to work, conditional data set.
=IIF(left(Parameters!MonGrp.Value,2) = "BB",Nothing,"exec p_GetConditions")
What I am looking for now if the user selects BB, I need to hide the
condition parameter...something in the "Nothing" piece that diables the
condition parameter.
Thank you for all your help, but I have to get this b/c it will be needed
throuhout all of my reports.
"Kaisa M. Lindahl Lervik" wrote:
> You can only cascade (link) parameters that have a relation. That's sort of
> the point. What you choose as the value in the first parameter, get sent to
> the query for the next parameter as a parameter.
> Microsoft has written a tutorial on "Adding cascading parameters to a
> report" at
> http://msdn2.microsoft.com/en-us/library/aa337426.aspx
> You might want to read it to get a clear picture on what it is and how to
> use it in your report. :)
> Kaisa M. Lindahl Lervik
>
> "ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
> news:6FCEEC06-0591-4C4F-B10C-360DEE2B21CD@.microsoft.com...
> >I have something working but what can I set the true property to?
> > =IIF(left(Parameters!MonGrp.Value,2) = "BB", Nothing,"exec
> > p_GetConditions")
> > causing an error: ExecuteReader: CommandText property has not been
> > initialized
> > This is exciting help pls on this last endeavor.
> >
> > "ATB4U" wrote:
> >
> >> Thank you for the reply.
> >> I am using stored procs everywhere and having a time trying to link them.
> >> I can get part of the parameter to do what I want it's at execution it
> >> fails
> >> to work.
> >> Will try to make my parameters link, currently there is no relationship.
> >> If
> >> you select a certain group you get a set of med conditions and they are
> >> not
> >> tied to each other on the backend?
> >> Will keep trying though...thx again..it was very helpful.
> >>
> >> "Kaisa M. Lindahl Lervik" wrote:
> >>
> >> > Cascading parameters means one parameter is dependant on a different
> >> > parameter.
> >> > Like you have to choose a Country before a drop down list of Cities in
> >> > that
> >> > country gets selectable.
> >> > You make cascading parameters by having the query for the second
> >> > parameter
> >> > take the value of the selected item of the first parameter as a
> >> > parameter.
> >> >
> >> > Pseudocode:
> >> >
> >> > Parameter 1 - Country
> >> > Select CountryID, CountryName from Country
> >> > - Parameter Value = CountryID, Parameter Label = CountryName
> >> >
> >> > Parameter 2:
> >> > Select CityID, CityName from City where CountryID => >> > Parameters!Country.Value
> >> >
> >> > When you connect parameters, you won't be able to choose a city before
> >> > you've chosen a country.
> >> > You link the second parameter to the first just as you would link a
> >> > data set
> >> > to the parameters.
> >> >
> >> > Kaisa M. Lindahl Lervik
> >> >
> >> >
> >> > "ATB4U" <ATB4U@.discussions.microsoft.com> wrote in message
> >> > news:35B6AA90-3871-4DA5-A05D-B1BDD9E8EB33@.microsoft.com...
> >> > >I am a little confused on these cascading parameters.
> >> > > Can I use them with stored procs and how?
> >> > > I have one main stored proc and many smaller ones for the parameters.
> >> > > I seem to be missing where to link them?
> >> > >
> >> >
> >> >
> >> >
>
>

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

Carrage Returns, Stored Procedures

Question:
What would be the best way to add carrage returns to a record, and would my
method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.
Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lastupdate
UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));
Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.
I would like to create a stored procedure that will append the current Date
and Time to each update that is being submitted via a web form. I only want
the web form to add text and have the stored procedure append the input text
the the existing record so that the data looks like the following. and have
it also update the field [lastupdate]
10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.
10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.
10/4/2003 6:27:08 AM
Backup failed.
Thank You,
DaveAssuming it is a VARCHAR(8000),
CREATE PROCEDURE dbo.addDetails
(
@.mitTicketID INT,
@.detail VARCHAR(255)
)
AS
BEGIN
DECLARE @.append VARCHAR(512)
SET @.append = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ CONVERT(CHAR(10), GETDATE(), 101) + ' '
+ STUFF(RIGHT(CONVERT(VARCHAR(255), GETDATE(), 109), 14), 9, 4, ' ')
+ CHAR(13) + CHAR(10) + @.detail
UPDATE mitTickets
SET
details = details + @.append,
lastUpdate = GETDATE()
WHERE ID = @.mitTicketID
END
GO
You can change all that formatting to this, if you're happy with MMM DD
YYYY HH:MMAM format:
CONVERT(VARCHAR(26), GETDATE())
I don't know what you mean by overhead and wasted space; if you want a
carriage return in the column, there isn't a more efficient way than putting
a carriage return in the column. Not sure what kind of magic you might be
expecting there?
In any case, I think this should be redesigned. Why do you want to store
all that detail in a huge column? That column almost looks like a table.
Why don't you store detail rows in a relational table... it will make the
handling of adding rows much easier, it will make reporting easier, it will
certainly make *removal* of older data easier, it will avoid the problems
you'll have when you have to make it TEXT because all of your data reaches
8000 characters at some point, and it will completely eliminate the
redundancy of having a lastupdate column.
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lastupdate
> UPDATE [mitTickets] SET [mitTickets].details= Now() &
>
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
> n], [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following. and
have
> it also update the field [lastupdate]
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.
>
> Thank You,
> Dave
>|||Hi
I am not sure why you wish to add this to the database as a single field.
What would happend if you wanted to query the data e.g all activity on a
certain day?
It would be better to add the carriage returned on output, either when
selecting the data or probably more preferably through the user interface.
You can use the getdate() function to get the current system date and time.
John
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lastupdate
> UPDATE [mitTickets] SET [mitTickets].details= Now() &
>
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
> n], [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following. and
have
> it also update the field [lastupdate]
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.
>
> Thank You,
> Dave
>

Carrage Returns, Stored Procedures

Question:
What would be the best way to add carrage returns to a record, and would my
method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.

Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lupdate

Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.

I would like to create a stored procedure that will append the current Date
and Time to each update that is being submitted via a web form. I only want
the web form to add text and have the stored procedure append the input text
the the existing record so that the data looks like the following.

10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.

10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.

10/4/2003 6:27:08 AM
Backup failed.Sorry, I meant to post my Stored Procedure

UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));

"new" <dduryea@.inetmicro.com> wrote in message
news:sXWfb.50789$nU6.8336240@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lupdate
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following.
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.|||new (dduryea@.inetmicro.com) writes:
> Sorry, I meant to post my Stored Procedure
> UPDATE [mitTickets]
> SET [mitTickets].details = Now() & Chr(13) + Chr(10) + [@.detailstxtbox] +
> Chr(13) + Chr(10) + Chr(13) + Chr(10) +
> [description],
> [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));

The CRLF are alright, but there are a coupld of other errors:

o There is no Now() in SQL Server. Use
convert(varchar(20), getdate, 121) to get the date.
o & is an operator for bitwise and. You want + for string concatenation.
o [@.detailslistbox] will resolve to a column with the name
@.detailslistbox. If you want refer to a variable, remove the brackets.
o While legal here, it is best to leave out the table name on the left-
hand side of the SET clause. You can only update the columns of one
table at a time, so the name is redundant here.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

Use a trigger like the following to do this.

CREATE TRIGGER mitTickets_inserted_time ON mitTickets
FOR insert
AS UPDATE mitTickets
SET lupdate= GETDATE()
WHERE problem in (SELECT problem FROM INSERTED)

That will take care of it.

Regards,
-Manoj

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
>