Help with SQL Function - Cant change Null value

What I'm trying to do is to build a string that will print all the presences of a user for a session. My problem is that I'd like to put the - value when the pr_presence isn't True or False but right now it only returns pr_presence when it contains a boolean value. This way I can't treat the NULL or blank value. Any help would be really appreciate !

Here is my fonction:

DECLARE strPresence VARCHAR;
sessionID alias for $1;
userID alias for $2;
idr record;
BEGIN
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
Else '-'
End as "TypePresence"
FROM seance, InscriptionEtat, inscriptionSession
RIGHT JOIN presence ON inscriptionSession.usr_id = presence.usr_id
LEFT JOIN session ON inscriptionSession.ses_id = session.ses_id
WHERE session.ses_id = sessionID
AND presence.usr_id = userID
AND presence.sea_id = seance.sea_id
AND seance.ses_id = session.ses_id
AND seance.sea_valide = 't'
AND inscriptionSession.usr_id = usager.usr_id
AND inscriptionSession.ie_id = inscriptionEtat.ie_id
AND inscriptionEtat.ie_OK = 't'
ORDER BY seance.sea_datedebut
LOOP
strPresence:= strPresence||', '||idr."TypePresence";
END LOOP;
strPresence:= substring(strPresence,char_length(', ')+1);
RETURN strPresence;
END;I don't quite understand your problem. The CASE statement works OK:

SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;

P T
- -
t P
f A
-
x -|||Originally posted by andrewst
I don't quite understand your problem. The CASE statement works OK:

SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;

P T
- -
t P
f A
-
x -

If my session contains 4 seances, and the user only enters 1 presence for these seances, my string should looks like "P,-,-,-" because the other 3 pr_presence would be Null
Right now, my string is returning "P" when I test it... I also tought my case was ok but I'm now wondering why I don't get what I want. Thanks for your help

Help with sp_executesql

I have a full sql statement which was generated dynamicly, and need to
execute that string and then take the output and generate a
spreadsheet document based on the output. I'm new to sql and the book
I have doesn't really explain much. Anyone with an example of their
work would be appreaciated.
thank you.use output option
for example:

select @.sql = 'select @.bdate=min(effective_date) from ' + @.table
SET @.ParmDefinition = N'@.bdate datetime OUTPUT'
EXEC sp_executesql @.sql, @.ParmDefinition, @.bdate OUTPUT

"Ado" <a3vr6tur@.hotmail.com> wrote in message
news:848bd3a0.0401151001.7270c50b@.posting.google.c om...
> I have a full sql statement which was generated dynamicly, and need to
> execute that string and then take the output and generate a
> spreadsheet document based on the output. I'm new to sql and the book
> I have doesn't really explain much. Anyone with an example of their
> work would be appreaciated.
> thank you.|||"Ado" <a3vr6tur@.hotmail.com> wrote in message
news:848bd3a0.0401151001.7270c50b@.posting.google.c om...
> I have a full sql statement which was generated dynamicly, and need to
> execute that string and then take the output and generate a
> spreadsheet document based on the output. I'm new to sql and the book
> I have doesn't really explain much. Anyone with an example of their
> work would be appreaciated.
> thank you.

The Books Online syntax documentation for sp_executesql has two examples,
and the subject "Using sp_executesql" has several more. You'll probably also
find this useful:

http://www.sommarskog.se/dynamic_sql.html

If you're still having problems after checking those sources, perhaps you
could post a (simple) example of what you're trying to do.

Simonsql

Help with sorting strings...

I'm trying to search a database and get a list of results of
the latest values which are of type 'STRING'. How would I do it?

For instance, I've got a dataset like the one below.

Col 1 Col 2 Col 3
---------------
Dog Blue 11a
Dog Blue 11b
Cat Blue 14
Cat Red 21a
Cat Red 21b
Fish Yellow 31
Shark Black 12a
Shark Purple 21

I only want it to return the ones with the highest 'Col 3' value, so it returns something like.

Col 1 Col 2 Col 3
----------------
Dog Blue 11b
Cat Red 21b
Fish Yellow 31
Shark Purple 21

I've tried something like this:

SELECT
table.col1,
table.col2,
table.col3
FROM
table
WHERE
1 > (
SELECT
COUNT(DISTINCT table.col3)
FROM
table tab
WHERE
tab.col3 > table.col3
)

However I get the ERR: An aggregate may not appear in the WHERE clause
unless it is in a subquery contained in a HAVING clause or select
list, and the column being aggregated is an outer reference.I don't what your backend database is but this should help.

SELECT t.col1
, t.col2
, t.col3
FROM tablex t
, (SELECT tablex.col1
, max (tablex.col3) col3
FROM tablex
group by col1) g
WHERE t.col1 = g.col1
AND t.col3 = g.col3
;

or using ANSI joins

SELECT t.col1
, t.col2
, t.col3
FROM tablex t
JOIN (SELECT tablex.col1
, max (tablex.col3) col3
FROM tablex
group by col1) g
ON t.col1 = g.col1
AND t.col3 = g.col3
;|||Thanks for your help gannet.

Help with reuse methology?

I find that many stored procedures are necessary in nearly all databases I
use. Creating dynamic (one that takes a string and executes the sql in it)
stored procedures is expensive performance wise. However, they do allow you
to pass in strings of sql and execute it. I find in general that SPs are
created and can only be used for one specific thing - registration form
input, account info update, etc. For example, I can't take the account
update SP and use it for the registration input SP.
Is there some general or fundamental guide that should be followed to lower
the overall number of stored procedures in a database?
Thanks,
BrettBrett wrote:
> I find that many stored procedures are necessary in nearly all
> databases I use. Creating dynamic (one that takes a string and
> executes the sql in it) stored procedures is expensive performance
> wise. However, they do allow you to pass in strings of sql and
> execute it. I find in general that SPs are created and can only be
> used for one specific thing - registration form input, account info
> update, etc. For example, I can't take the account update SP and use
> it for the registration input SP.
> Is there some general or fundamental guide that should be followed to
> lower the overall number of stored procedures in a database?
> Thanks,
> Brett
Not really. You need what you need. Dynamic SQL is not the way to go.
It's really no different than executing the SQL statement directly from
the client and exposes your database to SQL injection and performance
issues.
Writing stored procedures is just a part of the application development
process. You can leverage things like functions or utility-type stored
procs to simplify repetitive code, but not much you can do to lower the
number of procedures you need.
David Gugick
Imceda Software
www.imceda.com

Help with retrieving string data

Hello,

I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.

autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'";

Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it.

Thanks for your help in advance.

Can you share/check the length of 'ownph' column in the database. Your second select is doing ltrim(rtrim) but not limiting the length to 12 characters.

|||

I am not sure how long the field is in the database that I am retrieving the data from, because I get nothing when I put a watch on the field. As to the ltrim(rtrim) function, the examples do not show how to limit the length to any specified number of characters. All the examples only show to do ltrim(rtrim(fieldname)).

|||Problem solved. Thank you for your help.|||As we use this post to learn new things, can you share how you got around the problem.

Help with retrieving string data

Hello,

I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.

autoClaimSalvage.Phone = "Select LEFT ('" + (string)(drInputDataSource["OWNPHN"]) + "',12) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM(OWNPHN)) From '" + entityManager.TargetTable + "'";

autoClaimSalvage.Phone = "Select LTRIM(RTRIM('" + ((string)(drInputDataSource["OWNPHN"])) + "'))" + entityManager.TargetTable + "'";

Please let me know what i am doing wrong and if anyone has a sample code or if you can point me in the right direction, I will appreciate it.

Thanks for your help in advance.

Can you share/check the length of 'ownph' column in the database. Your second select is doing ltrim(rtrim) but not limiting the length to 12 characters.

|||

I am not sure how long the field is in the database that I am retrieving the data from, because I get nothing when I put a watch on the field. As to the ltrim(rtrim) function, the examples do not show how to limit the length to any specified number of characters. All the examples only show to do ltrim(rtrim(fieldname)).

|||Problem solved. Thank you for your help.|||As we use this post to learn new things, can you share how you got around the problem.

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"

.Connection = sqlConnection1

EndWith

cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<namespaces>

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken