Multiple values int textbox...

Hi,
I need to display the result set in a textbox,
if there are multiple rows i need to show them as comma separated..
Can anyone give me idea of how it can be achieved..
Thanks in advance,I can't think of an easy way.
You need to get the resultset concatanating, not do it in report layout.
You could use a cursor (effectively a loop) in the SQL query - get the
result set and concatanate the rows into a variable and output the
variable as the result set. Cursors are quite slow though.
You could do the same thing in VB code using ADO if that's your thing.
Chris
CCP wrote:
> Hi,
> I need to display the result set in a textbox,
> if there are multiple rows i need to show them as comma separated..
> Can anyone give me idea of how it can be achieved..
> Thanks in advance,sql

Multiple Union

Anyone point know a more elegant solution to the following?
I have 2 tables
Events and Locations
Events structure is
EventID int
EventName varchar
EventLeader int
LocationID int
Locations consists of
LocationID int
Location1 varchar
Location2 varchar
Location3 varchar
.
.
Location20 varchar
I want to be able to get 1 column containing all locations
for a particular EventID like
Location
Boston
.
.
Seattle
NewYork
The way I found is having a SELECT location1 ..UNION
SELECT location2 all the way to SELECT location20
Is there a better way?"michael.obrien@.DelThiSul.ie" <anonymous@.discussions.microsoft.com> wrote in
message news:57ef01c42d34$2e05fc30$a501280a@.phx.gbl...
> Anyone point know a more elegant solution to the following?
> I have 2 tables
> Events and Locations
> Events structure is
> EventID int
> EventName varchar
> EventLeader int
> LocationID int
> Locations consists of
> LocationID int
> Location1 varchar
> Location2 varchar
> Location3 varchar
> .
> .
> Location20 varchar
> I want to be able to get 1 column containing all locations
> for a particular EventID like
> Location
> Boston
> .
> .
> Seattle
> NewYork
> The way I found is having a SELECT location1 ..UNION
> SELECT location2 all the way to SELECT location20
> Is there a better way?
Well you could store the locations in a seperate table, or use a
table-values UDF.
Like this:
create table location(
LocationID int,
Location1 varchar(20),
Location2 varchar(20),
Location3 varchar(20))
go
alter function get_locations(@.LocationID int)
returns @.locations table (Location varchar(20))
as
begin
declare @.Location1 varchar(20)
declare @.Location2 varchar(20)
declare @.Location3 varchar(20)
select
@.Location1 = Location1,
@.Location2 = Location2,
@.Location3 = Location3
from location
where locationID = @.LocationID
if @.Location1 is not null
insert @.locations values(@.Location1);
if @.Location2 is not null
insert @.locations values(@.Location2);
if @.Location3 is not null
insert @.locations values(@.Location3);
return;
end
go
insert location(locationid, location1,location2) values
(1,'Detroit','Houston')
select * from get_locations(1)
David

Multiple Table Problem

Hi and Thanks to all in advance for help,
I have 2 tables. What we want to do is grab the columns listed and then
if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
items that have ONHAND(int) in the other table (STOCK) is greater than
0.
SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
KEYWORDITEMS
WHERE
STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
AND
STOCK.WH = 1
AND
(Here is the confusion)
What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
I attempted to use CASE statements but it was not working because it
wanted a result if the condition was false. I am sure I didn't do it
right though.
Other things I am trying to do:
CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only
the returned result that is to become the new table) = "BEST DEAL" ELSE
''.
I would like to drop all STOCK columns after it is returned except
[DESCRIPTION] and [STNS]
Thanks again for your help.
Kelly"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
> the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
>
Try:
AND (ProductGroupNumber < 4999 OR Stock.OnHand >0)
JBK|||How are the two tables related? What does their DDL (create table) statement
s
look like?
Thomas
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
the
> PRODUCTGROUP(int) column is larger than 4999 we only want to grab items th
at
> have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD, STOCK.ITEMNUMB
ER,
> STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK, KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it wante
d a
> result if the condition was false. I am sure I didn't do it right though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only the
> returned result that is to become the new table) = "BEST DEAL" ELSE ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>|||I don't understand exactly what you are trying to do. Part of my confusion
is caused by your incomplete select statement. Take a look at this example
that I wrote and see if it gets you pointed in the right direction.
USE Northwind
GO
SELECT A.OrderID, A.CustomerID, A.EmployeeID,
A.OrderDate, A.RequiredDate, A.ShippedDate,
'EmployeeORShip' = CASE WHEN A.ShippedDate IS NULL THEN 'emp-' +
C.FirstName + ' ' + C.LastName ELSE 'shp-' + B.ContactName END
FROM Orders A
JOIN Customers B ON A.CustomerID = B.CustomerID
JOIN Employees C ON A.EmployeeID = C.EmployeeID
Keith
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
> the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it
> wanted a result if the condition was false. I am sure I didn't do it right
> though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only the
> returned result that is to become the new table) = "BEST DEAL" ELSE ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>|||Try,
SELECT
PRODUCTGROUPNUMBER,
ITEMNO,
STOCK.[DESCRIPTION],
KYWD,
STOCK.ITEMNUMBER,
STOCK.WH,
STOCK.ONHAND,
STOCK.STNS
FROM
STOCK
inner join
KEYWORDITEMS
on STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO AND STOCK.WH = 1
WHERE
(STOCK.ONHAND > 0 and PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999)
or
PRODUCTGROUP.PRODUCTGROUPNUMBER <= 4999
AMB
"scorpion53061" wrote:

> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then
> if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than
> 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it
> wanted a result if the condition was false. I am sure I didn't do it
> right though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only
> the returned result that is to become the new table) = "BEST DEAL" ELSE
> ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>
>|||I love what you did here though I have run the query for 10 minutes and
it is still running.
Here is what I have now with the corrected table names and fields....
SELECT
DISTINCT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM
STOCK2
inner join
KEYWORDITEMS11
on STOCK2.NUMBER = KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE
(STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
or
KEYWordITEMS11.PRODUCTGROUP <= 4999
ORDER By PRODUCTGROUP, ITEMNO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message news:AlejandroMesa@.discussions.microsoft.com:
> Try,
> SELECT
> PRODUCTGROUPNUMBER,
> ITEMNO,
> STOCK.[DESCRIPTION],
> KYWD,
> STOCK.ITEMNUMBER,
> STOCK.WH,
> STOCK.ONHAND,
> STOCK.STNS
> FROM
> STOCK
> inner join
> KEYWORDITEMS
> on STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO AND STOCK.WH = 1
> WHERE
> (STOCK.ONHAND > 0 and PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999)
> or
> PRODUCTGROUP.PRODUCTGROUPNUMBER <= 4999
>
> AMB
>
> "scorpion53061" wrote:
>|||You might get a better perf if you convert your "DISTINCT" and "OR" to
"UNION" query.
e.g.
SELECT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM STOCK2 inner join KEYWORDITEMS11 on STOCK2.NUMBER =
KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE (STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
UNION
SELECT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM STOCK2 inner join KEYWORDITEMS11 on STOCK2.NUMBER =
KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE KEYWordITEMS11.PRODUCTGROUP <= 4999
ORDER By PRODUCTGROUP, ITEMNO
-oj
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:%23BTFhOBTFHA.2872@.TK2MSFTNGP14.phx.gbl...
>I love what you did here though I have run the query for 10 minutes and it
>is still running.
> Here is what I have now with the corrected table names and fields....
> SELECT
> DISTINCT
> PRODUCTGROUP,
> ITEMNO,
> STOCK2.[DESCRIPTION],
> KYWD,
> STOCK2.NUMBER,
> STOCK2.WH,
> STOCK2.ONHAND,
> STOCK2.STNS
> FROM
> STOCK2
> inner join
> KEYWORDITEMS11
> on STOCK2.NUMBER = KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
> WHERE
> (STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
> or
> KEYWordITEMS11.PRODUCTGROUP <= 4999
> ORDER By PRODUCTGROUP, ITEMNO
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:AlejandroMesa@.discussions.microsoft.com:
>

multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?

Multiple Row Update/Insert

Hi,
I have a a table that is primarily a linking table that contains values for categories
indid int Indicator ID
indtype int Indicator Type can be either 0 or 1
catflagnum int Category Flag Number the number of the Category
catflagvalue int Cat Flag Value The Value for that category
this table can then be updated from a web form.
The Question I have is that can I do this in one statement or do I have to do it one at a time
i.e
The Data set could look something like
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 1 'This value will change
Catflagvalue = 1 'This value will change
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 2 'This value will change
Catflagvalue = 3 'This value will change
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 3 'This value will change
Catflagvalue = 1 'This value will change
indid = 3 'This value will be the same for all rows
indtype = 0 'This will be the Same for all rows
Catflagnum = 4 'This value will change
Catflagvalue = 5 'This value will change
A further complication is that in the table an entry may not be in that table for the category so we would possibly have to check if the record exist or do an insert
I am stumped on this one?

I would say pass all the parameters to a stored proc and inside your stored proc you can branch out as :
IF EXISTS ( SELECT * FROM <table>)
BEGIN
-- Record already exists so you need to UPDATE
END
ELSE
BEGIN
-- You need to do an INSERT
END
Is there any particular reason you are trying to stick to one sql stmt ? You can still get all this done in one trip to the database if thats what you are worried about..|||

I have that sort of T-SQL sorted out, that's not a problem.

Basically what I wanted to do as , the requirement states for me to do is ,

There are a load of drop downlist boxes on a Page , that are basically choices the user can make , etc,

then I need to update the record in , when a user clicks a submit button, so I needed to iterate through all the controls and submit thier values to a

I was wondering if I could do it in one trip, i.e Pass all the parameters to a stored proc, and do it in one batch
at the moment I am doing it in a loop that loops through the controls, and submits thier Data One By One, I was wondering If I could do it in one trip as opposed to 12
if that makes sense?

|||

I was wondering if I could do it in one trip, i.e Pass all the parameters to a stored proc, and do it in one batch
at the moment I am doing it in a loop that loops through the controls, and submits thier Data One By One, I was wondering If I could do it in one trip as opposed to 12
if that makes sense?

Yes. It actually depends on your database structure but for the part you posted above I would think you could pass in all the parameters to a stored proc..write out all your logic there and do the insert/update..all in one trip.

|||

Try the link below for sp_executesql it will enable you run more than one statement. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp

|||

Thanks for both of those solutions,

I did learn from the sp_executesql , that will be a solutiuon for another part of the same project, thanks for that.

As for the other solution of passing all my Parameters to a stored proc, I don't like this idea, as it would mean I am passing 24 Parameters to my stored proc, and the coding implications for doing that are not cool. There must be a more elegant solution than that.