How to get this output

Hi evrybody
i have a question like this
i need to display the row data like this
If i use this sql
select datediff(yyyy,'01/01/2001','01/01/2004')
i get the output = 4

problem is sometime first parameter will be null
like this
select datediff(yyyy,NULL,'01/01/2004'),'N/A')
in that case my result looks like NULL

But what i need to see is if the result is null then i need to display like this N/A

How to do this task ?
regards
suis

Hey,

You can use the isnull method, so that if a value is null, it will return another value, like 'N/A' in this case. Though I don't know how that could work in a datediff method. Rather, you may want to wrap the isnull around the entire datediff call, so that if it returns null, you can return 'N/A'.

|||Hi i already used isnull
like this way
select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')
but it giving me error !

"Conversion failed when converting the varchar value 'N/A' to data type int."
regards
suis|||

Try changing this:

select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')

to

select isnull(convert(varchar(4), datediff(yyyy,NULL,'01/01/2004')),'N/A')

|||Hi Kent
Thanks for ur comments,
its worked out
regards
suis

sql

How to get the second row of a recordset?

Here's my SQL Statement (I'm using MS SQL 2000):

SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName
FROM Menu
ORDER BY MenuDate DESC

This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).

Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!

MikeYou want the last row? Is that correct? Use the MoveLast method of the ADO recordset object. This will take you to the second row using a top 2.

eg:
dim Conn = ".... Your connection string ...."

Set dbAPI = Server.CreateObject("ADODB.Connection")
set rs1 = Set rs1 = Server.CreateObject("ADODB.Recordset")

myCmd = "select ........"

dbAPI.Open Conn

set rs1 = dbAPI.Execute myCmd

if rs1.EOF = false then

rs1.MoveLast

myvar1 = rs1("myCol1")
myvar2 = rs1("myCol2")
... and so on

end if

dbAPI.Close

Hope this helps.|||SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM Menu
ORDER BY MenuDate DESC) Menu
ORDER BY Menu.MenuDate

I hope this will solve your problem.|||Thank you both VERY MUCH! The move last would work (can't believe I didn't think of it). I decided to use Rudra's subquery because it was faster (in other words, less typing for me).

Thanks again for solving a problem that was driving me crazy!

how to get the rowNumber in a matrix?

I am working with reporting services recently.

when using a matrix, I want to get the row number to dynamically set the row color.

but i can not get it by using the rownumber function,

can anyone help me with it?

thank you very much.

The row number doesn't really help you in a matrix because you are always dealing with grouped data in a matrix. You have to follow the approach discussed in the following blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

How to get the Row Number per Distinct Records?

Row Number	Name	Phone Number
1	John Doe	(555) 123-1221
1	John Doe	(555) 144-9989
2	Smith	(666) 191-1201
3	Jane Doe	(555) 188-0191
3	Jane Doe	(555) 189-0192
3	Jane Doe	(555) 190-0193

Here are the records I get back using a Grouping on "Name". I would like to assign a Row Number for each "Distinct" row. I've tried all the possible aggregate functions with no luck! Can anybody help me with this? Thanks.

Please try something like this:

=RunningValue(Fields!Name.Value & Fields!PhoneNumber.Value ,CountDistinct, Nothing)

|||Thanks. I didn't know that Expression can take multiple values!!! Now I know!

how to get the primary key from the field of the row ive just inserted

I need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work

int Key = (int)command.ExecuteScalar();

where command is SqlCommand object.

It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.ExecuteScalar() returns the first row/first column of the resulet set. This should work IF part of the command contains something like SELECT Scope_IDentity() or SELECT @.@.IDENTITY after the insert, and the table has an IDENTITY column.sql

How to get the numbers of row in a table using function. Simple but there is a problem i have

Here is the code. Trying to get the number of the rows in a table. If it is greater than 0 return false else return true. Later i changed this code with if else but i am wondering why it is giving this error?

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

CASE WHEN @.ProductNum >= 1 THEN @.Status = 'False' ELSE @.Status = 'True' END

RETURN @.Status

END

Code Snippet

Msg 156, Level 15, State 1, Procedure RestoranHazirMi, Line 25

Incorrect syntax near the keyword 'CASE'.

Msg 102, Level 15, State 1, Procedure RestoranHazirMi, Line 29

Incorrect syntax near 'END'.

CASE WHEN is a statement level construction, not flow control

You could use

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.Status = CASE WHEN COUNT(*)>=1 THEN 'False' ELSE 'True' END

FROM tblProduct

WHERE ProductID = @.ProductPrm

RETURN @.Status

END

OR (IF/ELSE for flow control):

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

IF (@.ProductNum >= 1)

SET @.Status = 'False'

ELSE

SET @.Status = 'True'

RETURN @.Status

END

|||thanks dude. I've learned it now.sql

How to get the matrix row subtotal value

Hi,
i created a report like this:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 100%
i wana show the percentage, so i need to get the row subTotal value, can
anyone help me?
--
Pony TsuiHi Pony,
Thank you for your posting!
Based on my understanding, you want to know how to get the row sub Total
value. If I misunderstood your concern, please feel free to let me know.
You could add the subTotal to your row group. Just right-click the Group
and click SubTotal, then, you could use get the Subtotal under the detail
row.
Hope this will be helpful and if you have any questions or concerns, please
feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Wei,
Thanks for your reply!
I know how to add the subTotal field, i want to get the row subtotal value to
compute the percentage in each cells
as the sample:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 100%
sales2 customer2 10 20 30 40 100
10% 20% 30% 40% 100%
i need to get subtotal 100 in the Q1 - Q4 Columns, i already found out i can
type:
Sum(Fields!Amount.Value, "matrix1_Customer")) to get the row subtotal value,
i can get percentage of Q1-Q4, but in subtotal, the percentage will not 100%,
it show the percentage of (rowsubtotal / total), so how to fix this problem?
Pony Tsui|||Hi Wei,
As i wrote in my previous mail, the percentage of subtotal field will show
the rowSubTotal / Total, i wanna get the resule:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 100%
sales2 customer2 10 20 30 40 100
10% 20% 30% 40% 100%
but the report show:
Q1 Q2 Q3 Q4 subTotal
sales1 customer1 10 20 30 40 100
10% 20% 30% 40% 50%
sales2 customer2 10 20 30 40 100
10% 20% 30% 40% 50%
How to fix this problem?
Pony Tsui|||Hi Pony,
Thanks for the reply.
Would you please send your report file to me? Also, please send some sample
data so that I can troubleshoot and re-produce your problem. Thank you!
I understand the information may be sensitive to you, my direct email
address is weilu@.ONLINE.microsoft.com(Please remove ONLINE when you send
the email), you may send the file to me directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Pony,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How to get the index of each row from a SELECT query in SQL ?

Hi,
I am making as SELECT query to fill a repeater, and I need to retrieve the index of each line of the query.
ie, I want to get a dataset like :
"0", "dataCol1", "dataCol2" for the first line
"1", "dataCol1", "dataCol2" for the second line
"2", "dataCol1", "dataCol2" for the third line
etc.
Anyone knows if there is a sql statement that does it ?
Thanks
Johanncheck this|||You can also create a temp table with an int identity column, insert the first table into the temp, and return it.

how to get the highest row from the bunch of rows

hi...i just want to retrieve the highest row from the collection of
rows like...my table is:
name year
a 2008
a 2006
b 2007
b 2006
b 2005
c 2007
c 2004
I just need the latest year for all the names like:
name year
a 2008
b 2007
c 2007
THANKS..."sql_learner" <mailfrd@.gmail.com> wrote in message
news:eafce7c0-7c49-415e-b410-336593650ce2@.s8g2000prg.googlegroups.com...
> hi...i just want to retrieve the highest row from the collection of
> rows like...my table is:
> name year
> a 2008
> a 2006
> b 2007
> b 2006
> b 2005
> c 2007
> c 2004
> I just need the latest year for all the names like:
> name year
> a 2008
> b 2007
> c 2007
> THANKS...
SELECT name, MAX(year) year
FROM tbl
GROUP BY name;
--
David Portas|||sql_learner wrote:
> hi...i just want to retrieve the highest row from the collection of
> rows like...my table is:
> name year
> a 2008
> a 2006
> b 2007
> b 2006
> b 2005
> c 2007
> c 2004
> I just need the latest year for all the names like:
> name year
> a 2008
> b 2007
> c 2007
> THANKS...
SELECT name,MAX(year)
FROM tablename
GROUP BY name
... p
--
Posted via a free Usenet account from http://www.teranews.com

How to get the count of a value( for ex "PASS") in each row and showits count as a col

I have my sP output as given below:

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2

1 abc 1/1/2007 Pass PassYes No

2 abc 1/1/2007 Pass Fail Yes No

3 abc 1/1/2007 Pass PassYes No

4 abc 1/1/2007 Pass Fail Yes No

5 abc 1/1/2007 Pass Fail Yes No

What i need is this way

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount

1 abc 1/1/2007 Pass Pass Yes No 2

2 abc 1/1/2007 Pass Fail Yes No 1

3 abc 1/1/2007 Pass Pass Yes No 2

4 abc 1/1/2007 Pass Fail Yes No 1

5 abc 1/1/2007 Pass Fail Pass No 2

Similarly i need FailCount, Yes Count, Nocount as few more columns.

The query for the first table output is this way..

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'

-- Add the parameters for the stored procedure here

@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,

E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,

--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',

max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',

max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',

max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',

max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',

max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',

max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',

max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',

max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',

max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',

max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',

max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',

max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',

max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',

max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',

max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',

max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',

max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',

max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',

max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',

max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',

max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',

max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',

max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',

max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',

max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',

max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',

max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',

max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',

max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',

max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',

max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',

max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',

max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',

max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',

max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',

max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',

max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',

max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',

max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',

max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',

max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',

max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',

max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',

max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',

max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',

max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',

max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',

max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',

max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',

max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',

max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',

max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',

max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',

max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',

max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',

max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',

max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',

max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'

--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'

from dbo.Audit_Details AD INNER JOIN

dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN

dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN

dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN

dbo.Employee E ON E.Emp_Id = AD.DE_empid

where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'

AND AD.audit_createdDate between @.FromDate and --'3/29/2007'

REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')

group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,

E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria

--order by AD.audit_id

END

Please Help me out in solving this problem. Thanks in advance

Moving to T-SQL forum.

Mike

|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'

How to get the count of a value( for ex "PASS") in each row and showits count as a col

I have my sP output as given below:

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2

1 abc 1/1/2007 Pass PassYes No

2 abc 1/1/2007 Pass Fail Yes No

3 abc 1/1/2007 Pass PassYes No

4 abc 1/1/2007 Pass Fail Yes No

5 abc 1/1/2007 Pass Fail Yes No

What i need is this way

Audit_Id Audit_Name Audit_CreatedDate 6.2 6.3 6.2.1 6.2.2 Passcount

1 abc 1/1/2007 Pass Pass Yes No 2

2 abc 1/1/2007 Pass Fail Yes No 1

3 abc 1/1/2007 Pass Pass Yes No 2

4 abc 1/1/2007 Pass Fail Yes No 1

5 abc 1/1/2007 Pass Fail Pass No 2

Similarly i need FailCount, Yes Count, Nocount as few more columns.

The query for the first table output is this way..

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[VerificationSummaryReport_TEST] '1/1/2007','3/28/2007','ONBOARD'

-- Add the parameters for the stored procedure here

@.FromDate datetime,@.ToDate datetime,@.VerificationType varchar(15)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

select AD.audit_id, AD.Audit_Name, SM.Shortcode 'shortcode_name', CM.Campaign_Name , CM.Shortcode_Owner 'brand_name', SM.Operator_Name,

E.first_name+' '+E.last_name 'employee_name',AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate,

--case when DE.Status_criteria = 'PASS' then count(*) else '0' end as 'PassCount',

max(case when DE.criteria_id = 1 then DE.Status_criteria else '-' end) as '6.2',

max(case when DE.criteria_id = 2 then DE.Status_criteria else '-' end) as '6.2.1',

max(case when DE.criteria_id = 3 then DE.Status_criteria else '-' end) as '6.2.2',

max(case when DE.criteria_id = 4 then DE.Status_criteria else '-' end) as '6.3',

max(case when DE.criteria_id = 5 then DE.Status_criteria else '-' end) as '6.3.1',

max(case when DE.criteria_id = 6 then DE.Status_criteria else '-' end) as '6.4',

max(case when DE.criteria_id = 7 then DE.Status_criteria else '-' end) as '6.4.1',

max(case when DE.criteria_id = 8 then DE.Status_criteria else '-' end) as '6.4.3',

max(case when DE.criteria_id = 9 then DE.Status_criteria else '-' end) as '6.4.2',

max(case when DE.criteria_id = 10 then DE.Status_criteria else '-' end) as '3.1',

max(case when DE.criteria_id = 11 then DE.Status_criteria else '-' end) as '3.1.1',

max(case when DE.criteria_id = 12 then DE.Status_criteria else '-' end) as '3.1.2',

max(case when DE.criteria_id = 14 then DE.Status_criteria else '-' end) as '3.2',

max(case when DE.criteria_id = 15 then DE.Status_criteria else '-' end) as '3.2.1',

max(case when DE.criteria_id = 16 then DE.Status_criteria else '-' end) as '3.2.2',

max(case when DE.criteria_id = 19 then DE.Status_criteria else '-' end) as '4.6',

max(case when DE.criteria_id = 20 then DE.Status_criteria else '-' end) as '4.6.2',

max(case when DE.criteria_id = 21 then DE.Status_criteria else '-' end) as '4.6.3',

max(case when DE.criteria_id = 22 then DE.Status_criteria else '-' end) as '4.6.4',

max(case when DE.criteria_id = 23 then DE.Status_criteria else '-' end) as '4.7',

max(case when DE.criteria_id = 24 then DE.Status_criteria else '-' end) as '4.7.2',

max(case when DE.criteria_id = 25 then DE.Status_criteria else '-' end) as '4.8',

max(case when DE.criteria_id = 26 then DE.Status_criteria else '-' end) as '4.8.1',

max(case when DE.criteria_id = 27 then DE.Status_criteria else '-' end) as '4.9',

max(case when DE.criteria_id = 28 then DE.Status_criteria else '-' end) as '4.9.2',

max(case when DE.criteria_id = 29 then DE.Status_criteria else '-' end) as '7.1',

max(case when DE.criteria_id = 30 then DE.Status_criteria else '-' end) as '7.1.1',

max(case when DE.criteria_id = 31 then DE.Status_criteria else '-' end) as '7.2',

max(case when DE.criteria_id = 32 then DE.Status_criteria else '-' end) as '7.2.1',

max(case when DE.criteria_id = 36 then DE.Status_criteria else '-' end) as '5.10',

max(case when DE.criteria_id = 37 then DE.Status_criteria else '-' end) as '5.10.1',

max(case when DE.criteria_id = 38 then DE.Status_criteria else '-' end) as '5.5',

max(case when DE.criteria_id = 39 then DE.Status_criteria else '-' end) as '5.5.1',

max(case when DE.criteria_id = 41 then DE.Status_criteria else '-' end) as '5.5.2',

max(case when DE.criteria_id = 42 then DE.Status_criteria else '-' end) as '5.6',

max(case when DE.criteria_id = 43 then DE.Status_criteria else '-' end) as '5.6.1',

max(case when DE.criteria_id = 44 then DE.Status_criteria else '-' end) as '5.6.2',

max(case when DE.criteria_id = 45 then DE.Status_criteria else '-' end) as '5.7',

max(case when DE.criteria_id = 46 then DE.Status_criteria else '-' end) as '5.7.1',

max(case when DE.criteria_id = 47 then DE.Status_criteria else '-' end) as '5.9',

max(case when DE.criteria_id = 48 then DE.Status_criteria else '-' end) as '5.9.1',

max(case when DE.criteria_id = 49 then DE.Status_criteria else '-' end) as '5.9.2',

max(case when DE.criteria_id = 51 then DE.Status_criteria else '-' end) as '1.3',

max(case when DE.criteria_id = 60 then DE.Status_criteria else '-' end) as '8.2',

max(case when DE.criteria_id = 66 then DE.Status_criteria else '-' end) as '9.3',

max(case when DE.criteria_id = 67 then DE.Status_criteria else '-' end) as '9.3.1',

max(case when DE.criteria_id = 68 then DE.Status_criteria else '-' end) as '9.3.2',

max(case when DE.criteria_id = 69 then DE.Status_criteria else '-' end) as '10.1',

max(case when DE.criteria_id = 70 then DE.Status_criteria else '-' end) as '10.1.1',

max(case when DE.criteria_id = 71 then DE.Status_criteria else '-' end) as '10.1.2',

max(case when DE.criteria_id = 72 then DE.Status_criteria else '-' end) as '10.5',

max(case when DE.criteria_id = 73 then DE.Status_criteria else '-' end) as '10.5.1',

max(case when DE.criteria_id = 74 then DE.Status_criteria else '-' end) as '10.5.2',

max(case when DE.criteria_id = 75 then DE.Status_criteria else '-' end) as '10.6',

max(case when DE.criteria_id = 76 then DE.Status_criteria else '-' end) as '10.6.1',

max(case when DE.criteria_id = 77 then DE.Status_criteria else '-' end) as '10.7',

max(case when DE.criteria_id = 78 then DE.Status_criteria else '-' end) as '10.7.1',

max(case when DE.criteria_id = 79 then DE.Status_criteria else '-' end) as '6.4.4'

--, case when DE.Status_criteria = 'PASS' then Count(*) else 0 end as 'PASSCOUNT'

from dbo.Audit_Details AD INNER JOIN

dbo.Data_Evaluation DE ON DE.Audit_Id = AD.Audit_ID INNER JOIN

dbo.ShortCode_Master SM ON SM.ShortCode_Id = AD.Shortcode_Id INNER JOIN

dbo.Campaign_Master CM ON CM.Campaign_Id = AD.Campaign_Id INNER JOIN

dbo.Employee E ON E.Emp_Id = AD.DE_empid

where AD.present_auditstate = 'AS' and AD.verificationtype = @.VerificationType AND AD.STATUS = 'ACTIVE'

AND AD.audit_createdDate between @.FromDate and --'3/29/2007'

REPLACE(CONVERT(CHAR(10),DATEADD(day, 1,@.ToDate),110),'-','/')

group by AD.audit_id, AD.shortcode_id, AD.campaign_id, AD.Audit_Name, SM.Shortcode, CM.Campaign_Name , CM.Shortcode_Owner, SM.Operator_Name,

E.first_name,E.last_name ,AD.Type_of_Service, AD.SignUp,

AD.Delivery, AD.Price, AD.Unitofpurchase,AD.Audit_createdDate--, DE.Status_criteria

--order by AD.audit_id

END

Please Help me out in solving this problem. Thanks in advance

Moving to T-SQL forum.

Mike

|||sum(case when DE.Status_criteria = 'PASS' then 1 else 0 end) as 'PASSCOUNT'

How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?

Can you please assist me on how to get the 2nd record in case there are

3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get

the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.

How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ?

-----------------------*--

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.

Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...

SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,

(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate DESC) AS NEWBASIC,

******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ?

(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate ASC) AS OLDBASIC

FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @.FromDate AND
@.ToDate
ORDER BY E.LastNameheri (heri.carandang@.acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ?

SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC

Gives you the second highest value of val.

If you want to do this for a set values, this may be more practical:

SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||or

Select min(col) from
(
select top N col from table Order by col DESC
) T

Madhivanan

How to get Specific rows from Table

hi

i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200

HI,

do it as

for n to n1 records do as

select top n1-n * from

(

select top n1 * from

)

order by key desc

|||

If your total result set is small enough (> 1000 records) you could insert into a table variable with an identity column and then select back out referencing the identity column in the where clause. For example:

DECLARE @.tblTable TABLE

(

TableID INT IDENTITY(1,1)

,OtherID INT

,Value VARCHAR(50)

)

INSERT @.tblTable (OtherID, Value) VALUES (1, 'One')

INSERT @.tblTable (OtherID, Value) VALUES (2, 'Two')

INSERT @.tblTable (OtherID, Value) VALUES (3, 'Three')

INSERT @.tblTable (OtherID, Value) VALUES (4, 'Four')

INSERT @.tblTable (OtherID, Value) VALUES (5, 'Five')

DECLARE @.Start INT, @.End INT

SELECT @.Start = 1, @.End = 3

SELECT *

FROM @.tblTable

WHERE TableID BETWEEN @.Start AND @.End

|||

It seems that you are trying to do pagination in database.

Below is the code snippet for a stored procedure. This takes page number and numbers of records in a Page.

CREATE PROCEDURE Pagination
@.Page int,
@.Size int
AS

DECLARE @.Start int, @.End int
BEGIN TRANSACTION GetDataSet
SET @.Start = (((@.Page - 1) * @.Size) + 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
SET @.End = (@.Start + @.Size - 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @.@.ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
SELECT ...
-- Any kind of select statement is possible with however many joins
-- as long as the data selected can fit into the temporary table.
IF @.@.ERROR <> 0
GOTO ErrorHandler
SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @.Start) AND (Row <= @.End)
IF @.@.ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @.@.ERROR

Regards

Sachin

How to get rowcount

Hi,

How to get the row count for a particular query being executed within a session and scope. is there any option to get the value being displayed in the messages tab when a query is executed (no of rows affected). is there anything equivalent to scope_identity for getting the identity value inserted in a session and scope.

Vivek S

to obtain number of rows affected...

@.@.RowCount (return an int)

RowCount_Big() (return a big int)

to obtain last genereted identiy value...

@.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

@.@.IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @.@.IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

The scope of the @.@.IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.

|||did this help?

How to get row size

using sql 2k, what's the fastest and easiest way to get the row size of a
row. I hava a number of rows I need to look at.
Thanks.
moondaddy@.noemail.noemailHello,
If you need to estimate the size of a table, you can use the following
Excel file:
http://www.microsoft.com/downloads/...&displaylang=en
The size of each row can be variable (if there are variable-length
columns in the table). If you want to see the min/max/avg row size in
an existing table, you can use:
DBCC SHOWCONTIG ('table name') WITH TABLERESULTS
AFAIK, there is no direct method to get the row size for a particular
row in an existing table. If you need this information, copy that row
in an empty table with the same structure and use DBCC SHOWCONTIG, as
shown above.
Razvan|||this will give you the min size and max size of the rows in you table, then
make an educated guess..
This is the easiest way
select minlen,xmaxlen from sysindexes where indid in (1,0) and id =
object_id('tbl_name')|||Thanks for the reply.
What I'm trying to determin is how close a table is to the max rowsize of
8060. I dont see what part of this is going to help me with that.
I used DBCC SHOWCONTIG ('table name') WITH TABLERESULTS
and got the result below. Should I be able to guestimate the total rowsize
from this?
tbLeaseDt
898102240
PK_tbLeaseDt
1
0
45
1325
120
1431
263.83100000000002
0
7
6
268.73300170898437
96.679847717285156
85.714285714285708
6
7
0.0
14.285714149475098
moondaddy@.noemail.noemail
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146811248.235424.294000@.e56g2000cwe.googlegroups.com...
> Hello,
> If you need to estimate the size of a table, you can use the following
> Excel file:
> http://www.microsoft.com/downloads/...&displaylang=en
> The size of each row can be variable (if there are variable-length
> columns in the table). If you want to see the min/max/avg row size in
> an existing table, you can use:
> DBCC SHOWCONTIG ('table name') WITH TABLERESULTS
> AFAIK, there is no direct method to get the row size for a particular
> row in an existing table. If you need this information, copy that row
> in an empty table with the same structure and use DBCC SHOWCONTIG, as
> shown above.
> Razvan
>|||Thanks. can you please explain how I would guess the approximate total row
size using the min size and max size in the table? I may have a number
varchar columns of large size along with many other columns. even though
none of the current data in the columns is more then a length of 50.
Therefore, wouldn't the max rowsize be superficially low?
moondaddy@.noemail.noemail
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:017A12EC-48E1-409B-9A03-190E9630B9C2@.microsoft.com...
> this will give you the min size and max size of the rows in you table,
> then
> make an educated guess..
> This is the easiest way
> select minlen,xmaxlen from sysindexes where indid in (1,0) and id =
> object_id('tbl_name')
>|||>From these results, you can see that the row size for the smallest row
is 120, the row size for the biggest row is 1431 and the average row
size is 263.83100000000002. These informations are referring to the
rows that exist in the table at this time.
If you want the row size of largest row that can be inserted in the
table, you can compute this size based on the definitions of your
columns (that can be found in the syscolumns table), using the
informations presented in the Books Online topic "estimating table
size":
http://msdn.microsoft.com/library/e...des_02_92k3.asp
Razvan|||Hello,
You could use DATALENGTH() for a quick measure of row size.
Please see "Estimating the Size of a Table" in BOL for some related
infrmation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to get Row Numbers from SQL Express 2005 query?

Hi,

I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error:

"The OVER SQL construct or statement is not supported."

So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)?

My current command is as follows:

SELECT (SELECT Row_Number()OVER (ORDER BY UserName)As RowNumber), *
FROM Users
ORDER BY UserName

Thanks for any advice you can offer!

like this

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

Hope this helps

|||

SELECT ROW_NUMBER() OVER (ORDER BY ProductID) as RowNumber,ProductID, ProductName, UnitPrice FROM Products WHERE RowNumber BETWEEN 20 AND 30;

|||

Thank you both for your replies. I'm sorry, I think I my question may have been a bit misleading. I know that you can return a limited result set by using the statements you have provided, but the error message I am getting is as follows:

"The OVER SQL construct or statement is not supported."

Both your suggestions use the OVER construct, and this does not appear to be supported bySQL Server Express 2005. If I run your suggested queries inVisual Studio 2005 I get the aforementioned error and the query will not run (though the SQL validation check says it's fine).

So, is the "Row_Number() OVER" command supported by SQL Express 2005 or not? If not, what other ways can I return a limited result set?

|||

i think you are using the designer, go in the sql server management studio and to this

Select File / New / Query and type your queries in the editing window instead.

Hope this helps

|||

Run this in SQL SERVER 2005 Management Studio Express:

EXEC sp_dbcmptlevel yourDataBase

If the current compatibility level of your database is 80, then run this:

EXEC sp_dbcmptlevel yourDataBase, 90

You need the compatibility level at 90 to run the Row_Number() OVER() query and other new features.

|||

Hi Limno,

That sounds promising. I've tried getting my website database into SQL Server Management Studio Express in the past but without success. My database is held as a .MDF file within my project - is there a way of importing this directly into Management Studio? The "Connect To Server" dialog displayed on startup does not allow me to browse to a specific .MDF file, and the File -> Open dialog has no option to open .MDF files.

Thanks

|||

I just found out how to import an MDF here:

http://forums.asp.net/p/1147899/1871779.aspx#1871779

And my database shows a compatibility rating of 90. Using the Row_Number() command works within SQL Server Management Express Studio! In that case, how can I perform a query in Visual Studio 2005 that uses this command if Visual Studio does not support this command?

I'm using strongly typed Table Adapters and Data Tables using the DAL component in Visual Studio. Is it impossible? If not, can I at least perform this query programmatically (C#) and cast the results to my strongly typed Data Table? If it can be done programmatically, does anyone have any examples of how to do this?

Thanks again!

|||

In Visual Studio 2005, you will see that not support message. Have you tried to ingnore it and see what will happen? It seems is should work fine. Another way, you can wrap your query in a Stored Procedure to work with.

How to get row count from an inner query

Hi All,

I have the following SQL query:

select temp.emp_id, temp.rownum

from

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

) temp

where temp.rownum <=10

group by temp.emp_id

I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.

Hi,

I wrote something similar in here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2188269&SiteID=1

But the problem may be, that the Number of Rows in the outer Statement dosent match to the number of rows in the inner Statement.

Best Regards

Raimund

|||

How about this,

Code Block

with temp

as

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

)

,countfinder

as

(

select count(*) as [rowcount] from temp

)

select temp.emp_id, temp.rownum, (select [rowcount] from countfinder) [totalrowcount] from temp

where temp.rownum <=10 group by temp.emp_id

|||Brilliant.....this was a very novel way of doing it. Thank you very much. I will try out the same in my implementation.

How to get row count ?

To all gurus,
I am developing an application in which i want to show the number
of rows returned by the query.
e.g.
Select Categories.CategoryName, Products.ProductName,
Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
ProductSales
FROM
((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
Orders.OrderID)
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
WHERE
(Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
GROUP BY
Categories.CategoryName, Products.ProductName

I want the number of rows returned by this query.
How can i get the number of rows?

Please help me..
waiting for your replies..

Prem
(premratan@.hotmail.com)Select count (*) as "row count" from (select <any query here>) as t

For some reason, the final table alias "as t" is required.

Goetz Graefe

"Prem" <premratan@.hotmail.com> wrote in message
news:2f7d06ff.0311111515.2a2a040c@.posting.google.c om...
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.CategoryName, Products.ProductName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
> INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
> WHERE
> (Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.CategoryName, Products.ProductName
> I want the number of rows returned by this query.
> How can i get the number of rows?
> Please help me..
> waiting for your replies..
> Prem
> (premratan@.hotmail.com)|||Refer to @.@.ROWCOUNT in SQL Server Books Online. If you are using ADO in your
application, then you can use the recordset's RecordCount property to get
the value at the client side.

--
-- Anith
( Please reply to newsgroups only )|||premratan@.hotmail.com (Prem) wrote in message news:<2f7d06ff.0311111515.2a2a040c@.posting.google.com>...
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.CategoryName, Products.ProductName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
> INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
> WHERE
> (Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.CategoryName, Products.ProductName
> I want the number of rows returned by this query.
> How can i get the number of rows?
> Please help me..
> waiting for your replies..
> Prem
> (premratan@.hotmail.com)

After running the query, you can do this:

select @.@.rowcount

If you need to use the value later, you can put it in a variable:

set @.rows = @.@.rowcount

Simon

How to get results back from a Stored procedure.

I have a stored procedure1 calling stored procedure 2. Stored
procedure 2 when it is finished returns a single row with 2 columns.
Is there a way to grab the return of column 1 from stored procedure 2
inside stored procedure 1?
Thank you in advance
http://www.sommarskog.se/share_data.html
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:1651d138-de4e-49ac-928c-c2558468ef55@.s50g2000hsb.googlegroups.com...
>I have a stored procedure1 calling stored procedure 2. Stored
> procedure 2 when it is finished returns a single row with 2 columns.
> Is there a way to grab the return of column 1 from stored procedure 2
> inside stored procedure 1?
> Thank you in advance

How to get results back from a Stored procedure.

I have a stored procedure1 calling stored procedure 2. Stored
procedure 2 when it is finished returns a single row with 2 columns.
Is there a way to grab the return of column 1 from stored procedure 2
inside stored procedure 1?
Thank you in advancehttp://www.sommarskog.se/share_data.html
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:1651d138-de4e-49ac-928c-c2558468ef55@.s50g2000hsb.googlegroups.com...
>I have a stored procedure1 calling stored procedure 2. Stored
> procedure 2 when it is finished returns a single row with 2 columns.
> Is there a way to grab the return of column 1 from stored procedure 2
> inside stored procedure 1?
> Thank you in advance