I've created a Stored Procedure which performs a Select against my table, and displays the rows returned via these stmts - @RowCount int Output SELECT @rowcount = @@RowCount This Works fine when Executed from SQL Server, but when trying to invoke the SP from my ASP page it complains that the SP expects parameter '@RowCount' which was not supplied. I don't need to supply it when invoking the SP directly, why do I need to supply it from ASP? I tried defining it as NULL within my SP, but can't seem to get it to accept both the NULL & Output parms. And while I'm at it, how do I get my ASP page to display this @RowCount value?
A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?
I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.
I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?
I tried doing this with an ArrayList but couldn't bind it to the DataGrid.
I have a table with entries tied to a membership database. The problemis that I want to select a limit of sixteen entries per member, perday, where some members have 16+ entries per day.I have this so far ( which I've simplified for this post)SELECT dbo.members.firstname, dbo.members.lastname,dbo.entries.gameDayFROM dbo.members INNER JOINdbo.entries ON dbo.members.memberID =dbo.entries.memberIDIf it's day 5, each member should have 80 total.How can I change this to select only 16 entries for each member, makingsure it's 16 per day based on dbo.entries.gameDay?Thanks for your help.
Can anyone just point me in the right direction. All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this?
Can anyone help me in modifying this select statement in order to add two more columns to see if the column is identity and if the column is indexed.
SELECT SUBSTRING(SYSOBJECTS.NAME, 1, 45) AS TABLE_NAME, SUBSTRING(SYSCOLUMNS.NAME, 1, 40) AS COLUMN_NAME, SUBSTRING(SYSCOLUMNS.NAME, 1, 15) AS DATA_TYPE, SYSCOLUMNS.LENGTH, SYSCOLUMNS.XPREC AS PRECISIONS, SYSCOLUMNS.XSCALE AS SCALE, SYSCOLUMNS.ISNULLABLE AS ALLOW_NULLS
FROM SYSREFERENCES RIGHT OUTER JOIN SYSCOLUMNS INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE INNER JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ON SYSREFERENCES.CONSTID = SYSOBJECTS.ID WHERE SYSOBJECTS.XTYPE <> 'S' and SYSOBJECTS.XTYPE <> 'V' AND SYSOBJECTS.XTYPE <> 'P' ORDER BY TABLE_NAME
How do I use the select statement to create a blank field in a temp table, so I can use the update statement to populate the data later.
I tried [Select ' ' as field] and then tried the update statement to later populate data and I am getting the following error: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.
select uid, OrderID, Count(OrderID)As DupCnt from OrdDetails group by uid, OrderID having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
How to get recordset from only last SELECT stmt executed???
this is part of my code.. in T-Sql
Create proc some mySp ... AS ... set nocount on if (@SelUserID is null) select 0 else if (@SelUserID = @userID)
select 1
else begin
select * -- select a. from dms_prsn_trx_log where @incNo = ult_incid_no and prsn_trx_no = 10 and trx_log_txt = @logText if (@@rowcount != 0) set @isForwardedByUser = 1 select 2 -- select b. I NEED This value.
end set nocount off GO
here it executes select a, b. But, I want mySp return last executed select result. which is here "select 2"
I thought set nocount ON does that, but it doesn't. How can I do???
For my application, i am writing a generalized Sp for the Select Stmt.
I have started with as shown below,
Create procedure proSelect @TabName varchar(1000), @ColName varchar(1000), @ConName varchar(1000) As Begin Declare @str Varchar(8000) If @ColName = '' Set @ColName = '* ' else Set @ColName = @ColName + ' ' Set @str = 'Select ' +@ColName+ 'From ' +@tabname If @ConName <> '' Set @str = @str + ' Where '+ @ConName exec (@str) End;
I wan to more generalize it.. so that all the functionalities of select stmt can be accomplished with this sp...
Can anyone help ???
Thanks in advance...
one more small doubt(personal): All these days i was a starting member of this forum,but today it has changed to 'Yak Veteran Posting'.. What does that mean.. If not to be disclosed in forum, Do mail me at satishr@kggroup.com
Regards, satish.r "Known is a drop, Unknown is an Ocean"
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output <br> Trans Date, total deposits, total withdrawls, closing balance <br> i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
Hello Everybody,I have a problem, with select stmt:SELECT TOP 15 *FROM oaVIEW_MainData AS TOP_VIEW,oaLanguageData_TAB AS RwQualifierJoin with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin.text_id = c_cfgRegPointAND (((RwQualifierJoin.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dateTime=(SELECT MAX(SUB_VIEW.c_dateTime)FROM oaVIEW_MainData AS SUB_VIEW,oaLanguageData_TAB ASRwQualifierJoin1 with (nolock)WHERE (c_dateTime>='2007.01.10 00:00:00' AND c_dateTime<='2007.01.1023:59:59')AND RwQualifierJoin1.text_id = c_cfgRegPointAND (((RwQualifierJoin1.local1 LIKE N'Position of any bubu')))AND TOP_VIEW.c_dsmIdent=SUB_VIEW.c_dsmIdent)order by c_dateTime descPlease consider:- top doesn't metter, if I will use one or 10000 result is always thesame.- oaVIEW_MainData, is a view on major big table, holding lot of recordsjoinden with small table containing configuration data, over left outerjoin; both tables are with nolock option,- quersy supose to return last record from major table/view, in giventime, additionaly, with other where conditions (like in this case withtext),- on major table, are indexes which one is on id field (not used inthis query at all), which is a pk clustered, and other is on dateEvt(c_dateTime) which is a desc index with fill level 90%- table has also other indexes, on three different fields, one oftheses is dsmIdent,Now, if I'm using max(id) works very fast, and ok for me, but theproblem is, I should not use id, because might be, that the recordswill be written in the table with random order, so the only one sayingwhich is newest, will be dateEvt.Using dateEvt as max(), dramaticly slows query, so I'm acctualy unableto get result. What is much more funny, server is totaly busy with thisquery, and it's procesor jumps on 100%.Now, because the query is builded dynamicly, by a user selections,that's why we decided on such a parser ... problem is, it is notworking :(Can I change index on dateEvt somehow, to sped this up?Maybe construct query somehow different, to get this over max() date?Please helpMatik
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
Here is my query: The result is below: (SELECT 'Succesful' AS metric_value, SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('S Infr', 'S Entitl', 'S Provi') THEN 1 ELSE 0 end) AS S, SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Ading', 'Bending Floor', 'Net Ops' ) THEN 1 ELSE 0 end) AS N, SUM(CASE WHEN Product_Cat_Tier_3__2_ IN ('Management NET', 'erprise') THEN 1 ELSE 0 end) AS ENTERP, SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%Sector%' THEN 1 ELSE 0 end) AS TS, SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%NMS%' THEN 1 ELSE 0 end) AS MS, SUM(CASE WHEN Product_Cat_Tier_3__2_ = 'Fading' ) AR TS, month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) as month FROM Change
WHERE YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND
Product_Cat_Tier_1_2_ = 'Network' AND Product_Cat_Tier_2__2_ = 'RFC' AND (Change_Request_Status = 11 and Status_Reason = 6000) AND Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video') GROUP BY YEAR(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')), month(DATEADD(second, Actual_End_Date, '1969-12-31 8:00:00 PM')) )
===================== Result ================ metric_value S N ENTERP TS MS AR month ------------ -- -- ------ -- -- -- ---- Succesful 0 1 0 0 0 0 5 Succesful 0 1 1 0 0 0 6
And I want the following output, because now is June so month is equal to 6. metric_value S N ENTERP TS MS AR month ------------ -- -- ------ -- -- -- ---- Succesful N N N N N N 1 Succesful N N N N N N 2 Succesful N N N N N N 3 Succesful N N N N N N 4 Succesful 0 1 1 0 0 0 5 Succesful 0 1 1 0 0 0 6
hi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
How could I correct the erroneous value on the property window of an SQL Table.
My problem is that, if I am going to display the property window of Table1, the 'Rows' information displays 115. However, if I am going to execute - Select Count(*) from Table1 then it returns 117. How could I fix this glitch?
I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows. I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.
