Use Result Value From SELECT As Arg For New SELECT?

Apr 21, 2008

 Hi -
 I'm stuck and would really appreciate some help with my stored procedure. I think I am probably really close, but I'm just missing something basic as I'm still fairly new to SQL. Here are the things I need this procedure to do...
 
Query one table with a text string stored in the field called "UserName".  (WHERE cs_Users.UserName = @userName)


Use the UserId value found in the single row that should be returned by this query to execute a second query on a different table. (WHERE traveler_stories.UserID = cs_Users.UserID)

This second query returns all rows that match this UserId and are approved.
Execute the paging logic to return the specified paged result set.
 
Much of this code is already working in other stored procedures. I think I just need to figure out how to use the cs_Users.UserID returned by the first SELECT statement. At present I get the following error which I don't understand "The multi-part identifier "cs_Users.UserID" could not be found." when I try to save the stored procedure. I have pasted the code below. Thanks so much!
 Peter
 

@userName nvarchar(256),
    @PageSize int,
    @PageNumber int,
    @total int = 0 OUTPUT
   
    AS
   
    Declare @RowStart int
    Declare @RowEnd int
   
   
    if @PageNumber > 0
    Begin
   
    SET @PageNumber = @PageNumber -1;

    SET @RowStart = @PageSize * @PageNumber + 1;
    SET @RowEnd = @RowStart + @PageSize - 1 ;
   
    SELECT * FROM cs_Users
    WHERE cs_Users.UserName = @userName;

    WITH T1 As ( SELECT traveler_stories.StoryTitle,
         traveler_stories.PostDate,
         traveler_stories.UserID,
         traveler_stories.City,
         traveler_stories.Country,
         traveler_stories.Tags,
         traveler_stories.StoryID,
         traveler_stories.Approved,
         ROW_NUMBER() OVER (order by traveler_stories.PostDate) as RowNumber
         FROM traveler_stories WHERE traveler_stories.Approved = 1 AND traveler_stories.UserID = cs_Users.UserID)
       
        SELECT *
        FROM T1
        WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd;
        SET @total = @@rowcount;
     


    END
 

View 3 Replies


ADVERTISEMENT

Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into

Jul 9, 2002

When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks

View 1 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Sum Result Of A Select

Jun 21, 2007

In the database i have a table with this information:key_id =1key_desc =43+34+22+12I want sum the values in key_desc. Something like:SELECT key_desc FROM tableBut the result of the select was "111" and not "43+34+22+12".Is this posible?

View 6 Replies View Related

How To Do A SELECT On SP Result

May 10, 2006

Hi there,

Problem:

Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.

Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:

SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1 

Is there a way to realize this ?

 

Thx for helping

View 5 Replies View Related

Select All = No Result

Jan 30, 2007

hey there

I have parameters in my report set up

CallStatus = string

Available Values are non-queried and values are listed in the value list

closed, open, pending, select all

If I choose each one separately I get a result - if I choose select all I get no result.

This worked fine attaching to another datasource - I have now attached it to a new source.

Tried altering the relationship to properties to a left outer join but this made no difference.

Can someone tell me what else I should check?

thanks

View 6 Replies View Related

Conflict In SQL Select Result S

Jan 21, 2002

Hi,

I ran a sql somedays ago it gave me result, I ran the same sql today it gave different result. The only difference is that the particular table in the sql is frequently used table with lots of new insert. But the sql I ran is not selecting the new rows, it only deals with already inserted records.

Any idea what will make the sql to give different result from same set of records in different time.

Thanks
John Jayaseelan

View 3 Replies View Related

Select 'static' Row/result

Oct 17, 2006

Let's say I have a simple query to return the results of my "Status" table. The query reads as follows:


Code:


Select statusID, statusName
From Status



Here is the result set that I am returned:


Code:


22 Associate Member
23 Is Not Paying
24 Exempt
25 Fair Share
26 Member
29 Retiree
30 Staff
32 Fair Share - Self Pay
34 Member - Self Pay



Now, I am using this query for reporting purposes and would like to inject some additional sql that will append one additional row to my result set -- this is what I am calling the 'static' row in the thread title.

In other words, without modifying my database I would like to return the above set of data but with one additional row that has an arbitrary ID with the name "Unknown" or something similar.

again, I want to avoid adding an "Unknown" field directly to my database -- is their any way to "hard code" the selection of this row in my sequal?

Thanks,

Zoop

View 1 Replies View Related

Add Index To Select Result

Mar 20, 2007

Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?

thank you
eliko"

View 1 Replies View Related

Different Result Of Each Select Statement

Mar 14, 2008

Hi, I execute a select statement

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'

every times I execute it I get different result.

1st result : 17036986
2nd result : 17037903
3rd result : 17038309

Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.

View 18 Replies View Related

How Do I Select Last 10 Rows Of The Result Set??

Jun 13, 2006

Can we select "last 10 rows" from the result set. (sql server 2000)

Something of an opposite of "top" functionality

View 3 Replies View Related

Get Numeric Result From SELECT Statement

Jun 11, 2008

Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);

View 3 Replies View Related

Using The Result Of A SELECT As A Vaule To UPDATE With

Jun 13, 2005

Hi AllI hope some1 with more experience of Sql Server 2000 Stored proc's can help me or point me to somewhere I can find the info cos at the moment it's doing my head in, I have spent 2 day,s so far trying to solve the following query, the problem is I do not understand the errors I am getting or why so here is the code that won't work
ALTER PROCEDURE  dbo.UpdateStock
@OrderID intASSELECT    (dbo.Products.Stock - dbo.OrderDetails.Quantity) AS NewStockFROM         dbo.OrderDetails INNER JOIN dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductIDWHERE     (dbo.OrderDetails.OrderID = @OrderID)                          UPDATE    dbo.Products                           SET  dbo.Products.Stock = NewStockWhat I am trying to do is get the OrderDetails.Quantity form the  OrderDetails table the UPDATE  the Stock feild in the Products Table by deducting the  OrderDetails.Quantity  From the Products.Stock  to give me the new stock levelWell it works to an extent in that if I do this to  SET  dbo.Products.Stock = 10 it will put 10 in the Stock feild but I am unabke to get the value of  NewStock , which caluculates the correct value into the set statment I keep getting invalid colum name, I have tried any combination I can think of but I still get error of 1 sort or another.So if any1 could help as I am pulling my hair out with this 1So perhaps some1 could cast there eye over it pleaseThanks

View 2 Replies View Related

How To Encrypt Result Of Select Statement

Jun 6, 2012

I want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?

The original data is from an MS SQL database to be transferred to a PostgreSQL database.

MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)

I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.

View 2 Replies View Related

SELECT Result Without Index Sorting

Mar 16, 2004

I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.

View 14 Replies View Related

SELECT Result In Inserted Sequence

Mar 16, 2004

I have one table with primary key index, when i want to select the result from this particular table, i wish the result arrange by record inserted squence and not by the primary key i had been set.

View 4 Replies View Related

Saving Select Result To A Table

Jun 21, 2014

I have the select statement below - where I use some external functions - and I would like the result to be saved to another table.After executing the statement I end up with a table containing the following columns:

accountno
d.date_start
d.date_end
TWRR_Month
TWRR_Cum

Normally I would use the command 'INTO TableName' but since the statement is so long I don´t know where to place it.The select statement is as follows:

IF OBJECT_ID('tempdb..#trn') IS NOT NULL
DROP TABLE #trn

IF OBJECT_ID('tempdb..#mv') IS NOT NULL
DROP TABLE #mv
SELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn,
CASE PTR_TAC
WHEN 'BUY' THEN 0
ELSE PTR_LOCALAMT
END as amt_trn

[code]...

View 2 Replies View Related

Assign SELECT Result To Variable

Jun 7, 2007

Hi this is probably a very stupid question, but I still need to know.

How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?

View 3 Replies View Related

Select Distinct - Incorrect Result

Jul 20, 2005

I have a function that is designed to return a variable that containsconcatenated values from a partinular field in the returned rows:DECLARE @output varchar(8000)SELECT@output =CASEWHEN @output IS NULL THEN CAST(TSD.ScheduledTime ASvarchar(4))ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime ASvarchar(4)),'')ENDFROM TSDWHERE ClientGUID = 2000001447020001 ANDParentGUID = 6000006684068001Select @outputThe variable returned with this code contains:"1200, 1400, 1200, 1400"I want to only get the unique values so that the variable returns "1200,1400". Seems simple enough just to add DISTINCT to the SELECT statement.However, what is returned is simply "1400".I cannot figure out why that is the case. Is there any explanation to thisresult?Side note: I can work around this by using a cursor but I would like toknow why DISTINCT does not work.Many thanks in advance for any help that can be provided!Pat

View 5 Replies View Related

How To Select In From A Store Procedure Result?

Feb 26, 2007

HI, I'm a simple store procedure that returns a result such as this one:

AM
AM-1
AM-2
AM-n

and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile

select fields from table where id in (execute sp)

how can I make this?

Thanks a lot.

View 4 Replies View Related

Storing The Result Of A Select TOP 1 Into A Variable

Sep 7, 2007

hello

Does anybody know how to store the result of a select top 1 into a variable??

I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc


And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc

But none of them work


Any ideas??
Thanks

View 1 Replies View Related

Working On The Result Set Of Select Query

Sep 20, 2007



Hi
I have a table as follows

Table Cats
{

catergory varchar(20)
Update datetime
}


And the data in the table is as follows

Category Update
------------- --------------
cat1 d1
cat2 d2
cat3 d3

I would like to get only 'Category' in result set and work on it ( similar to foreach in C# )

select Category from Cats will return the required result , but how can i iterate thru then in T-Sql
Can any one please throw some light

Thank you
~Mohan Babu


View 1 Replies View Related

Can You Check My Select Max Query ? The Result Not Corret...

Aug 1, 2007

in table Databackup
company       keepmonth
-----------------  -------------------
001                 12002                 12003                  6005                  607917              609747              6
 
I run this query
select Max(keepmonth) as keep from Databackup
why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6
does my query error?
thank you

View 1 Replies View Related

Aggregate Function For Select Statement Result?

Oct 19, 2004

Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.

currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.

Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.

*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales


This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.

thanks,
Tom Anderson
Software Engineer
Custom Business Solutions

View 3 Replies View Related

How To Use Variable To Receive The Result Of A Select Statment. ?

Jun 21, 2002

Good morning;

My Problem is :im my transaction i use insert code like the following :

" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"

In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.

" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "

I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.

Thinks.

View 1 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

an example for the pb
1)First i have created a dynamic cursor :

DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor

2)The result for this cursor is for expamle 'USA'.

3) If now i do an update on that location with a new value 'USA1'

update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7

4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).

If i remove DISTINCT from the cursor declaration , the process works fine .

View 10 Replies View Related

Return A Result Set From A SELECT Query In A Function?

Jan 21, 2008

Hello all:

How can I return the result of a SELECT statement from a stored procedure (function)?

CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN

// Is this right??
RETURN SELECT * FROM authors

END


Thanks!

View 12 Replies View Related

Row Count Returned By A Select Query In Result

Jul 30, 2013

I want to show the number of rows returned by a select query in the result.

e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy

If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result

OutPut

0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..

How can i get this number in the result.

View 3 Replies View Related

Select Query - How To Get Result Based On Given Table

Sep 14, 2014

Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -

Result should be -

ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two

Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three

select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing

View 2 Replies View Related

Create Table Structure From Select Result

Dec 21, 2006

Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null

declare @1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,
@9 int,@10 int,@11 int,@12 int,@21 int,@22 int,@23 int,@24 int,@25 int,
@26 int,@27 int,@28 int,@29 int,@30 int,@31 int,@32 int

set @21=(select count(*) from j9a whereIngestion=1)
set @22=(select count(*) from j9a whereInhalation/nasal=1)
set @23=(select count(*) from j9a whereAspiration=1)
set @24=(select count(*) from j9a whereOcular=1)
set @25=(select count(*) from j9a whereDermal=1)
set @26=(select count(*) from j9a whereBite=1)
set @27=(select count(*) from j9a whereParenteral=1)
set @28=(select count(*) from j9a whereOtic=1)
set @29=(select count(*) from j9a whereRectal=1)
set @30=(select count(*) from j9a whereVaginal=1)
set @31=(select count(*) from j9a whereOther=1)
set @32=(select count(*) from j9a whereUnknown=1)

Create table table9(Route varchar(30),Fatal int)
insert into table9 values ('Route_Ingestion',@1,@21)
insert into table9 values ('Route_Inhalation',@2,@22)
insert into table9 values ('Route_Aspiration',@3,@23)
insert into table9 values ('Route_Ocular',@4,@24)
insert into table9 values ('Route_Dermal',@5,@25)
insert into table9 values ('Route_Bite',@6,@26)
insert into table9 values ('Route_Parenteral',@7,@27)
insert into table9 values ('Route_Otic',@8,@28)
insert into table9 values ('Route_Rectal',@9,@29)
insert into table9 values ('Route_Vaginal',@10,@30)
insert into table9 values ('Route_Other',@11,@31)
insert into table9 values ('Route_Unknown',@12,@32)

select * from table9

The exRoute result is like this
70 Ingestion
71 Inhalation
72 Aspiration
73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal

The above giving the errors
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Inhalation'.

Thanks in advance

View 1 Replies View Related

Select Query Result In Excel Sheet

Mar 11, 2008

hi all
how can i put select query result in excel sheet.
can any one help me

Regards
js.reddy

View 2 Replies View Related

Transact SQL :: How To Select Result From OPENQUERY Into A Variable

Sep 23, 2015

I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?

DECLARE    @HISTORY_ID        INT,
        @NSTATUS    VARCHAR(20),
        @IMPORT_DATE    DATETIME,
        @TSQL            VARCHAR(8000);
SET        @HISTORY_ID = 350721;
   SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')'
   SELECT @TSQL, @HISTORY_ID;
   EXEC (@TSQL) ;

View 7 Replies View Related

SELECT DISTINCT Will Always Result In A Static Cursor

Jul 31, 2007

An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste CURSOR DYNAMIC
FOR Select DISTINCT name from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .


any idea how to make a select distinct result in a dynamic Cursor?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved