asssigning values to multiple vars in a SP in one go (without temp table)

I have to select several field values from a table and need to assign them to different variables in my SP.

Here's what I do now:

declare

@.ReceiverEmailnvarchar(50)

SET

@.ReceiverEmail=(SELECT EmailFROM UsersWHERE UserCode=@.UserCodeOwner)

declare

@.UsernameSendernvarchar(50)

SET

@.UsernameSender=(SELECT UsernameFROM UsersWHERE UserCode=@.UserCodeOwner)
As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:S
So, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table...)?

Peter,

i dont know off the top of my head a way to get around the 2X search without the temptable, unless you use a table variable instead which in principle is still the same thing as your temp table. if the result set of email and usernames is not that big, then the table variable may save you a bit since it is being run in memory. I know this is not the answer your probably looking for, but its all i have...good luck!

|||the reason I dont want to use a temp table is because i've read that it might cause concurrency conflicts amongst others...
Is that still true in SQL Server 2005?
Otherwise I might as well go with the temp table..|||

Hi there,

try with this code it works

DECLARE @.RECEIVEREMAILNVARCHAR(50)DECLARE @.USERNAMESENDERNVARCHAR(50)

SELECT
@.RECEIVEREMAIL = EMAIL,
@.USERNAMESENDER = USERNAME
FROM USERS
WHERE USERCODE = @.USERCODEOWNER

Regards,

Fernando

|||

It sude did!
Thanks!

Assistance with Stored Procedure

I am running SQL Server 7.0 and using a web interface. I would like
for a user to be able to input multiple values into a single field
with some sort of delimiter (such as a comma). I want to pass this
field into a Stored Procedure and have the stored procedure use the
data to generate the resutls.

Example:

Web page would ask for ID number into a field called IDNum. User
could input one or many ID numbers separated by a comma or some other
delemiter - could even be just a space (113, 114, 145).

SQL statement in Stored Procedure is something like this:

Select * from tblEmployess where IDNumber = @.IDNum

I need the SQL statement to somehow use an "or" or a "loop" to get all
of the numbers passed and use the delimiter to distinguish when the
"loop" stops.

I obtained a module from a friend that allows me to do this in access,
but have recently converted everything to SQL server and web
interface. Now, everyone in the office expects to be able to
accomplish the same results via the web.

Any help is appreciated. If you need any additional information to
provide me some assistance, please email me at
tod.thames@.nc.ngb.army.mil.

Thanks in advance.

TodTake a look at http://www.algonet.se/~sommar/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod|||Everytime I try to get to the website you refrenced, I get TCP_ERROR.
Some sort of communication problem. Is there any other sites that have
the same sort of information?

Thanks for the response,

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know if the content is mirrored elsewhere. The author, Erland
Sommarskog, frequents this newsgroup so maybe he'll jump in.

BTW, I don't have any problems accessing the site.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:3f803253$0$195$75868355@.news.frii.net...
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that
have
> the same sort of information?
> Thanks for the response,
> Tod
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||[posted and mailed]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that have
> the same sort of information?

Too bad. If you have the complete error message, I'm interested. I'm
inclined to suspect that this might be some firewall problem at your
side, but I might get carried away of the .mil in your address.

Anyway, here is an excerpt of the part which is most relevant to
you. If you want to read the entire article, just drop me a line.

An Extravagant List-of-integers Procedure

The technique in the previous section can of course be applied to a list
of integers as well, so what comes here is not a true port of the
iter_intlist_to_table function, but a version that goes head over heels
to validate that the list items are valid numbers to avoid a conversion
error. And to be extra ambitious, the procedure permits for signed
numbers such as +98 or -83. If a list item is not a legal number, the
procedure produces a warning. The procedure fills in a temp table that
has a listpos column; this column will show a gap if there is an illegal
item in the input.

CREATE PROCEDURE intlist_to_table_sp @.list ntext AS

DECLARE @.pos int,
@.textpos int,
@.listpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @.textpos = 1, @.listpos = 1, @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SELECT @.chunklen = 4000 - datalength(@.leftover) / 2
SELECT @.tmpstr = ltrim(@.leftover + substring(@.list, @.textpos, @.chunklen))
SELECT @.textpos = @.textpos + @.chunklen

SELECT @.pos = charindex(' ', @.tmpstr)
WHILE @.pos > 0
BEGIN
SELECT @.str = rtrim(ltrim(substring(@.tmpstr, 1, @.pos - 1)))
EXEC insert_str_to_number @.str, @.listpos
SELECT @.listpos = @.listpos + 1
SELECT @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SELECT @.pos = charindex(' ', @.tmpstr)
END

SELECT @.leftover = @.tmpstr
END

IF ltrim(rtrim(@.leftover)) <> ''
EXEC insert_str_to_number @.leftover, @.listpos
go

-- This is a sub-procedure to intlist_to_table_sp
CREATE PROCEDURE insert_str_to_number @.str nvarchar(200),
@.listpos int AS

DECLARE @.number int,
@.orgstr nvarchar(200),
@.sign smallint,
@.decimal decimal(10, 0)

SELECT @.orgstr = @.str

IF substring(@.str, 1, 1) IN ('-', '+')
BEGIN
SELECT @.sign = CASE substring(@.str, 1, 1)
WHEN '-' THEN -1
WHEN '+' THEN 1
END
SELECT @.str = substring(@.str, 2, len(@.str))
END
ELSE
SELECT @.sign = 1

IF @.str LIKE '%[0-9]%' AND @.str NOT LIKE '%[^0-9]%'
BEGIN
IF len(@.str) <= 9
SELECT @.number = convert(int, @.str)
ELSE IF len(@.str) = 10
BEGIN
SELECT @.decimal = convert(decimal(10, 0), @.str)
IF @.decimal <= convert(int, 0x7FFFFFFF)
SELECT @.number = @.decimal
END
END

IF @.number IS NOT NULL
INSERT #numbers (listpos, number) VALUES (@.listpos, @.sign * @.number)
ELSE
RAISERROR('Warning: at position %d, the string "%s" is not an legal integer',
10, -1, @.listpos, @.orgstr)
go

Here is how you would use it:

CREATE PROCEDURE get_product_names_iterproc @.ids varchar(50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_table_sp @.ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_names_iterproc '9 12 27 37'

The validation of the list item is in the sub-procedure
insert_str_to_number. For many purposes it would be sufficient to have
the test

@.str NOT LIKE '%[^0-9]%' AND len(@.str) BETWEEN 1 AND 9

which checks that @.str only contain digits and is at most nine digits
long (that is, you disapprove ten-digit numbers as well as signed
numbers).

You might guess that there is a performance cost for this extravaganza,
and indeed the procedure needs about 50% more time than the corresponding
function. Still, for many situations, the execution time is acceptable.

One note about the warning produced with RAISERROR: with ADO, this
warning may be difficult or impossible to detect on client level. If you
change the severity from 10 to 11, it will be an error, and raise an
error in your client code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thanks for the response - it's a little above my abilities, but I plan
on studying it and trying to make it work for me project.

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See if the following link helps..
http://tinyurl.com/6iil

--
-- Anith|||Tod,

Try this:

create procedure ListEmployees
@.IDNum char(1024)
as begin
set @.IDNum = ' ' + replace(@.IDNum, ',', ' ') + ' '
select *
from tblEmployess
where @.IDNum like ('% ' + ltrim(str(IDNumber)) + ' %')
end

Shervin

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod

assigning values to parameters dynamically

i using a bound data grid which is using a stored proc. The stored proc needs the ClientID "if logged in" there is no form or control on the page outside of the loginstatus. I am wanting to pass theMembership.GetUser.ProviderUserKey.ToString() to the asp:parameter but I cant get it to work.

So How do I pass a variable to a stored proc parameter using a bound data grid.

I this its very strange that this cant be dont and there are a raft of reason why you wold want to do this with out the need to pass it to a form control.

please help

jim

Add the parameter to you parameters collection as a normal asp:parameter, then hook up to the data-source control's Inserting/Updating evnet (I assume you use a data-source control with the grid) and use the event argument to get access to the command object's parameters collection (if you use SqlDataSource), or the InputParameters if you use the ObjectDataSource.|||Or alternatively, when the user authenticates, store their id in a session variable, then anywhere in your app you can add it as a session parameter.

Assigning values to multiple variables (via subqueries) for use in an update

Hi, figured out where I was going wrong in my post just prior, but is
there ANY way I can assign several variables to then use them in an
Update statement, for example (this does not work):

ALTER PROCEDURE dbo.UpdateXmlWF
(
@.varWO varchar(50)
)
AS
DECLARE @.varCust VARCHAR(50)
SELECT @.varCust = (SELECT Customer FROM tblWorkOrders
WHERE WorkOrder=@.varWO)

DECLARE @.varAssy VARCHAR(50)
SELECT @.varAssy=(SELECT Assy FROM tblWorkOrders
WHERE WorkOrder=@.varWO)

UPDATE statement here using declared variables...

I can set one @.variable but not multiple. Any clues? kinda new to
this.

Thanks,
KathyKathy,

See my response to your previous post.

I think you want to try this instead:

DECLARE @.varCust varchar(50), varAssy varchar(50)
SELECT @.varCust = Customer,
@.varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @.varWO

UPDATE table
SET field = @.varCust,
field2 = @.varAssy
where somefield = somevalue

In fact, you could condense this to the following:

UPDATE table
SET field = tblWorkOrders.Customer,
field2 = tblWorkOrders.Assy
FROM tblWorkOrders
JOIN table ON tblWorkOrders.PK = table.FK
WHERE tblWorkOrders.WorkOrder = @.varWO
AND otherconditions...

Hope this helps.

--
-Chuck Urwiler, MCSD, MCDBA
http://www.eps-software.com|||Kathy,

You don't need two separate SELECT statements to assign values to your
variables. You can do it like this:

DECLARE @.varCust VARCHAR(50),
@.varAssy VARCHAR(50)

SELECT @.varCust = Customer,
@.varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @.varWO

But I don't understand your problem with inserting. You can modify as many
columns as you want in UPDATE statement. What's your problem here?
By the way, if these variables have been declared just to use in your UPDATE
statement, then you don't really need them. You can join tblWorkOrders table
with whatever table you want update and fetch values from tblWorkOrders
directly into your destination table.

Shervin

"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310030718.82cad7f@.posting.google.co m...
> Hi, figured out where I was going wrong in my post just prior, but is
> there ANY way I can assign several variables to then use them in an
> Update statement, for example (this does not work):
> ALTER PROCEDURE dbo.UpdateXmlWF
> (
> @.varWO varchar(50)
> )
> AS
> DECLARE @.varCust VARCHAR(50)
> SELECT @.varCust = (SELECT Customer FROM tblWorkOrders
> WHERE WorkOrder=@.varWO)
> DECLARE @.varAssy VARCHAR(50)
> SELECT @.varAssy=(SELECT Assy FROM tblWorkOrders
> WHERE WorkOrder=@.varWO)
> UPDATE statement here using declared variables...
> I can set one @.variable but not multiple. Any clues? kinda new to
> this.
> Thanks,
> Kathy

assigning values to fields

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, 1, Desc1
Cust1, 2, Desc1
Cust1, 2, Desc2
How would you make those null values 3's or the MAX(Inv#) for Cust1?
thanks,
rodcharIf this is your entire table, you might need to clean it up to include a
key. Otherwise,
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# ) + 1
FROM tbl t1 )
WHERE Inv# IS NULL ;
Anith|||Try:
SELECT NAME, ISNULL(INV,3), DESCCOL
FROM YOURTABLE
OR
SELECT NAME, ISNULL(INV,(SELECT MAX(INV)FROM YOURTABLE)), DESCCOL
FROM YOURTABLE
OR
DECLARE @.VAL INT
SELECT @.VAL = MAX(INV) FROM YOURTABLE
SELECT NAME, ISNULL(INV,@.VAL), DESCCOL
FROM YOURTABLE
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||UPDATE YourTable
SET [Inv#] =
(
SELECT MAX([Inv#])
FROM YourTable Y1
WHERE Y1.Name = YourTable.Name
)
WHERE YourTable.[Inv#] IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||3 things for everyone:
1st: Thanks for the great replies
2nd:
UPDATE Transactions
SET InvNo = ( SELECT MAX( InvNo ) + 1
FROM Transactions t1 )
WHERE InvNo IS NULL ;
This worked for me but what i thought would happen with this is that once it
updated the first null and made it a 3 the 2nd null would become a 4. Can
anyone explain please?
3rd:
This same query didn't work in an access database it said that this wasn't
an updateable query. any ideas?
thanks again,
rodchar
"Anith Sen" wrote:

> If this is your entire table, you might need to clean it up to include a
> key. Otherwise,
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# ) + 1
> FROM tbl t1 )
> WHERE Inv# IS NULL ;
> --
> Anith
>
>|||>> This worked for me but what i thought would happen with this is that once
I am not sure what to explain, since the code is simple and clear. In case,
you are looking for sequentially incrementing value to replace the NULLs,
you'd have to use a "ranking" mechanism like:
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# )
FROM tbl t1 ) +
( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Name = tbl.Name
AND t1.Descr <= tbl.Descr
AND t1.Inv# IS NULL )
FROM tbl
WHERE Inv# IS NULL ;
Depending on the ranking variations you need, you'll have to adjust the
correlations in the subquery using COUNT(*).
Access has different updateability rules and different UDPATE dialect than
SQL Server. If you are using MS Access, consider posting this question in an
Access forum.
Anith|||i'm sorry for not being clear. your code posting is exactly what i needed. i
was just curious why it didn't make the nulls sequential. Because once it
makes the first null record a 3 wouldn't that be the new MAX(InvNo), and in
turn making the last null value a 4.
just trying to understand how the engine thinks and works. this helped a lot
.
"Anith Sen" wrote:

> I am not sure what to explain, since the code is simple and clear. In case
,
> you are looking for sequentially incrementing value to replace the NULLs,
> you'd have to use a "ranking" mechanism like:
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# )
> FROM tbl t1 ) +
> ( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Name = tbl.Name
> AND t1.Descr <= tbl.Descr
> AND t1.Inv# IS NULL )
> FROM tbl
> WHERE Inv# IS NULL ;
> Depending on the ranking variations you need, you'll have to adjust the
> correlations in the subquery using COUNT(*).
>
> Access has different updateability rules and different UDPATE dialect than
> SQL Server. If you are using MS Access, consider posting this question in
an
> Access forum.
> --
> Anith
>
>|||>> i was just curious why it didn't make the nulls sequential.
With no correlation, the value is generated only once for the entire
dataset. With a correlation, the values are generated for each matching row
in the dataset.
No problem.
Anith|||thank you everyone for the help. this has been very productive.
"Anith Sen" wrote:

> With no correlation, the value is generated only once for the entire
> dataset. With a correlation, the values are generated for each matching ro
w
> in the dataset.
>
> No problem.
> --
> Anith
>
>|||On Fri, 14 Oct 2005 10:09:04 -0700, ari wrote:

>2nd:
>UPDATE Transactions
>SET InvNo = ( SELECT MAX( InvNo ) + 1
>FROM Transactions t1 )
>WHERE InvNo IS NULL ;
>This worked for me but what i thought would happen with this is that once i
t
>updated the first null and made it a 3 the 2nd null would become a 4. Can
>anyone explain please?
Hi ari,
In SQL, all operations are done "at once". At least in theory. In
practice, they will eventuelly, somewhere deep in the engine, be
processed one row at a time, but the DB should behave as if the complete
statement is executed at once.
That's why you can swap columns without temp storage to hold the old
value, like you would in procedural languages:
UPDATE SomeTable
SET A = B,
B = A
WHERE ...
The right-hand B and A both refer to the "old" values (before the
update). The DB can process this internally ion any order it wants, as
long as the result looks as if it was all executed at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

assigning values for variables from ref table

I have variables and values stored in a table in this format

process_id | t_variable | t_value
--
1 | Remote_Log_Server | AUSCPSQL01
...
many such rows

how to assign values to variables in SSIS?

basically i'm looking for SQL equivalent of the following query i currently use to assign values to multiple variables (in a single query)

SELECT
@.varRemoteLogServer=MAX(CASE WHEN [t_variable] = 'Remote_Log_Server' THEN [t_value] END)
,@.varVariable2=MAX(CASE WHEN [t_variable] = 'variable2_name' THEN [t_value] END)
FROM Ref_Table
WHERE process_id=1
Have you tried using Execute SQL task? It may be used to assign values from a result set to a variable.|||This looks similar to what you get with a SQL Server or table based configuration. It has its own way of doing this, but you may want to look into it.

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

assigning values

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust2, null, Desc1
Cust2, null, Desc1
Cust3, null, Desc2
How would I make it:
Cust1, 1, Desc1
Cust1, 1, Desc2
Cust2, 2, Desc1
Cust2, 2, Desc1
Cust3, 3, Desc2
thanks,
rodcharselect name,right(name,1),Desc
from table
This will work for this example
http://sqlservercode.blogspot.com/
"rodchar" wrote:

> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust2, null, Desc1
> Cust2, null, Desc1
> Cust3, null, Desc2
> How would I make it:
> Cust1, 1, Desc1
> Cust1, 1, Desc2
> Cust2, 2, Desc1
> Cust2, 2, Desc1
> Cust3, 3, Desc2
>
> thanks,
> rodchar|||the invoice numbers don't come from the NAME field.
it should find the MAX(InvoiceID) and assign it to all transactions for a
single customer that have a NULL value, then for the next set of transaction
s
for the next customer the Invoice number should be incremented by 1.
CustA, 1, Desc1
CustA, 1, Desc2
CustB, 2, Desc1
CustB, 2, Desc1
CustC, 3, Desc2
"SQL" wrote:
> select name,right(name,1),Desc
> from table
> This will work for this example
> http://sqlservercode.blogspot.com/
> "rodchar" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor -- the keys have nothing whatsoever to do with the display in
the front end.
Let me fix those horrible data element names with some guesses. The
only possible key is (cust_name, item_description), while silly me, I
would have thought that invoice_nbr would be unique in an invoice
table.
What you posted does not make any sense.|||Sorry about that.
Alright, let me give a quick 30,000 ft view because my logic could be off
(it has been before many times.)
I'm trying generate invoices from a transactions table. so here's what i do.
i enter all the transactions for the month for all the customers. when
invoice time comes around here are my steps:
1. I assign invoice numbers to each record in the transactions table where
InvoiceID IS NULL.
2. Then I insert the records into the invoice headers table and invoice
details table.
Transactions table:
rowID int primary key
CustomerID int
InvoiceID int
TransactionType (bill,payment)
Cost money
so my records look like this
1, 122, null, bill, $20
2, 122, null, bill, $20
3, 105, null, bill, $20
4, 101, null, bill, $20
5, 102, null, bill, $20
now if my logic is sound
my transactions table will look like the following after assigning invoice
numbers to them:
1, 122, 1, bill, $20
2, 122, 1, bill, $20
3, 105, 2, bill, $20
4, 101, 3, bill, $20
5, 102, 4, bill, $20
Please advise. Is there a more clearer way to do this whole process that i'm
missing.
thanks,
rodchar
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless. If you want an ordering, then you need to havs a column
> that defines that ordering. You must use an ORDER BY clause on a
> cursor -- the keys have nothing whatsoever to do with the display in
> the front end.
> Let me fix those horrible data element names with some guesses. The
> only possible key is (cust_name, item_description), while silly me, I
> would have thought that invoice_nbr would be unique in an invoice
> table.
> What you posted does not make any sense.
>

Assigning multiple values to a parameter in stored procedure

Hi:
How do you write a SQL statement in a stored procedure so
that it will allow you to assign multiple values to a
parameter?
For instance in this example below, depending on what the
users select on the front-end application, the values
assign can be one customer id value or multiple customer
id values:
Create procedure dbo.SP_Test
As @.CustID varchar(3)
Select * from tblCust where customerid = @.CustID
Please help!This is probably not the best solution but it should work
create procedure sp_test
@.cust_id varchar(50)
as
set nocount on
exec ('select * from tblcust where customerid in (' + @.cust_id +')')
go
This procedure would be called as sp_test '50' for one cust_id or sp_test
'50, 55, 100' for several cust_id's

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?
Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign T-SQL variables in dynamic SQL statement?

I have a dynamic SQL statement in which I need to assign values to
variables.
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC(@.querystring)
This doesn't work as it doesn't assign values to @.rows and @.pages that can
be accessed within the stored procedure.
Any suggestions?Use sp_executesql with output parameters:
DECLARE @.rows INT
DECLARE @.pages INT
DECLARE @.querystring NVARCHAR(300)
SELECT @.querystring = 'SELECT @.rows = COUNT(*)
@.pages = COUNT(*) / @.perpage
FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
EXEC sp_executesql
@.querystring,
N'@.rows INT OUTPUT, @.pages INT output, @.perpage INT',
@.rows OUTPUT, @.pages OUTPUT, @.perpage
PRINT @.pages
PRINT @.rows
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:eNR78M7uFHA.3256@.TK2MSFTNGP09.phx.gbl...
> I have a dynamic SQL statement in which I need to assign values to
> variables.
> SELECT @.querystring = 'SELECT @.rows = COUNT(*)
> @.pages = COUNT(*) / @.perpage
> FROM utbl' + CAST(@.tableid AS VARCHAR(15)) + ' WITH (NOLOCK)'
> EXEC(@.querystring)
> This doesn't work as it doesn't assign values to @.rows and @.pages that can
> be accessed within the stored procedure.
> Any suggestions?
>

Assign several different sets of paramters, to be chosen by user?

Hi,

Is there a way to put in several different sets of values for the defaults in parameters, where the user could then choose which set of defaults they want?

For example, I have two drop-downs for dates: Start Date and End Date. I would also like another drop-down (or button, or whatever) called, say "Interval". It would have choices such as "1 week interval" or "52 week interval", and whatever is chosen would set the Start and End Dates to whatever default values I had assigned to the parameters in "Interval".

It's not quite the same as cascading parameters. Any suggestions would be greatly appreciated! Thanks.Sorry, but this is cascading parameters. One parameter which pulls of the data from the server and fills the other parameters afterwards, thats truly cascading.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Assign multiple values using CASE in a Select Statement

Hello All,
I have a need to assign the values of the three variables in one select
statement. Currently, this is done using three
different Select statements :
Select @.Male = SMnemonic from sex where SName = 'Male'
Select @.Female = SMnemonic from sex where SName = 'Female'
Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
I would like to replace with just one Select statement. I thought that this
is simple, however, I'm getting vague results :
NULL
(1 row(s) affected)
NULL
(1 row(s) affected)
Unknown Mnemonic is : U
(1 row(s) affected)
Thanks,
Gopi
CREATE TABLE [dbo].[Sex] (
[SCode] [char] (10) ,
[SName] [varchar] (50),
[SMnemonic] [char] (10)
)
GO
Select * from Sex order by SCode
SCode SName SMnemonic
-- ---- --
1 Male
M
2 Female
F
3 Unknown
U
4 Not Known
NK
Select SMnemonic from sex where SName = 'Male'
Select SMnemonic from sex where SName = 'Female'
Select SMnemonic from sex where SName = 'Unknown'
Declare @.Male char(10)
Declare @.Female char(10)
Declare @.Unknown char(10)
Set @.Male = 'Junk'
Set @.Female = 'Junk'
Set @.Unknown = 'Junk'
Select @.Male = CASE SName
WHEN 'Male' THEN SMnemonic
END ,
@.Unknown = CASE SName
WHEN 'Unknown' THEN SMnemonic
END ,
@.Female = CASE SName
WHEN 'Female' THEN SMnemonic
END
from Sex
WHERE SName IN ('Male','Female','Unknown')
Select 'Female Mnemonic is : ' + @.Female
Select 'Male Mnemonic is : ' + @.Male
Select 'Unknown Mnemonic is : ' + @.UnknownTry,
Select
@.Male = case when SName = 'Male' then SMnemonic else @.Male end,
@.Female = case when SName = 'Female' then SMnemonic else @.Female end,
@.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
from
sex;
AMB
"rgn" wrote:

> Hello All,
> I have a need to assign the values of the three variables in one select
> statement. Currently, this is done using three
> different Select statements :
> Select @.Male = SMnemonic from sex where SName = 'Male'
> Select @.Female = SMnemonic from sex where SName = 'Female'
> Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
> I would like to replace with just one Select statement. I thought that thi
s
> is simple, however, I'm getting vague results :
> --
> NULL
> (1 row(s) affected)
> --
> NULL
> (1 row(s) affected)
> --
> Unknown Mnemonic is : U
> (1 row(s) affected)
>
> Thanks,
> Gopi
> CREATE TABLE [dbo].[Sex] (
> [SCode] [char] (10) ,
> [SName] [varchar] (50),
> [SMnemonic] [char] (10)
> )
> GO
> Select * from Sex order by SCode
> SCode SName SMnemonic
> -- ---- --
> 1 Male
> M
> 2 Female
> F
> 3 Unknown
> U
> 4 Not Known
> NK
>
> Select SMnemonic from sex where SName = 'Male'
> Select SMnemonic from sex where SName = 'Female'
> Select SMnemonic from sex where SName = 'Unknown'
>
> Declare @.Male char(10)
> Declare @.Female char(10)
> Declare @.Unknown char(10)
> Set @.Male = 'Junk'
> Set @.Female = 'Junk'
> Set @.Unknown = 'Junk'
>
> Select @.Male = CASE SName
> WHEN 'Male' THEN SMnemonic
> END ,
> @.Unknown = CASE SName
> WHEN 'Unknown' THEN SMnemonic
> END ,
> @.Female = CASE SName
> WHEN 'Female' THEN SMnemonic
> END
> from Sex
> WHERE SName IN ('Male','Female','Unknown')
> Select 'Female Mnemonic is : ' + @.Female
> Select 'Male Mnemonic is : ' + @.Male
> Select 'Unknown Mnemonic is : ' + @.Unknown
>
>|||Alejadro,
Thanks a Million. I see the problem. Since ELSE part is missing it is
assigning NULLs and the reason why the last
variable, in this case Unknown, retains the value.
Gopi
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BB446702-10C6-47D3-91F7-3C6E9FD1D4FC@.microsoft.com...
> Try,
> Select
> @.Male = case when SName = 'Male' then SMnemonic else @.Male end,
> @.Female = case when SName = 'Female' then SMnemonic else @.Female end,
> @.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
> from
> sex;
>
> AMB
> "rgn" wrote:
>

aspnet_regsql.exe - cannot insert null values

Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errors

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.

------------
Details of failure
------------

SQL Server:
Database: [AddressVerification]
SQL file loaded:
InstallCommon.sql

Commands failed:

CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)

INSERT INTO #aspnet_Permissions
EXEC sp_helprotect

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions

SQL Exception:
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??

** I am logged in with full admin privaliges

tia

Mark.

This is because the preinstalled user defined data type sysname by default does not allow nulls--that means column of sysname data type won't accept nulls unless you explicitly set the column to allow null in column definition. So if the sp_helprotect stored procedure returns some rows with nulls in the [column] field (this is an rare issue but it does exist), the insert command will fail. So here I have to say it is a flaw of the aspnet_regsql utility.|||

I wrote a simple script to update the system table systypes to make sysname data type to accept NULLs. However the script is not fully tested and since it modifies system table directly, I don't recommend to use it and you need to consider the potential risk when using it. If some issue is caused by the script, please pass 'NoNulls' as parameter to change the systypes back to default:

CREATE PROCEDURE usp_KillUsers
@.p_DBName SYSNAME = NULL
AS

/* Check Paramaters */
/* Check for a DB name */
IF (@.p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@.p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@.p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied

SET NOCOUNT ON

/* Declare Variables */
DECLARE @.v_spid INT,
@.v_SQL NVARCHAR(255)

/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.p_DBName

OPEN c_Users

FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.v_SQL = 'KILL ' + CONVERT(NVARCHAR, @.v_spid)
-- PRINT @.v_SQL
EXEC (@.v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @.v_spid
END -- While

CLOSE c_Users
DEALLOCATE c_Users

CREATE PROCEDURE sp_SetSysnameAllowNulls @.Operation varchar(10)='AllowNulls'
AS
BEGIN
DECLARE @.cmd nvarchar(4000)
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
EXEC sp_msforeachdb N'use[?];
IF (DATABASEPROPERTY(db_name(),''IsReadOnly'')=1)
BEGIN
declare @.dbname sysname;
set @.dbname=db_name();
use master
EXEC usp_KillUsers @.dbname;
EXEC sp_dboption @.dbname,''read only'',''false'';
END'

IF (@.Operation = 'AllowNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status-1
WHERE name=''sysname'' AND (status&1)=1'
ELSE
IF (@.Operation = 'NoNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status+1
WHERE name=''sysname'' AND (status&1)=0'
ELSE RAISERROR('Expected @.Opertaion=''AllowNulls'' or ''NoNulls''',16,1)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
END

ASP.net SQL Problem

Hello,

I have as exemple these values into my database:
ruiter paard punten
1 1 2
1 1 10
2 1 4
1 1 10

i want the second highest value of "punten" grouped by ruiter and paard

this is what i have got so far:
SELECT u.ruiter,u.paard,(SELECT max(TOP 2 punten) FROM uitslag where ruiter = u.ruiter and paard = u.paard) AS points1 FROM uitslag u group by u.ruiter,u.paard"

when i put this code into my asp.net page i get errors

Someone any ideas how i can fix this??
When i can get it to work for the second highest value. I then can work further for the third, fourth,... highest value.

tnx alot
PhilippeUse Query Analyser, that SQL just isn't SQL. It will give you better hints as to why it's wrong

ASP.NET and SQL Server Help

I was using this code yesterday on my SQL Server at home to insert values and everything was working fine.

Public Function doInsertPrimary(ByVal iState As String)
 Dim sqlConnect As String
 sqlConnect = "server = mySRVR; database = myDB; uid = sa; password = somepass"
 Dim oConnection As New SqlClient.SqlConnection _
 (sqlConnect)
 Dim sqlString As String
 sqlString = "INSERT INTO myTable VALUES (" & iState & ")"
 Dim oCommand As New SqlClient.SqlCommand(sqlString, oConnection)
 oConnection.Open()
 Try
 oCommand.ExecuteNonQuery()
 Finally
 oConnection.Close()
 End Try
 End Function

Today, when I do the insert on a new SQL Server, it is inserting the same values 5 times. I am not sure whether the problem is in my code or on the server. Can someone let me know where they think the problem is at?It's not your function. That's pretty basic. Somehow, it's getting called 5 times. Since it's not a stored procedure, there's no way there's some goofy T-SQL. I've found it's usually something with your page processing order. Button click calls a sub, but it's called in the page load, but that fires another one and you end up with multiple calls when you want one. Enable tracing, put a trace statement in yor function and see how many times it's called. Then try to figure out what's calling it multiple times.|||Here is something interesting that I forgot to mention:

I am running Virtual PC on the same laptop as .NET with Windows 2003 and Sql Server 2000 on it. Here at work, I have a second laptop as well. When I execute the same code on the remote laptop, everything executes perfectly. Hence, the code is fine. I enabled tracing, and the code is only being called once. Anyone know anything about this?