Help with update forms: varchar

Showing posts with label varchar. Show all posts

Monday, March 26, 2012

HELP! Stored Procedure problem

I have the following stored procedure:

CREATE procedure sp_BA_BuildTablesStep2

(
@.BACKENDDATEVALUE varchar(10),
@.QUERYSTATMENT varchar(100)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_BACK') is not null drop table BA_BACK

SELECT BA_NEW.SRC_CODE,
Count(RG_BA_SHIPPED_ORDERS.ORDER_NBR) AS SUBSEQUENT_ORDERS,
Sum(RG_BA_SHIPPED_ORDERS.TOTAL_AMT) AS INVOICE_TOTAL,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_CHARGE) AS SHIPPING_CHARGE,
Sum(RG_BA_SHIPPED_ORDERS.SHIP_COST) AS SHIPPING_COST,
Sum(RG_BA_SHIPPED_ORDERS.MARGIN) AS MARGIN

INTO BA_BACK

FROM BA_NEW INNER JOIN RG_BA_SHIPPED_ORDERS ON BA_NEW.CUST_NBR = RG_BA_SHIPPED_ORDERS.CUST_NBR

WHERE (RG_BA_SHIPPED_ORDERS.DATE > [BA_NEW].[MaxOFDATE]) And (RG_BA_SHIPPED_ORDERS.DATE < = " + @.BACKENDDATEVALUE+ ") " + @.QUERYSTATMENT +"

GROUP BY BA_NEW.SRC_CODE"

EXEC(@.SQL)

GO

When I run it I get 0 rows affected, when I run the query in QA with my values plugged in I get the results I want... can you see anything I did wrong?

@.BACKENDDATEVALUE is a date supplied as '02/18/03' and @.QUERYSTATMENT is like: 'and RG_BA_SHIPPED_ORDERS.CO_TYPE =1'

I'm changeing @.QUERYSTATMENT by adding "OR" statements at the end depending on what the user chooses in the form.

Any ideas?

KenFigured out the problem! I wasn't passing the vars correctly!

Thanks much!

Ken

HELP! Stored Procedure problem

Monday, March 12, 2012

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegroups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
---
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
---
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegroups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> ---
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> ---
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>|||Thanks Tibor! That was helpful.

HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?
Separate presentation from data. SQL Server returns data values and the value 0 is the same as the
value 0.00. The client application presents the binary values returned by SQL Server in a
human-readable form (like number with decimal points). If you didn't write the client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Services, etc, then these
tools will present the values according to the datatype *for the column'. I.e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the person writing the too
who makes these decision, but it applies to all values for the column. The only way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to handle this in the client
app. All client app designed for end users has functionality for formatting data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegro ups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>
|||Thanks Tibor.
Unfortunately, handling this at the UI end is not an option. I'm
converting an app (from Access). I need the data to be exactly the
same. I unable to change the UI at this time.
|||Plus, I would like to know why SQL Server is rounding that value. To
me this looks like a defect. It should not be rounding the value.
|||I'm not sure I understand your comments about rounding. Let's work with executable code instead.
Can, you, based on below, describe when you want:
CREATE TABLE y(c1 float)
INSERT INTO y (c1) VALUES (NULL)
INSERT INTO y (c1) VALUES (123.45)
INSERT INTO y (c1) VALUES (555.70)
INSERT INTO y (c1) VALUES (35444.21)
INSERT INTO y (c1) VALUES (35444.79)
SELECT
CASE WHEN c1 IS NULL THEN 0 ELSE c1 END
FROM y
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174942335.112146.304030@.n59g2000hsh.googlegr oups.com...
> Plus, I would like to know why SQL Server is rounding that value. To
> me this looks like a defect. It should not be rounding the value.
>
|||The problem is in the last two writes in the example I gave.
Hopefully this explains it more...Run this code to create table Z.
Run this:
CREATE TABLE z(c1 varchar(15))
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
END FROM y
Here is what ends up in z for me:
0
123.45
555.7
35444.2
35444.8
The last two records get rounded to one decimal place. Why does that
happen? When I cast it as money, the values do not round:
INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
CAST(y.c1 AS Money) END FROM y
0.00
123.45
555.70
35444.21
35444.79
But now the '0' is '0.00'. Which is NOT what I want.
|||I really don't know why SQL Server would cast the float values that way. In general, I avoid float
being what sometime is called an approximate datatype. So, it seems that when SQL Server converts
from float to varchar, you see some funny things. I would suggest you cast to something else, like
decimal And if you *really* want to do presentation logic in the engine, you can convert NULL to 0,
cast to varchar, then replace '0.00' with 0. See below:
INSERT INTO z ([c1])
SELECT CASE WHEN c = '0.00' THEN '0' ELSE c END
FROM
(
SELECT
CAST(
CAST(
ISNULL(y.c1, 0)
AS decimal (9,2))
AS varchar(15)) AS c
FROM y
) AS inr
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1175032433.780317.287070@.d57g2000hsg.googlegr oups.com...
> The problem is in the last two writes in the example I gave.
> Hopefully this explains it more...Run this code to create table Z.
> Run this:
> CREATE TABLE z(c1 varchar(15))
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE y.c1
> END FROM y
> Here is what ends up in z for me:
> 0
> 123.45
> 555.7
> 35444.2
> 35444.8
> The last two records get rounded to one decimal place. Why does that
> happen? When I cast it as money, the values do not round:
> INSERT INTO z ([c1]) SELECT CASE WHEN y.c1 IS NULL THEN '0' ELSE
> CAST(y.c1 AS Money) END FROM y
> 0.00
> 123.45
> 555.70
> 35444.21
> 35444.79
> But now the '0' is '0.00'. Which is NOT what I want.
>
|||Thanks Tibor! That was helpful.

Wednesday, March 7, 2012

HELP! Concatentation of 2 INT columns in SQL 2000

I am looking to do a concatentation of 2 integer columns without converting them to varchar in SQL Server 2000. If you do a concatentation operator(+) on two integer columns it will try to do math instead of concatenation. Does anyone know how to do this without doing a cast or convert statement? I am asking due to performance issues.
Here is the statement that I am trying to change:

select
cast(a.so_id as varchar ) + cast(a.line as varchar) cust_db_shipment_key,
MULTIPLE OTHER PARTS OF THE STATEMENT

from SO_LINE a left join SO b on a.SO_ID = b.SO_ID where carrier_id = '2' and cast(a.so_id as varchar) + cast(a.line as varchar) = ?

Thanks for the help in advance!!!!!!!!
SteveThere isn't too much of a performance hit for doing this in the SELECT. That FROM will kill you though. I would make it:
SELECT
CAST(a.so_id AS VARCHAR) + CAST(a.line AS VARCHAR) AS cust_db_shipment_key,
blah,
blah,
blah
FROM
SO_LINE a
LEFT JOIN SO b ON a.SO_ID = b.SO_ID
WHERE
carrier_id = '2'
AND so_id = LEFT(@.whatever,2) --However the string is broken down.
AND a.line = RIGHT(@.whatever,2)|||OK...I have come to the conclusion that I will not be keeping the columns as int's. The (@.whatever, 2)...what is that representing?
Thanks,
Steve|||?? Whatever the "?" is in your post. I'm assuming your passing a string or something to this query aren't you?|||This works great so far. I am working off of a limited dataset so tomorrow will be the real test but so far so good.
Thanks again,
Steve

Friday, February 24, 2012

Help with WHERE Clause in Stored Procedure

Hi,

I have an sp with the following WHERE clause

@.myqarep varchar(50)

SELECT tblCase.qarep FROM dbo.tblCase

WHERE dbo.tblCase.qarep = CASE @.myqarep WHEN '<All>' THEN
dbo.tblCase.qarep ELSE @.myqarep

@.myqarep is returned from a combo box (ms access)...the user either
picks a qarep from the combo box or they leave the default which is
'<All>'

they problem i'm having is that if the record's value for
dbo.tblCase.qarep is null...the record does not show up in the
results...but i need it to

any help is appreciated.

thanks
Paul... WHERE qarep = @.myqarep
OR @.myqarep = '<All>'

--
David Portas
SQL Server MVP
--|||thanks for the quick response...i'll give it a try!

David Portas wrote:
> ... WHERE qarep = @.myqarep
> OR @.myqarep = '<All>'
> --
> David Portas
> SQL Server MVP
> --

Sunday, February 19, 2012

Help with varchar to date

I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived form the
column above. So I tried the following
CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
RETURNS smalldatetime
AS
BEGIN
--want to be sure it is interpreted as dd-mm-yy format
RETURN CONVERT(smalldatetime,
'01-' +
CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+ '-' + Right(@.DATETEXT, 2))
END
I then try to execute the following:
UPDATE Credit_Card
SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
WHERE LEN(Expiry_Date) = 5 --jic a bad field
And get the following error:
Syntax error converting character string to smalldatetime data type
The function does return correctly. Can anyone give me some help in getting
this working. ThanksCan you show a simplified example, e.g. your table structure, and 3 or 4
rows of sample data that cause the failure.
This is just one of the dozens of problems with choosing the wrong data
type. Another big one with your function specifically:
You're checking for left(@.datetext,2) but then saying WHEN 1 -- two problems
here, one is that 1 is not a string ('1' would be) and unless it is november
or december, I am sure that Left(@.DateText, 2) yields two characters (only
one of which is the month). How on earth do you distinguish between 11206
(jan 12 06) and 11206 (nov 2 06)? How about 1006 (nov 06) vs. 106 (jan 06)?
Again, some sample data that causes the problem would be useful. But more
importantly, before trying to debug a function, get a query running that
does what you want (but without the convert to smalldatetime). That makes
it much easier to debug and figure out which rows are not producing valid
dates.
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||Harry
> --want to be sure it is interpreted as dd-mm-yy format
Make sure that the format you are converting to is YYYYMMDD
CREATE TABLE Credit_Card (dt VARCHAR(5))
INSERT INTO Credit_Card SELECT '10/06'
SELECT * FROM Credit_Card
UPDATE Credit_Card SET dt= CAST(CONVERT(CHAR(6),GETDATE(),112)+'01'
AS
DATETIME)
It retruns just 'June'
How about to alter the table and change the datatype's column or expand it
to varchar(50) for instance
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Can you show a simplified example, e.g. your table structure, and 3 or 4
> rows of sample data that cause the failure.
> This is just one of the dozens of problems with choosing the wrong data
> type. Another big one with your function specifically:
> You're checking for left(@.datetext,2) but then saying WHEN 1 -- two
> problems
Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
'01'
The function DOES work correctly and returns from a parameter of '06/06'
the result '2006-06-01 00:00:00'
TRY the function!

> here, one is that 1 is not a string ('1' would be) and unless it is
> november or december, I am sure that Left(@.DateText, 2) yields two
> characters (only one of which is the month). How on earth do you
> distinguish between 11206 (jan 12 06) and 11206 (nov 2 06)? How about
> 1006 (nov 06) vs. 106 (jan 06)?
READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
I am prepending '01-' for the day in the function.
The reason I am converting the LEFT 2 characters to a tinyint for use in the
CASE statement
e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
in dd-MMM-yy format
so the convert function will not be

between an Australian date
format and a US format.

> Again, some sample data that causes the problem would be useful. But more
> importantly, before trying to debug a function, get a query running that
> does what you want (but without the convert to smalldatetime). That makes
> it much easier to debug and figure out which rows are not producing valid
> dates.
AGAIN, the function works - READ what I have written. Further to this I have
given you sample data!
"I want to convert a varchar(5) field to smalldatetime. I have a column that
hold credit card expiry dates in mm/yy format e.g 10/06
I want to update a new column with a smalldatetime value derived from the
column above. So I tried the following"
PLEASE read and understand the question.

>
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>|||"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:qComg.3523$b6.86616@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
>hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
> END
> I then try to execute the following:
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
> And get the following error:
> Syntax error converting character string to smalldatetime data type
> The function does return correctly. Can anyone give me some help in
> getting this working. Thanks
Perhaps I had better explain further:
I have a table with a number of columns, one of which is called
"Expiry_Date" - varchar(5) which stores string values in the format mm/yy
e.g '06/06' or '12/06' as we all see as the expiry date on a credit card. I
now need to know in advance if a crediy card is going to expire. The current
format makes that very difficult. So I am trying the following:
I have added another column called "CardExpiry" which is smalldatetime. I
want to update this column from values contained in the "Expiry_Date"
column. Obviously I have to convert the string value to a smalldatetime
value first. That is why I created the function above. Even though the
function returns a smalldatetime value, sql server (2000) still thinks the
output of the function is a character string.|||> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
CREATE TABLE Credit_Card (Expiry_Date VARCHAR(5),CardExpiry SMALLDATETIME)
INSERT INTO Credit_Card (Expiry_Date) SELECT '10/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '09/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '01/06'
INSERT INTO Credit_Card (Expiry_Date) SELECT '02/06'
SELECT * FROM Credit_Card
--Now we are going to update CardExpiry column
UPDATE Credit_Card SET CardExpiry= CAST('20'+RIGHT(Expiry_Date,2)+ LEFT
(Expiry_Date,2) +'01' AS SMALLDATETIME)
SELECT * FROM Credit_Card
DROP TABLE Credit_Card
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:1Jqmg.14185$ap3.3358@.news-server.bigpond.net.au...
> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
> I now need to know in advance if a crediy card is going to expire. The
> current format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>|||This alteration to your function shuold clear up the error you are receiving
.
It returns a alpha month, 4 digit year, and the first of the month. Bad and
non-confirming parameters will return NULL. You can then easily find the bad
data.
CREATE FUNCTION dbo.StringToDate
( @.DateText varchar(11) )
RETURNS datetime
AS
BEGIN
IF len( @.DateText ) < 5
RETURN NULL
SET @.DateText =
CASE left( @.DateText, 2 )
WHEN '01' THEN replace( @.DateText, '01/', '01/Jan/' )
WHEN '02' THEN replace( @.DateText, '02/', '01/Feb/' )
WHEN '03' THEN replace( @.DateText, '03/', '01/Mar/' )
WHEN '04' THEN replace( @.DateText, '04/', '01/Apr/' )
WHEN '05' THEN replace( @.DateText, '05/', '01/May/' )
WHEN '06' THEN replace( @.DateText, '06/', '01/Jun/' )
WHEN '07' THEN replace( @.DateText, '07/', '01/Jul/' )
WHEN '08' THEN replace( @.DateText, '08/', '01/Aug/' )
WHEN '09' THEN replace( @.DateText, '09/', '01/Sep/' )
WHEN '10' THEN replace( @.DateText, '10/', '01/Oct/' )
WHEN '11' THEN replace( @.DateText, '11/', '01/Nov/' )
WHEN '12' THEN replace( @.DateText, '12/', '01/Dec/' )
END
IF @.DateText LIKE '%/0%'
RETURN ( replace( @.DateText, '/0', '/200' ))
IF @.DateText LIKE '%/1%'
RETURN ( replace( @.DateText, '/1', '/201' ))
RETURN NULL
END
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message news:qComg.3523$b6.86616
@.nasal.pacific.net.au...
>I want to convert a varchar(5) field to smalldatetime. I have a column that
> hold credit card expiry dates in mm/yy format e.g 10/06
>
> I want to update a new column with a smalldatetime value derived form the
> column above. So I tried the following
>
> CREATE FUNCTION [dbo].[StringToDate] (@.DATETEXT varchar(5))
> RETURNS smalldatetime
> AS
> BEGIN
> --want to be sure it is interpreted as dd-mm-yy format
> RETURN CONVERT(smalldatetime,
> '01-' +
> CASE CONVERT(tinyint, Left(@.DATETEXT, 2))
> WHEN 1 THEN 'Jan'
> WHEN 2 THEN 'Feb'
> WHEN 3 THEN 'Mar'
> WHEN 4 THEN 'Apr'
> WHEN 5 THEN 'May'
> WHEN 6 THEN 'Jun'
> WHEN 7 THEN 'Jul'
> WHEN 8 THEN 'Aug'
> WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov'
> WHEN 12 THEN 'Dec'
> END
> + '-' + Right(@.DATETEXT, 2))
>
> END
>
> I then try to execute the following:
>
> UPDATE Credit_Card
> SET Credit_Card.CardExpiry = dbo.StringToDate(Expiry_Date)
> WHERE LEN(Expiry_Date) = 5 --jic a bad field
>
> And get the following error:
>
> Syntax error converting character string to smalldatetime data type
>
> The function does return correctly. Can anyone give me some help in gettin
g
> this working. Thanks
>
>|||Apparently the function does NOT work -you are getting an error!
I'm

now.
Does the function NOT work properly and you are asking for our help,
OR
Does the function work properly and you are posting here for -what was that
reason again?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Still Love VB6" <harry@.nospam.com.au> wrote in message
news:Svqmg.14176$ap3.2372@.news-server.bigpond.net.au...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:O9Um9QblGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Have a look again--CONVERT(tinyint, Left(@.DATETEXT, 2)) returns 1 from
> '01'
> The function DOES work correctly and returns from a parameter of '06/06'
> the result '2006-06-01 00:00:00'
> TRY the function!
>
> READ again -- I am passing a string value with a mm/yy format e.g. '10/06'
> I am prepending '01-' for the day in the function.
> The reason I am converting the LEFT 2 characters to a tinyint for use in
> the CASE statement
> e.g. '01' becomes 1, '02' becomes 2 etc the reason being I want the string
> in dd-MMM-yy format
> so the convert function will not be

between an Australian date
> format and a US format.
>
> AGAIN, the function works - READ what I have written. Further to this I
> have given you sample data!
> "I want to convert a varchar(5) field to smalldatetime. I have a column
> that
> hold credit card expiry dates in mm/yy format e.g 10/06
> I want to update a new column with a smalldatetime value derived from the
> column above. So I tried the following"
> PLEASE read and understand the question.
>
>|||Could it be that you have bad data in your table, for example an expiry date
of 13/06?
Chris
"Still Love VB6" wrote:

> "Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
> news:qComg.3523$b6.86616@.nasal.pacific.net.au...
> Perhaps I had better explain further:
> I have a table with a number of columns, one of which is called
> "Expiry_Date" - varchar(5) which stores string values in the format mm/yy
> e.g '06/06' or '12/06' as we all see as the expiry date on a credit card.
I
> now need to know in advance if a crediy card is going to expire. The curre
nt
> format makes that very difficult. So I am trying the following:
> I have added another column called "CardExpiry" which is smalldatetime. I
> want to update this column from values contained in the "Expiry_Date"
> column. Obviously I have to convert the string value to a smalldatetime
> value first. That is why I created the function above. Even though the
> function returns a smalldatetime value, sql server (2000) still thinks the
> output of the function is a character string.
>
>|||> Perhaps I had better explain further:
Yes, that would be a good start!

> Even though the function returns a smalldatetime value, sql server (2000)
> still thinks the output of the function is a character string.
No, that is not what is happening at all.
You have some "dates" in your table where they aren't really dates. I can
think of hundreds of examples, since you allow varchar(5) in there, there is
no easy way to make them conform to any date format, so your table is
probably full of crap. It may be one row that is causing your function to
fail; it may be all rows! Who knows?
Do you see, now, the importance of sample data!?

Help with user defined function

I have the following function:
create function dbo.AttributesList (@.Customerno varchar(10),
@.sold_to_sales_grp varchar(3), @.attr varchar(2))
returns varchar(15)
as
begin
declare @.sOut varchar(15)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '3'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
-- previous
if @.attr = '4'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from product1_channels_prev x
where x.cust_no = @.Customerno and x.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '5'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from product2_channels_prev y
where y.cust_no = @.Customerno and y.sold_to_sales_grp =
@.sold_to_sales_grp
end
if @.attr = '6'
begin
-- attribute table 3
-- attribute table 3
select @.sOut = @.sOut + ', ' + z.distr_channel
from product3_channels_prev z
where z.cust_no = @.Customerno and z.sold_to_sales_grp =
@.sold_to_sales_grp
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
which you call with a customer number, a sales group, and an attribute.
The tables it queries are built in a job that gets run overnight.
The results of the query are supposed to string together a distribution
channel if there are more than 1 returned.
product1 could return CH
product2 could return CH, DL
product3 could return nothing
The function as it is works properly. I get the anticipated results
when I run a query that calls this user function.
However, I want to write an online app that will allow the user to
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
The problem is - I can't make it work all the time.
If I put in the where clause:
'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
on for each of the 6 possibilities, it'll return ones with a
Distribution channel = DL but only ones which START with a DL. If DL is
the second one in the list, it doesn't return anything.
I have also tried this in the HAVING clause, to see if I can get it to
come out there but can't.
How can I take the distribution channel (in this case DL) and return
records where DL is in any of the 6 product groups?
Any ideas/suggestions appreciated. I'm stumped.
BCBlasting Cap (goober@.christian.net) writes:
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from product1_channels x
> where x.cust_no = @.Customerno and
>...
> The function as it is works properly. I get the anticipated results
> when I run a query that calls this user function.
> However, I want to write an online app that will allow the user to
> select a channel from a dropdown box, and the query will then return any
> data with that distribution channel anywhere in the current or
> previous year's product areas.
> The problem is - I can't make it work all the time.
This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right.
If you are on SQL 2005, there is built-in syntax for this thanks to
the FOR XML construct, here demonstrated by a sample query that I
keep around:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
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|||<<This is because the correct behaviour of this query is undefined. See
http://support.microsoft.com/default.aspx?scid=287515.
If you are on SQL 2000, you will need to run a cursor to get this
right. >>
I am using SQL 2000.
The place I'm running the report is just a straight select, with the
functions being called for 6 columns being returned.
select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region
having
-- if they have bought things in any of the last 3 years
(len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
How would you work a cursor into that select?
DECLARE @.AuthorID char(11)
DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.AuthorID
FETCH NEXT FROM c1
INTO @.AuthorID
END
CLOSE c1
DEALLOCATE c1
Also - is there a more efficient way to do this?
Thanks,
BC

> Blasting Cap (goober@.christian.net) writes:
when I run a query that calls this user function.
select a channel from a dropdown box, and the query will then return any
data with that distribution channel anywhere in the current or
previous year's product areas.
> This is because the correct behaviour of this query is undefined. See
> http://support.microsoft.com/default.aspx?scid=287515.
> If you are on SQL 2000, you will need to run a cursor to get this
> right.
> If you are on SQL 2005, there is built-in syntax for this thanks to
> the FOR XML construct, here demonstrated by a sample query that I
keep around:
> select CustomerID,
> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
> -- strip the last ',' from the list
> from
> Customers c cross apply
> (select convert(nvarchar(30), OrderID) + ',' as [text()]
> from Orders o
> where o.CustomerID = c.CustomerID
> order by o.OrderID
> for xml path('')) as Dummy(OrdIdList)
> go
>
>|||On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:

>I have the following function:
(snip)
Erland is correct - the UDF depends on undocumented behaviour. Even
though it works today, it might break tomorrow.
But Erland apparently missed the question you asked near the end of your
post:

>If I put in the where clause:
>'DL' in (dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) and so
>on for each of the 6 possibilities, it'll return ones with a
>Distribution channel = DL but only ones which START with a DL. If DL is
>the second one in the list, it doesn't return anything.
And yet, I'll let Erland answer that, since he has authored an excellent
page about this problem, and lots of possible solutions:
http://www.sommarskog.se/arrays-in-sql.html
However, in your case there might be a better way. Instead of first
using undocumented techniques to get a comma-seperated list and then
using a second technique to split those to tables, why not rewrite the
AttributesList to a table-valued function?
Hugo Kornelis, SQL Server MVP|||Blasting Cap (goober@.christian.net) writes:
> The place I'm running the report is just a straight select, with the
> functions being called for 6 columns being returned.
>...
> How would you work a cursor into that select?
In your UDF.

> Also - is there a more efficient way to do this?
Yes. Two options:
1) Upgrade to SQL 2005.
2) Do it client-side.
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|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> On Thu, 25 May 2006 16:33:30 -0400, Blasting Cap wrote:
> And yet, I'll let Erland answer that, since he has authored an excellent
> page about this problem, and lots of possible solutions:
> http://www.sommarskog.se/arrays-in-sql.html
> However, in your case there might be a better way. Instead of first
> using undocumented techniques to get a comma-seperated list and then
> using a second technique to split those to tables, why not rewrite the
> AttributesList to a table-valued function?
For the WHERE clause why not simply use an EXISTS against the underlying
table:
WHERE EXISTS (SELECT *
FROM product1_channels p
WHERE p.cust_no = a.cust_no
AND p.distr_channel = 'DL')
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|||Blasting Cap (goober@.christian.net) writes:
> select
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region,
> 'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
> 'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
> 'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
> 'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
> 'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
> 'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
> from sales_customer_list as a
> where cust_no in ('R1411600','R6713155')
> group by
> a.cust_no,
> a.cust_name,
> a.sold_to_sales_grp,
> a.ship_to_sales_grp,
> a.sold_to_sales_rep_cd,
> a.ship_to_sales_rep_cd,
> a.csr,
> a.credit_mgr,
> a.sales_region
> having
> -- if they have bought things in any of the last 3 years
> (len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5')) > 0 or
> len(dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')) > 0 )
> order by
> a.ship_to_sales_grp,
> a.ship_to_sales_rep_cd,
> cust_name
It seems unnecessary to call the UDFs a second time in the HAVING clause.
I'm a little uncertain of the effect of the GROUP BY in this SELECT as
there is no aggregate functions, but assuming that it works the way you
want I retain them. However you could use a derived table:
SELECT cust_no, cust_name, ...
FROM (select
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region,
'Prod1_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '1'),
'Prod2_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '2'),
'Prod3_curr' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '3'),
'Prod1_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '4'),
'Prod2_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '5'),
'Prod3_prev' = dbo.AttributesList(a.cust_no, a.sold_to_sales_grp, '6')
from sales_customer_list as a
where cust_no in ('R1411600','R6713155')
group by
a.cust_no,
a.cust_name,
a.sold_to_sales_grp,
a.ship_to_sales_grp,
a.sold_to_sales_rep_cd,
a.ship_to_sales_rep_cd,
a.csr,
a.credit_mgr,
a.sales_region) AS x
WHERE len(Prod1_curr) > 0 OR
len(Prod2_curr) > 0 OR
len(Prod3_curr) > 0 OR
len(Prod1_prev) > 0 OR
len(Prod2_prev) > 0 OR
len(Prod3_prev) > 0
order by
a.ship_to_sales_grp,
a.ship_to_sales_rep_cd,
cust_name
A derived table is a logical temp table within the query, but not materialis
ed,
and SQL Server recast computation order for the best query plan.
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

Help with Update Query command Problem

Hi all,

I have this store procedure as follows:

Create Proc UpdateProblem
@.ProblemID int,
@.CompanyName varchar (50),
@.Firstname varchar (50),
@.Lastname Varchar (50),
@.Address varchar (50),
@.Postcode varchar (50),
@.City varchar (50),
@.Phone varchar (50),
@.Cutype varchar (50),
@.ProDescript varchar (50),
@.Sol varchar (50),
@.Email varchar (50)

as Update Problem
set CompanyName = @.CompanyName,
Firstname = @.Firstname,
Lastname = @.Lastname,
Address = @.Address,
PostCode = @.Postcode,
City = @.City,
Phone = @.Phone,
Cutype = @.Cutype,
ProDescript = @.ProDescript,
Sol = @.Sol,
Email = @.Email

where ProblemID = @.ProblemID

when I test the querry

exec UpdateProblem
10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@.ht.com'

what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.

Please help. I have set the ProblemID as the Primary key.

When you do a SELECT * FROM Problem WHERE PRoblemID = 10004 , do you get 1 row or multiple rows?|||Only 1 row when I run : SELECT * FROM Problem WHERE PRoblemID = 10004|||

Find out the error. where ProblemID = @.ProblemID --> wrong

where @.ProblemID = ProblemID -- Right

cheers

|||I dont think so. They are both same. There's something else that happened. ...WHERE ProblemID = @.ProblemID should work just fine. Thats the way most queries are written.|||Hi Justnew, is the ProblemID column defined as int type? Can you post some sample data so that we can repro your issue?

Help with UDF using OPENROWSET to EXECUTE sproc

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||-- here is the 1st fragment, inside of a large production sproc. It
needs to create and populate #Areas which
-- will be used in JOINS later in the sproc and in the inner sprocs it
calls.
create table #areas
(
state char(2) ,
county char(3)
, SMSA_CD varchar(10) )
Execute dbo.AMS_I_GetAreasV2 -- 12/29/2004 JJA: add
parm for tiered-access support of custom areas using
dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID -- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName -- 12/29/2004 JJA: Make Custom Area
Definitions conform to tiered access rules and limits
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P' -- P: Purchase-Money R: Refi B:
Both F: FHA V: VA G: All_Govt
, @.CustomID INT = 0 -- 09/20/2004 JJA; 12/09/2004 JJA
, @.Debug int = 0 -- 03/01/2004 JJA
)
As
Set NOCount ON
declare @.err int, @.rows int -- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = ''; -- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =
' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0 -- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID --
09/20/2004 JJA
,@.Debug -- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT -- 09/20/2004 JJA
GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
----
--
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
----
--*
-- PURPOSE: Translate an ID for a custom area into a record set of
-- areas that the custom area defines. If any metro-area is part
-- of a custom-area definition, its component STATE and COUNTY are
-- included in this record set (otherwise SMSA is left as NULL).
-- USAGE: Called by AMS_I_GetAreasV2 to populate #Areas
-- HISTORY: 09/23/2004 JJA - implement new sproc
----
--*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.CustomID int -- key to parent table (i.e. has name of this
definition, etc.)
,@.Debug int = 0 -- set default to 1 for Query-Analyzer debugging
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties (NULL, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
would have been added to the
-- SELECT just below as another table to JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
at the top #AREAS is being
-- populated and I got the complaint about nesting immediately
trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||I am sorry I did not chop off all comments from the code posted above
(it is pretty hard to read). I will try to summarize:
In this problem, there are 2 pieces of business logic implemented by
stored procedures and called in numerous places by other stored
procedures.
AMS_I_GetAreasV2 is called to populate a temporary table #AREAS which
is used in JOINS here and is used in reporting elsewhere.
AccountFetchCounties is called to return a set of allowed counties
based on a code and username.
In short, I need to expand the functionality of AMS_I_GetAreasV2 such
that in some cases it calls AccountFetchCounties, joining the allowed
set of rows produced by that sproc with another SELECT.
As I said, when I ran into the complaint about nesting, I tried making
a UDF to EXECUTE AccountFetchCounties but that is disallowed. Then I
tried OPENROWSET with EXECUTE of AccountFetchCounties but that does not
allow parameters to be passed to the sproc. Then I posted the original
question.
To solve this in a semi-ugly way, I have replicated all the
functionality of my sproc, AccountFetchCounties, into a UDF. Then in
the SELECT inside 'AMS_I_GetAreasV2_CustomAreaTranslator',
I have
added this UDF which returns a table to my JOIN which works pretty
well.
But I am uncomfortable with the idea of cloning this "business logic"
from SPROC to UDF because of keeping my sanity in the future
maintenance of this code. Thanks in advance for trying to follow my
story and help.|||Here is a better post of code (now cleaned up and fewer comments) that
caused the original nesting error:
-- this CREATE is inside a large production sproc
-- here is the first relevant fragment:
create table #areas
(
state char(2)
, county char(3)
, SMSA_CD varchar(10)
)
Execute dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P'
, @.CustomID INT = 0
, @.Debug int = 0
)
As
Set NOCount ON
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
-- , @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
SET NOCOUNT ON
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
-- would have been added to the SELECT just below as another table to
JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
-- at the top #AREAS is being populated and I got the complaint about
-- nesting immediately trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||Please reply if you can get a chance. I tried to describe my problem
better - maybe I gave too much detail. Bottom line is that I had to
clone business logic of a stored procedure into a user defined
function. I do not like having to do this but it works. Like I said, in
trying to extend the processing of the stored procedure, I ran into
multiple restrictions which I have tried to describe.

Help with UDF using OPENROWSET to EXECUTE sproc

Here is the UDF I am trying to create:
ALTER FUNCTION dbo.TieredAccessCounties
(
@.State CHAR(2)
, @.SourceTable CHAR(1)
, @.UserName VARCHAR(30)
)
RETURNS TABLE
AS
RETURN
SELECT A.* FROM
OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
AS A
I cannot get this to work. I am getting these messages:
Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
Line 10
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
Line 10
Must declare the variable '@.SourceTable'.
[OLE/DB provider returned message: Deferred prepare could not be
completed.]OPENROWSET is opening the connection to MDWDATA and executing the following
(literally):
'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName '
It has no idea what @.SourceTable and @.UserName are. Unfortunately, there's
no way I know of to pass values into OPENROWSET within a UDF.
Can you describe what you're trying to do? Maybe there's a better way than
using a UDF.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104430908.817837.179350@.z14g2000cwz.googlegroups.com...
> Here is the UDF I am trying to create:
> ALTER FUNCTION dbo.TieredAccessCounties
> (
> @.State CHAR(2)
> , @.SourceTable CHAR(1)
> , @.UserName VARCHAR(30)
> )
> RETURNS TABLE
> AS
> RETURN
> SELECT A.* FROM
> OPENROWSET('SQLOLEDB','MDWDATA';'sa';'passwordX',
> 'EXECUTE dbo.AccountFetchCounties NULL, @.SourceTable, @.UserName ')
> AS A
> I cannot get this to work. I am getting these messages:
> Server: Msg 8180, Level 16, State 1, Procedure TieredAccessCounties,
> Line 10
> Statement(s) could not be prepared.
> Server: Msg 137, Level 15, State 1, Procedure TieredAccessCounties,
> Line 10
> Must declare the variable '@.SourceTable'.
> [OLE/DB provider returned message: Deferred prepare could not be
> completed.]
>|||The sproc dbo.AccountFetchCounties is a bit of business logic that
takes 3 parms and returns a set of rows representing state-county areas
that are "allowed" for a given username. I need to add this
functionality inside of a much larger stored procedure and I need to
JOIN the output of this sproc with another SELECT. The first problem I
ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
because I had created a temp table to hold the rows back from
AccountFetchCounties (but this in turn was inside of an outer INSERT
into tableX EXECUTE myOuterSproc structure.
So that is why I thought to use User Defined Function to return a
table. But then I see that a UDF cannot call a stored procedure. So I
researched and discovered OPENROWSET as an alternative. But now I am
stuck with this strange message. I really appreciate your help. Thank
you for your quick reply.|||Can you post some code to duplicate the CANNOT BE NESTED error? I've never
seen it before, and was just about to recommend a temp table. There must be
a way around that...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"JJA" <johna@.cbmiweb.com> wrote in message
news:1104433457.065710.65650@.z14g2000cwz.googlegroups.com...
> The sproc dbo.AccountFetchCounties is a bit of business logic that
> takes 3 parms and returns a set of rows representing state-county areas
> that are "allowed" for a given username. I need to add this
> functionality inside of a much larger stored procedure and I need to
> JOIN the output of this sproc with another SELECT. The first problem I
> ran into was that I got the message: INSERT EXEC CANNOT BE NESTED
> because I had created a temp table to hold the rows back from
> AccountFetchCounties (but this in turn was inside of an outer INSERT
> into tableX EXECUTE myOuterSproc structure.
> So that is why I thought to use User Defined Function to return a
> table. But then I see that a UDF cannot call a stored procedure. So I
> researched and discovered OPENROWSET as an alternative. But now I am
> stuck with this strange message. I really appreciate your help. Thank
> you for your quick reply.
>|||Adam,
Here's an example:
create table T (
i int
)
go
create proc p as select 4
go
create proc q as
insert into T exec p
go
insert into T exec q
go
drop proc p,q
drop table T
I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
nested. In other words, the statement INSERT INTO T EXEC q will fail
if the procedure q contains an INSERT .. EXEC construction.
SK
Adam Machanic wrote:
>Can you post some code to duplicate the CANNOT BE NESTED error? I've never
>seen it before, and was just about to recommend a temp table. There must be
>a way around that...
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:uU38dyu7EHA.2196@.TK2MSFTNGP14.phx.gbl...
> I believe EXEC can be nested, and it's just INSERT .. EXEC that can't be
> nested. In other words, the statement INSERT INTO T EXEC q will fail
> if the procedure q contains an INSERT .. EXEC construction.
That makes perfect sense. The example you posted doesn't seem to do
anything, whereas at least something like this has some semblance of
purpose:
EXEC ('EXEC (''SELECT 1'')')
... not that I'd do that, but at least it makes more sense than nesting
an INSERT :)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||-- here is the 1st fragment, inside of a large production sproc. It
needs to create and populate #Areas which
-- will be used in JOINS later in the sproc and in the inner sprocs it
calls.
create table #areas
(
state char(2) ,
county char(3)
, SMSA_CD varchar(10) )
Execute dbo.AMS_I_GetAreasV2 -- 12/29/2004 JJA: add
parm for tiered-access support of custom areas using
dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID -- 09/20/2004 JJA: Add support for Custom
Area Definitions as a source for populating #Areas table
, @.UserName = @.UserName -- 12/29/2004 JJA: Make Custom Area
Definitions conform to tiered access rules and limits
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P' -- P: Purchase-Money R: Refi B:
Both F: FHA V: VA G: All_Govt
, @.CustomID INT = 0 -- 09/20/2004 JJA; 12/09/2004 JJA
, @.Debug int = 0 -- 03/01/2004 JJA
)
As
Set NOCount ON
declare @.err int, @.rows int -- 03/01/2004 JJA - capture essentials for
debugging
declare @.sepid varchar(80)
declare @.time varchar(30)
select @.sepid = ' - AMS_I_GetAreasV2: '
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = ''; -- this will enable PRINT below to occur and
DATALENGTH(@.SMSAV) to be 0 so CASE logic below works
end
select @.time = convert(varchar(30), getdate(), 109)
print @.time + @.sepid + ' entered. SMSA = ' + @.SMSAV + '; SourceTable =' + @.SourceTable + '; CustomID = ' + Convert(Varchar(9),@.CustomID)
if @.CustomID > 0 -- In this mode, a custom area definition is
translated -- 09/20/2004 JJA
BEGIN -- 09/20/2004 JJA
print @.time + @.sepid + ' Custom-Area Definition Mode is in
effect.' -- 09/20/2004 JJA
INSERT INTO #Areas -- 09/20/2004 JJA
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator @.CustomID --
09/20/2004 JJA
,@.Debug -- 09/20/2004 JJA
-- , @.SourceTable, @.UserName
-- I had to abandon these new arguments when nesting complaint
showed up
SELECT @.ROWS = @.@.ROWCOUNT -- 09/20/2004 JJA
GOTO CommonExitPoint -- 09/20/2004 JJA
END
--SMSA:
IF datalength(@.SMSAV) = 4 --SMSA
BEGIN
print @.time + @.sepid + ' SMSA Mode in effect.'
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-----
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
----*
-- PURPOSE: Translate an ID for a custom area into a record set of
-- areas that the custom area defines. If any metro-area is part
-- of a custom-area definition, its component STATE and COUNTY are
-- included in this record set (otherwise SMSA is left as NULL).
-- USAGE: Called by AMS_I_GetAreasV2 to populate #Areas
-- HISTORY: 09/23/2004 JJA - implement new sproc
----*
-- EXECUTE dbo.AMS_I_GetAreasV2_CustomAreaTranslator 1,@.Debug=1
(
@.CustomID int -- key to parent table (i.e. has name of this
definition, etc.)
,@.Debug int = 0 -- set default to 1 for Query-Analyzer debugging
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
DECLARE @.pid varchar(100)
SELECT @.pid = 'AMS_I_GetAreasV2_CustomAreaTranslator: '
SET NOCOUNT ON
if @.Debug = 1 PRINT @.pid + ' entered for CustomID = ' +
CONVERT(VARCHAR(9),@.CustomID)
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties (NULL, @.CategoryCode = @.SourceTable,
@.UserName = @.UserName)
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
would have been added to the
-- SELECT just below as another table to JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
at the top #AREAS is being
-- populated and I got the complaint about nesting immediately
trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||I am sorry I did not chop off all comments from the code posted above
(it is pretty hard to read). I will try to summarize:
In this problem, there are 2 pieces of business logic implemented by
stored procedures and called in numerous places by other stored
procedures.
AMS_I_GetAreasV2 is called to populate a temporary table #AREAS which
is used in JOINS here and is used in reporting elsewhere.
AccountFetchCounties is called to return a set of allowed counties
based on a code and username.
In short, I need to expand the functionality of AMS_I_GetAreasV2 such
that in some cases it calls AccountFetchCounties, joining the allowed
set of rows produced by that sproc with another SELECT.
As I said, when I ran into the complaint about nesting, I tried making
a UDF to EXECUTE AccountFetchCounties but that is disallowed. Then I
tried OPENROWSET with EXECUTE of AccountFetchCounties but that does not
allow parameters to be passed to the sproc. Then I posted the original
question.
To solve this in a semi-ugly way, I have replicated all the
functionality of my sproc, AccountFetchCounties, into a UDF. Then in
the SELECT inside 'AMS_I_GetAreasV2_CustomAreaTranslator', I have
added this UDF which returns a table to my JOIN which works pretty
well.
But I am uncomfortable with the idea of cloning this "business logic"
from SPROC to UDF because of keeping my sanity in the future
maintenance of this code. Thanks in advance for trying to follow my
story and help.|||Here is a better post of code (now cleaned up and fewer comments) that
caused the original nesting error:
-- this CREATE is inside a large production sproc
-- here is the first relevant fragment:
create table #areas
(
state char(2)
, county char(3)
, SMSA_CD varchar(10)
)
Execute dbo.AMS_I_GetAreasV2
@.SMSA,
@.S1,
@.C1,
@.SourceTable
, @.CustomID = @.CustomID
, @.UserName = @.UserName
-- here is the 2nd fragment (EXECUTEd from above):
ALTER Procedure dbo.AMS_I_GetAreasV2
(
@.SMSA VarChar(10) = NULL,
@.S1 Char(2) = NULL,
@.C1 Char(3) = NULL,
@.SourceTable varchar(1) = 'P'
, @.CustomID INT = 0
, @.Debug int = 0
)
As
Set NOCount ON
declare @.err int, @.rows int
DECLARE @.SMSAV VARCHAR(10)
SELECT @.SMSAV = RTRIM(@.SMSA)
If @.SMSAV IS NULL
begin
SELECT @.SMSAV = '';
end
if @.CustomID > 0
BEGIN
INSERT INTO #Areas
EXECUTE @.ERR = dbo.AMS_I_GetAreasV2_CustomAreaTranslator
@.CustomID
,@.Debug
-- , @.SourceTable
-- , @.UserName
-- I had to abandon these new arguments when nesting complaint
-- showed up
SELECT @.ROWS = @.@.ROWCOUNT
GOTO CommonExitPoint
END
--SMSA:
IF datalength(@.SMSAV) = 4
BEGIN
If @.SourceTable = 'P'
Begin
INSERT INTO #Areas
SELECT C.State_cd, C.County_Cd, @.SMSAV AS SMSA_CD
FROM County C INNER JOIN SMSA M ON C.SMSA_CD = M.SMSA_CD
WHERE C.SMSA_CD = @.SMSAV AND C.PDATA = 'YES'
.........etc. etc.
-- here is the 3rd fragment EXECUTED from just above
ALTER procedure dbo.AMS_I_GetAreasV2_CustomAreaTranslator
(
@.CustomID int
,@.Debug int = 0
-- ,@.SourceTable char(1)
-- ,@.UserName varchar(30)
)
as
DECLARE @.err int
DECLARE @.rows int
SET NOCOUNT ON
-- Here, I tried to do:
Create Table #AllowedCounties
(
County_CD CHAR(3),
TypeCounty VARCHAR(60),
State_CD CHAR(2)
)
INSERT INTO #AllowedCounties
EXECUTE dbo.AccountFetchCounties
NULL
, @.CategoryCode = @.SourceTable
, @.UserName = @.UserName
-- SourceTable and Username would have been passed in from callers.
-- Upon successful creation of temporary table #AllowedCounties, it
-- would have been added to the SELECT just below as another table to
JOIN.
-- Yet the INSERT INTO / EXECUTE construct here is disallowed because
-- at the top #AREAS is being populated and I got the complaint about
-- nesting immediately trying to compile this "inner" sproc.
SELECT DISTINCT
C.State_CD
, C.County_CD
, SMSA_CD =CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then C.CBSACode + C.CBSADivision
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then C.CBSACode
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then C.SMSA_CD
END
FROM dbo.AcctCustomArea X
, dbo.GovtCountiesList C
WHERE
X.CustomID = @.CustomID
AND
CASE
When Datalength(X.SMSACBSA) = 10 AND X.SMSACBSA = C.CBSACode +
C.CBSADivision Then 1
When Datalength(X.SMSACBSA) = 5 AND X.SMSACBSA = C.CBSACode
Then 1
When Datalength(X.SMSACBSA) = 4 AND X.SMSACBSA = C.SMSA_CD
Then 1
When X.SMSACBSA IS NULL AND X.State = C.State_CD
AND X.County = C.County_CD Then 1
ELSE 0 -- where 0 = 1 rejects record
END = 1 -- where 1 = 1 allows record
ORDER BY
C.State_CD
, C.County_CD|||Please reply if you can get a chance. I tried to describe my problem
better - maybe I gave too much detail. Bottom line is that I had to
clone business logic of a stored procedure into a user defined
function. I do not like having to do this but it works. Like I said, in
trying to extend the processing of the stored procedure, I ran into
multiple restrictions which I have tried to describe.

Monday, March 26, 2012

HELP! Stored Procedure problem

HELP! Stored Procedure problem

Monday, March 12, 2012

HELP! Data Conversion Rounding Problem

HELP! Data Conversion Rounding Problem

HELP! Data Conversion Rounding Problem

Wednesday, March 7, 2012

HELP! Concatentation of 2 INT columns in SQL 2000

Friday, February 24, 2012

Help with WHERE Clause in Stored Procedure

Sunday, February 19, 2012

Help with varchar to date

Help with user defined function

Help with Update Query command Problem

Help with UDF using OPENROWSET to EXECUTE sproc

Help with UDF using OPENROWSET to EXECUTE sproc

Help with update forms

Blog Archive

About Me